Load worksheet

Insert data from a worksheet into documents or emails

Load all rows from worksheet My Worksheet in spreadsheet My Sheet

fileId
rows[].rowNumber
rows[].rowIndex
rows[].date

With this building block you can load assorted rows from a worksheet in a Google Sheet. The rows to be loaded can be specified by row numbers and can also be filtered.

Use cases

  • Create documents for each row in the sheet
  • Merge a table of data from the sheet into a document
  • Create a document with a formatted paragraph/page per row (aka Catalog merge)
  • Query a row based on a variable to lookup values (e.g. fetch customers name for a given email)

Configuration

Click on the bold parts in the title of the building block to configure the data to be loaded.

Click on given file to open a file picker that helps you to track down the Google Sheet you want to load and also gives you an option to create a new sheet from scratch. Creating a new spreadsheet will open the new sheet in a new browser tab.

After selecting the Google Sheet the first worksheet will be preselected. Click on the bold name of the worksheet in the title of the building block to load a different worksheet.

The first row of the selected worksheet will be treated as header row. The output variables showing up in the output section of the building block will be derived from the header row.

You will find all columns of the selected worksheet as variables. The three lines on the right of the variable indicate that each variable represents a list of values: they contain the data from all rows.

By default the block will load all rows from the selected worksheet.

Click on all rows for further configuration options.

You can specify the header row which will then be used to derive the output variable names.

You can also select the first row and the maximum number of rows to be loaded. This will allow you to embed just a selected set of rows into your documents.

You can also specify a query to only load rows that match a given filter.

Click on the Configure button to open the query builder.

The query builder will help you to compose the query. Select the column that you want to use to filter the data in the dropdown on the left.

If the selected column contains numbers you can then for example filter it either by entering a fixed value or by entering a variable representing a number into the blue field on the right.

Clicking on the Advanced tab in the query building will show you how the translated query will look like. Ultradox is using a query format that is optimized for performance.

If you are an expert you may want to adjust the compiled query according to your needs.

The configuration dialog also allows you to specify the name and type of the output variable for each column.

You can then select the format of the loaded values:

Loading unformatted data is the right choice if you want to for perform calculations on the data or if you want to format the values with Ultradox.

Load the formatted values if you just want to insert the data in exactly the same format as it shows up in your sheet.

If your cells contain formulas and you want to print the formula instead of the calculated value, select the last option

 Reload

Click on the reload icon to scan the template again to track down added or removed variables. If you have renamed your template on Google Drive clicking on this button will also update the document name in Ultradox.

 Edit

Use the edit icon to edit the template in a new browser tab.

Useful combinations

Merge a worksheet as a table into a document

Load all rows from worksheet Branches in spreadsheet Revenue

rows[].calendarWeek
rows[].branch
rows[].revenue

Generate PDF with revenue data

rows[].calendarWeek
pdf.file
rows[].branch
rows[].revenue

The first block will load all rows from the specified worksheet from the selected Google Sheet.

To merge the rows as a table into your document you may want to open the Ultradox Template Editor from the add-ons menu in Google Docs. The template editor helps you to insert a simple table into you template.

Lookup values

Load one matching row from worksheet Employees in spreadsheet Contacts

name
email

Send email with subject Report to ${email}

email

To lookup a value from a sheet, set the maximum numbers of rows to be loaded to 1 and configure a query that will fetch just one row for a given value, e.g. string:name = "${name}" in case you have a column called Name and a variable called name that contains the name of the employee.

If your worksheet has a column called Email you will then get the matching email in return.

Generate, upload and print PDF document for each row of a sheet

Load all rows from worksheet Employees in spreadsheet Contacts

rows[].name
rows[].email

Repeat for each item in rows[]

rows[].name
name
rows[].email
email

Generate contract PDF

name
pdf.file

Send contract PDF to employee

email
pdf.file

Print on HR printer

pdf.file

Upload contract to HR folder

pdf.file

Continue with next item

Questions and Feedback

If you have any comments on this page, feel free to add suggestions right to the Google document that we are using to create this site.

If you are not yet member of the Ultradox community on Google+, please join now to get updates from our end or to provide feedback, bug reports or discuss with other users.

Last Updated: 7/20/18