Difference between revisions of "Excel Parser"

Revision as of 15:57, 1 May 2020

Overview

What and Why

The Excel Parser feature gives your applicants the ability to complete their budgets using the Microsoft Excel application offline, and then the applicant can upload the completed Excel spreadsheet into your system later. The data contained within the spreadsheet will be used to populate custom fields in your system, or the data can be used to generate multiple activities. This feature may be a better option than using the Advanced Data Table custom field as this feature is well suited for complex budgets or tables of information with many columns.

This article will show you how to: set up Platform3 and an Excel spreadsheet to work with the Excel Parser process so your community can upload spreadsheets to populate their applications and generate activities.

You need to have Global Administrator access to setup or edit the elements used in the Excel Parser process. You also need to have Microsoft Excel to set up the spreadsheet that users will complete and upload.

Use Cases

Excel Parser is typically used for complex tables of information with many columns as found in budgets. This is an alternative to using the Advanced Data Table custom field which can be laborious to fill in and complicated to set up. It is also used to create activities such as budget items or project milestones. The Excel Parser may also be advantageous when you want to request lots of information from an applicant but only want to work with or report on some of the information.

Best Practices

When configuring the Excel Parser you can either populate fields on the record or create activities for the record, not both.

Configuration – Essentials

The process from the applicants perspective

1. Log into your instance and download the MS Excel template.
   

   See instructions with a link to download the template the applicant needs to complete
2. Complete and save the template.
   

   A sample completed Excel spreadsheet
3. Upload the template.
   

   Uploading the completed Excel spreadsheet
4. Information in the template is parsed and mapped fields are populated and activities are created.
   

   Activities created when spreadsheet uploaded
5. Optionally, you can delete the previously uploaded spreadsheet, then upload a new spreadsheet to update the information already in your system.
   

   Select the uploaded spreadsheet and click the delete button then upload a new spreadsheet to update your values in the system. Note: new values appear on refresh or save.

Preparing the Spreadsheet

Let's set up an Excel spreadsheet that will create level two activities.

1. Open Microsoft Excel and create a blank workbook.
2. Setup the Excel Spreadsheet the way you want it to look by adding headings, applying cell formatting and entering formulas as desired.
3. Set a background color to indicate the cells where you expect the user to enter information. We will lock the rest of the spreadsheet later.

A sample spreadsheet formatted with formulas headings and background colors

Naming Cells

Platform3 will need to know which cells you want to parse so we need to name those cells so we can refer to them later. We will do this by naming cells in Excel. You can name individual cells and groups of cells. In this example we are going to name individual cells only and use those to create activities.

1. Give a name like Salaries to the header cell you want to parse into Platform3. I will use that named cell to populate a specific custom field value later.

1. You can define the cell name in Excel by clicking on the desired cell, go to the Formulas tab and click Define Name. Once a name has been defined for a cell you can see it in the Name Box when the desired cell is selected.

Defining a name for a cell, so we can refer to it later. The defined name shows up in the name box when a cell or group of cells is selected.

1. Give a name like StartingSalaries to the first cell you want to parse into Platform3. I could give names to all the cells in this row but instead I chose to name the first cell and then I will refer to the other cells in the row using the syntax "col_1", "col_2", etc.

1. You may wish to name other cells, for example I also named SalariesHeader and SalariesSubTotal so I could map to those as well.

Note: You can also use the Name Manager in Excel to see and modify all cell names defined in your spreadsheet.

Locking The Excel Spreadsheet

Now that we have set up our Excel spreadsheet and named the cells we want to parse, we need to lock the spreadsheet. We will lock the entire spreadsheet except our desired cells so the applicant cannot add rows, columns or otherwise modify the spreadsheet as that could cause the parse process to fail.

1. Click the box to the left of column A (in between column A and row 1). This will select all cells.

Select all cells in the spreadsheet by clicking between column A and row 1

1. Right click the same box – select Format Cells then click the Protection tab.

1. Make sure the "Locked" checkbox is checked.

Make sure all cells are locked

