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.
When settings the maximum numbers of rows to 1 the variables will contain only a single value. A single line on the right of the variable will indicate that they just contain the value of the column of the loaded row
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.
Ultradox tries to detect the proper type for each column based on how you have formatted the columns in the sheet. Make sure that the type has been detected correctly and adjust the type if needed. This is especially important for columns holding dates.
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
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
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
Please make sure to set the correct language, time zone and currency both in Ultradox and Google Sheets so that your data can be loaded correctly and Ultradox will be able to produce the desired output. Further information under Internationalization.
