Working With Spreadsheet templates

Spreadsheet templates are available for use in the following activities that involve working with spreadsheets or workbooks:

Microsoft 365:
Google Workspace:

Why use templates?

When do you need templates? For example, if the workbook or spreadsheet you need in your automation is not yet available at design time. Instead of selecting a specific spreadsheet in an activity, you select the output variable from a previous activity, and use a template to indicate what data to look for.

Consider the following example:

Every day at 11:00 am, an .XLSX file is uploaded to your OneDrive. This spreadsheet contains a list of incoming products and always has the same structure, with the following columns: Product code, Product, Qty, Price/Kg, Total Price, Shipping Date. At the end of each day, you need to read content from this file and write it in a new spreadsheet.

The challenge? At design time, you don't have the .XLSX file you need, because it's not yet available. This is where templates come in handy. You can use a template .XLSX file as a placeholder for future files, because it has the same structure as the files you process with your automation: same sheet names, same column names, etc.

Example

Add a For Each File/Folder activity to your workflow.
- Product list is the folder in your OneDrive where an .XLSX file is uploaded daily at 11:00 am:
In the Body of the For Each activity, add a Read Range activity.
- For the Workbook field, add the CurrentItem variable. This represents each item (file) identified through the For each activity.
- Use Workbook sample to add a template file that has the same structure as the files you need to process daily. In this example, the following column names:
Add a Create Workbook activity.
Add a Write Range activity.
- For Workbook, select the output variable of the Create workbook activity.
- For What to write, select the output variable of the Read Range activity. This populates the new workbook with content read from the files uploaded daily to your OneDrive:

Errors

If the data available in the template file doesn't correspond to the data found in the read files, an error message indicates what went wrong. For example:

Error: UiPath.MicrosoftOffice365.Excel.ExcelException: Column names or order do not match. Individual errors: at index 3:
                  expected: Product ID but got: ID.

On this page

Why use templates?
Example

Was this page helpful?

PREVIOUSTechnical references

NEXTWorking with SharePoint lists