1. Click OK (these first few steps make sure that all cells are locked. See steps 5-6 for unlocking individual cells).

1. Right click just the cell(s) you wish to have editable by the end user (i.e. unlocked).

1. Select Format Cells then click the Protection tab. Then un-check the Locked box. This will unlock the cells that you want the end user to be able to edit.

1. Click the Review tab, then click Protect Sheet (In older versions of Excel, this may be found under the Tools menu, then Protection).

1. Only allow the user to select unlocked cells and enter a password in the prompt to be used to un-protect the sheet.

Spreadsheet is protected with a password to unlock

Users will now only be able to select and input into cells we unlocked in step #6. They won’t be able to insert or delete columns or rows, add comments, or any of the other actions.

Upload the Spreadsheet to a SmartFolder

Now that we have our spreadsheet setup for the applicant to use, we need to put that spreadsheet somewhere the applicant can get it. We will put the spreadsheet in a SmartFolder.

1. In your instance of Platform3 go to the Menu Icon > SmartFolders.

1. Create a new folder to hold the spreadsheet and call it something like Excel Parser (You could also place this in an existing folder).

Upload Excel spreadsheet template to a SmartFolder

1. Navigate to the Excel spreadsheet file and upload it into your SmartFolder

1. Once the file has been uploaded, under File Options change the drop down to View URL this will provide you with the link to the file we just uploaded.

View URL to see the path to the Excel spreadsheet template

1. Copy the link to the spreadsheet into notepad and save it. We will use this link later. The link should look something like https://alias.smartsimple.com/files/1234567/f123456/activity-excel-parser.xlsx

Create a Download Link

Once you have uploaded the spreadsheet to a SmartFolder you need to provide a link for your applicants so they can download the spreadsheet template you expect them to complete. You could put a link in the instructions for the “Upload - Multiple Files Storage” custom field we will create later, but I have chosen to create a separate instructions field for greater visibility. Here is how you can create an instructions field with a link to download the Excel spreadsheet that the applicant will download and complete.

1. Navigate to the desired UTA such as Submission Manager and click the configuration settings buttons (looks like a gear).
2. Go to the desired level tab, in my case it was a Level 1 called Grants.
3. Click the Custom Fields link.
4. Create a new custom field of the type Display - Instructions and call it something like Budget Instructions.
5. For the content enter something like the following:

Content for the plain text editor of a Display – Instructions custom field

1. Now replace the link above that looks like /files/1234567/f123456/activity-excel-parser.xlsx with the link to the spreadsheet you uploaded to a SmartFolder in our previous steps. Remember to remove this part https://alias.smartsimple.com from your link. Always use relative links inside Platform3 to ensure they function correctly and work across environments.
2. Save the custom field.

Alternatively you could also add a button for the applicant to click in order to download the excel template. Below is the code you would use. Just as above you would need to update the relative path.

Custom code to add a button to download the template.

An instructions custom field with link to download spreadsheet template

Configuring the Multi-File Upload Field

Previously we added instructions and a link so the applicant can download the Excel template. Now we need to create a field where the applicant can upload the completed template so it can be parsed.

1. Go to the desired UTA configuration settings, click on the desired level tab, and click the Custom Fields link.
2. Create a new custom field of the type Upload - Multiple Files Storage and call it something like Upload Budget.
3. Set the Caption Location to Above Field (this will give more space for longer captions and instructions).
4. If you created an instructions custom field above, move to the next step, otherwise you can add instructions into this field as per the above using the relative link to the excel template.
5. For Button Label enter something like Upload Completed Excel Template or Upload Completed Budget Template.
6. If your applicants must upload a budget, toggle on Mandatory and add a Failed Validation Message something like You must upload a completed budget using the provided Excel template.
7. Enter xlsx for allowed file type to ensure applicants can only upload the correct file format.
8. After the July 2020 upgrade you may need to set the Minimum Number of Files to 1.
9. Toggle on Enable Excel Parsing.
10. Save the custom field.

Field Mappings on the Multi-File Upload Field

