Pricing
Company
Free Trial
We Use Cookies

We use cookies to ensure that we give you the best experience on our website. If you continue to use this site we will assume that you are happy with this.

See our cookie policy.

Automation Action: Create Spreadsheet

Create a spreadsheet and save it in various formats.

Built-In Action

Create an Excel compatible spreadsheet using the built-in Spreadsheet Editor. Automation %variables% can be assigned to cells when the Automation executes. Spreadsheet formulas are then recalculated. The resulting spreadsheet can then be saved in various formats and you can read cell values back into Automation %variables%. You can use this Action to create formatted Invoices, Quotations etc. that can then be saved and emailed.

The Spreadsheet Editor emulates Microsoft Excel. You can format the spreadsheet as you would with Excel using formulas, charts, images, borders, fonts etc. You can load an existing Excel file using the File - Open option. Once you save the Action the spreadsheet data will be saved with your Automation (the original file will remain unchanged).

Inserting Variables

Spreadsheet cells can contain Automation %variables%. When the Automation executes these will be replaced with their values.

There are two ways to embed %variables%.

  1. Drag and drop a variable from the Variables List onto any cell or edit a cell and include one or more %variables% inside any text. It is assumed these variables are not used in formulas.

  2. Add a Cell Assignment. You would use this method if the cell is used in a formula. Select a cell in the Spreadsheet and click the Add button from the Before Calculation Assign Variables To Cells list. The Assign Cell form will be displayed. Enter a value or select a %variable% from the Assign From list. You can enter a value in the spreadsheet cell itself as a placeholder to allow you to see formatting/formula results etc. When the Automation executes the cell value will be replaced and all formulas re-calculated. You can create any number of Cell Assignments.

Saving The Spreadsheet (optional)

Enable the Export File option.

From the Save As Format list select the type of file to save when the Automation Executes. The spreadsheet can be saved as:

  • Excel File
  • CSV File
  • PDF Document
  • HTML File

When the Automation executes, the spreadsheet template will be used to create a file in the above format. All %variables% and Cell Assignments in the spreadsheet will be replaced and formulas re-calculated.

If Excel or PDF format is selected then you can optionally specify a Password. The recipient of the file will need the password to open it.

Specify the Save To folder - click the ... button to select a local file or use %Root% to save it in the default ThinkAutomation location.

Enter a File Name to save the spreadsheet as (the extension will be added automatically based on the Save As Format).

If Ensure Unique File Name is enabled then ThinkAutomation will add a timestamp to the filename to ensure it is unique within the Save To Folder.

If Delete File After Message Is Processed is enabled then ThinkAutomation will remove the file when the Automation completes for the current message. This is useful if you wish to use the document in the Automation (for example, to send the spreadsheet as an attachment with the Send Email action), but do not need to keep a local copy afterwards.

You can assign the saved path & filename to a variable by selecting the variable from the Assign Saved File Path To list. You can then use this variable in the Attachments entry on Send Email actions or in any other way.

Reading Back Cell Values

After all Cell Assignments have been made and formulas re-calculated you can optionally read cell values back and assign to Automation %variables%.

For example, you could create a quotation spreadsheet that adds %qty% and %price% variables and then read back the 'total' cell to a %variable% which you can then use further in the Automation.

Select a cell in the Spreadsheet and click the Add button from the After Calculation Assign Cells To Variables list. The Assign Cell form will be displayed. Select a %variable% from the Assign To list. You can create any number of Variable Assignments.

Assign CSV Data

You can optionally assign the spreadsheet data in CSV format to a variable. Select the variable from the Assign CSV Data To list. Enable the Use Formatted Values For CSV if you want to return data as it has been formatted, otherwise unformatted data is used.

This option is useful if you want to use the Spreadsheet action to simply create a table of data that you can then use further in the Automation. For example, you may want to include a table in an outgoing email. Use the Spreadsheet action to create your table and assign the CSV data to a variable. Then use the Set Variable action with the Convert CSV To Markdown Table operation and include the Markdown table in your outgoing email body.