Skip to main content

Configure the Excel Parser

Updated

Configure the Excel Parser to allow applicants to submit budget data or table information using a Microsoft Excel template. The uploaded data populates custom fields or creates activities in SmartSimple.

Who: Global Administrator

When to Use the Excel Parser

Use the Excel Parser when:

  • The application requires complex budget tables or data grids with many columns, and the Advanced Data Table custom field would be cumbersome to complete or difficult to configure.
  • The process relies on complex formulas that may change over time. Excel handles the calculations, and only the final values need to be stored in the system, making updates easier to manage than rebuilding formulas natively. 
  • Extensive information is collected from applicants, but only a subset of that data needs to be reported on or worked with in the system. 
  • The workflow requires generating individual activity records, such as budget items or project milestones, directly from applicant-submitted data.

Prepare the Excel Template

To prepare the spreadsheet that applicants will download and complete:

  1. Open Microsoft Excel and create a blank workbook.
  2. Configure the spreadsheet layout by adding headings, applying cell formatting, and entering formulas as needed.
  3. Assign a background color to cells where applicants will enter data. The remaining cells will be locked in a later step.

Name Cells in the Spreadsheet

SmartSimple uses named cells to identify which data to parse. Name the individual cells and cell groups you want to map to fields or activities.

  1. Select the cell you want to name (for example, a header cell such as Salaries).
  2. On the Formulas tab, click Define Name. Once named, the cell name appears in the Name Box when that cell is selected.
  3. For a row of activity data, name the first cell in the row (for example, StartingSalaries). Additional cells in the row can be referenced in field mapping syntax using col_1, col_2, and so on. No additional named cells are required for those columns.
  4. Repeat for any additional cells to map, such as header cells or subtotal cells.
NOTE  The Name Manager on the Formulas tab shows all named cells defined in your spreadsheet.

↑ Back to top

Secure the Excel Spreadsheet

Lock the spreadsheet to prevent applicants from modifying its structure. Lock all cells first, then unlock the cells where applicants should enter data.

  1. Click the Select All box between column A and row 1 to select all cells.
  2. Right-click the same Select All box, select Format Cells, then click the Protection tab.
  3. Check the Locked box, then click OK. All cells are now set to locked.
  4. Select the cells where applicants will enter data.
  5. Right-click the selected cells, click Format Cells, then click the Protection tab.
  6. Uncheck the Locked checkbox to unlock the designated input cells.
  7. On the Review tab, click Protect Sheet. In earlier versions of Excel, this may be under Tools > Protection.
  8. Limit user permissions to selecting unlocked cells and enter a password to secure the sheet.

Applicants will only be able to enter data in the unlocked cells and cannot insert or delete rows or columns, add comments, or make structural changes.

↑ Back to top

Upload the Spreadsheet to a SmartFolder

Upload the completed template to a SmartFolder so applicants can access it.

  1. Click the Menu icon in the upper navigation bar, and then select SmartFolders.
  2. Create a new folder or select an existing folder.
  3. Upload the Excel spreadsheet file to the folder.
  4. After uploading, under File Options, select View URL from the drop-down menu to obtain the file's direct link.
  5. Copy the link and save it for use in the next section. The link will resemble:

    https://alias.smartsimple.com/files/1234567/f123456/activity-excel-parser.xlsx

↑ Back to top

After uploading the spreadsheet, create a way for applicants to download the template. The steps below create a dedicated instructions field with a download link.

  1. Click the Menu icon in the upper navigation bar, and then select the applicable UTA/module.
  2. Click the Configuration Settings gear for that UTA/module.
  3. Click the desired level tab, then click Custom Fields.
  4. Create a new custom field of type Display - Instructions and label it.
  5. In the field content, add a download link using the relative path from your SmartFolder URL. Remove the https://alias.smartsimple.com portion. Always use relative links in SmartSimple to ensure the link works across environments. For example:

    <a href="/files/1234567/f123456/activity-excel-parser.xlsx">Download the Budget Template</a>

    Replace the file path in the example with the relative path from your SmartFolder URL.
  6. Click Save.
NOTE  A download button can be used instead of a text link. Update the relative path to match your file:
<a href="/files/1234567/f123456/activity-excel-parser.xlsx"><button type="button">Download Budget Template</button></a>

↑ Back to top

Configure the Multi-File Upload Field

Create the field where applicants upload the completed template.

  1. Click the Menu icon in the upper navigation bar, and then select the applicable UTA/module.
  2. Click the Configuration Settings gear for that UTA/module.
  3. Click the desired level tab, then click Custom Fields.
  4. Create a new custom field of type Upload - Multiple Files Storage and label it.
  5. Set Caption Location to Above Field.
  6. If you created a separate instructions field in the previous section, proceed to the next step. Otherwise, add the download link instructions directly in this field using the relative link to the Excel template.
  7. Enter a Button Label such as Upload Completed Excel Template.
  8. To make the upload required, toggle Mandatory on and add a Failed Validation Message such as: You must upload a completed budget using the provided Excel template.
  9. Enter xlsx for the allowed file type.
  10. Set Minimum Number of Files to 1.
  11. Toggle Enable Excel Parsing on.
  12. Click Save.

↑ Back to top

Set Field Mappings

Field mappings tell SmartSimple which named cells to parse and where to send the data. Configure the field mappings to either populate custom fields on the record or create activities, not both in the same configuration.

  1. Open the Upload - Multiple Files Storage custom field configured in the previous section. A Field Mappings section appears near the top of the field settings.
  2. Obtain the custom field IDs for the fields you want to populate. Field IDs appear near the top of each custom field's configuration page.
  3. Enter the field mapping code in the Field Mappings input. For syntax details and examples, see Excel Parser Field Mapping Syntax (Reference).
  4. Click Save.
  5. To verify the configuration, complete the process from the applicant's perspective: download the template, fill it in, upload it, and confirm that activities are created or fields are populated as expected.
NOTE  If data does not parse correctly, verify that cell names in the spreadsheet exactly match the names used in the field mapping code, and check that all custom field IDs are correct.

↑ Back to top

Update Existing Custom Fields and Activities

Applicants can upload a revised Excel template at any time to update custom fields and activities.

The activity name drives updates: if an activity name in SmartSimple matches a value in col_1, SmartSimple updates the existing activity on re-upload. If the name has changed, SmartSimple creates a new activity instead of updating the existing one.

NOTE  If the template structure changes, such as rows or columns moved or removed, data may not parse correctly after re-upload. If this occurs, clear the existing field data and upload the revised template to repopulate.

Troubleshooting

If the Excel Parser is not working as expected, check the following:

  • Confirm the template file extension is .xlsx.
  • When updating custom fields on the same record, the custom fields must be set to Deny Modify.

↑ Back to top

Helpful Links