Previously we set up a field for the applicant to upload the completed spreadsheet and we created a spreadsheet with named cells or groups of cells. Now we need to tell your system what data we want to parse, and what we want to do with that data. Typically you will either populate a custom field with one or more values from the spreadsheet or as in this example we will create level 2 activities based on rows from the completed spreadsheet.

1. On the Multi-File Upload custom field we set previously with Enable Excel Parsing will see a new section near the top called Field Mappings. This is where we define what will be parsed to where.
2. If you have already created the activity custom fields you want to map to, you will need to get the custom field IDs for each. Otherwise you need to create the desired activity custom fields.

Custom field IDs are found on each custom field's configuration page near the top

1. If you followed the instructions above you can place the following into the Field Mappings input and replace Custom Field IDs with your desired custom field IDs.

Code entered into Field Mappings

1. Click Save.

Now you can test out the entire process from the applicants point of view and watch the activities get created and populated with data upon upload of the completed spreadsheet.

Note: if it does not parse correctly, start by checking that the names defined in your spreadsheet match the names in the field mapping, and check that the custom field IDs are correct. For more information on the Syntax see below.

Updating Existing Custom Fields and Activities

The applicant can upload a modified Excel template at any time to update the custom fields and activities. Please note if you need to change your template by moving or removing rows or columns, the data may not parse correctly after such modifications. Depending on the configuration you may be able to go to the field data and replace values to clear out what was previously uploaded and then upload a new completed spreadsheet again to repopulate.

Configuration - Advanced

Activity Syntax and Field Mappings

When you are mapping cells in your spreadsheet to fields in your system you can either map to the same record or you can create activities.

The example above showed how to map to activities. Where the syntax opens and closes with a square bracket [] then each set of activities are encapsulated in curly brackets {} and separated by a comma ,.

Inside the curly brackets you call the field/property in quotes, for example "name" for the name of the activity or "[cf_3980270]" for a custom field on the activity or "sheet" for the sheet within the excel document where the data is located.

This is followed by a colon : to separate the field from the cell being parsed.

After the colon enter the named cell you defined in the spreadsheet and do so in quotes. Example "Equipment" if you named the cell in the spreadsheet Equipment. Alternatively if you declared a starting cell, example: "startcell": "StartingEquipment", you can call each cell in that row using col_1, col_2, etc. Example "[cf_1111112]": "col_1", this will create an activity for each row mapping the columns as defined until the parser comes to a row with a blank first value. At this point the parser will stop and move to the next set of activities to parse and create.

Lastly add a comma , to separate each field/property to cell name mapping.

Note: if you enter a name mapping that does not exist, the parser will fail and may create only 1 activity or none.

Same Record Syntax and Field Mappings

Alternatively, if you don’t want to create activities you can populate fields on the record. For example you could map a cell to a custom field such as a Text Box - Text Single Line or multiple cells with one name to a single field such as a Basic Data Table.

If you want to map multiple cells in your Excel template to a basic data table using a range of cells you can do so by doing the same as listed above, except instead of selecting a single cell and naming it in the Excel spreadsheet you select and name a set of cells example A1 to E5.

Also in this scenario the syntax is different. The syntax for custom fields is : [CustomFieldName1]=PersonnelTable;[CustomFieldName2]=SummaryTable

In this example the custom field name goes between square brackets [], followed by an equals sign = followed by the name you gave the cell or cell group in your excel spreadsheet.

lastly , each mapping is separated by a semicolon ;

Note: if you are populating a basic data table, you must set that custom field to Deny Modification, otherwise it will not update, and the current screen values will overwrite the parsed values on save.

Settings Explained

Field Mappings Syntax for Creating Activities

Field Mappings Syntax for Same Record

When populating custom fields on the same record the syntax is much simpler. Wrap the custom field in square brackets, add an equal sign then enter the name as defined in excel spread sheet for the desired cell or set of cells. If you want to populate multiple fields, separate them with a semicolon.

Example: [fieldname1]=NameInExcel1;[fieldname2]=NameInExcel2