Working With Spreadsheet templates

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

• Microsoft 365:

  • Read Range
  • Write Range
  • For Each Row in Workbook

• Google Workspace:

  • Read Range
  • Write Range
  • For Each Row in Spreadsheet

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

1. 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:

     
     

     

     
     

2. 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:

     
     

     

     
     

3. Add a Create Workbook activity.

   
   

   

   
   

4. 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.