Set up bots for your Spreadsheets to pre-fill them with data from the datasource of your choice or to update data in your system of record.
Let’s review how this is done in the example below: we need to pre-fill a table with data from Salesforce records to calculate the total amount. We’ll then update the value we’ve calculated in the Salesforce record.
1. Open your Spreadsheet document in the online editor. Then, enter a header for your table. This will help you to understand which data type the pre-filled values correspond to (Name, Amount, and Date in the example).
Select the cells which will correspond to the number of columns in your table by dragging the cursor to the right. Then, right-click the selected cells and select Define Name.
Tip: Be sure not to use table headers as table columns.
Create a named range for these cells (Income_report in the example).
Next, define the corresponding field type for each cell in the table:
1) Name → Text
2) Date → Date
3) Amount → General/Number
2. In this example, we need to calculate the total amount of the values that will be pre-filled into the Amount column. The calculated value will then be used for updating the value in your Salesforce record.
To do so, go to the fourth row. Under the Amount column, select a cell. Be sure to create a named range for this cell (TotalAmount in the example). Then, enter a formula that will calculate the total amount once the values are pre-filled:
=SUM(B2:B3)
Alternatively, enter the = symbol and search for the SUM function. Then, select cells that will contain income values by dragging them to the bottom.
Click Enter to save your settings. Then, click Save to finish editing the document.
3. Proceed to the workflow builder to install the Pre-fill bot (the Pre-fill from Salesforce records with SOQL bot in the example).
The bot settings will open. Enter your SOQL query to find the records that will be used to pre-fill data. Select Table for the data type that will be mapped.
Map Salesforce object fields to Spreadsheet document fields.
Finish setting up the bot according to your needs, then click Apply.
4. To update the total amount that will be calculated once the document is pre-filled, install and configure the Update Salesforce records bot. In the bot settings, select the Salesforce object (Opportunity in the example). Then, map the Spreadsheet document field to the Salesforce object field that should be updated.
Finish setting up the bot, then click Apply.
Once a workflow has been run, the spreadsheet document will be pre-filled with data from Salesforce records. The total amount will be automatically calculated and updated in the Salesforce record.
