Under construction ...
With Ultradox you can create invoices for different customers with the help of a time sheet. Write down the services for your customers in a Google spreadsheet with date, description, working time and hourly wage and, if required, select a customer for whom you want to create an invoice as a PDF file.
- Filtering, calculating and aggregating lists
- You have already added Ultradox to your account so that you can create new Ultradox files from Google Drive
- Get an overview of the possibilities of our List building blocks
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.
Add a Load worksheet building block from the Google Sheets section to your automation and create an empty Google Spreadsheet document that Ultradox will open in a new browser tab. Create your timesheet here with the columns Date, Customer, Service, Hours and Hourly rate. Set the columns to the appropriate format for date, number format and currency and delete all columns that are not used to minimize the list of variables.
Ultradox loads all variables of the table document in the source variables section in the Load worksheet building block.
In the list of variables in the Output variable section, the format is displayed at the end of each line behind the Google Sheet icon (, , etc.). If no format could be assigned, a
? is used.
Then we add a Filter building block to the automation to filter out all entries that are not for the corresponding customer.
To do this, click on the filter icon in the output variable section of the Load Worksheet Block and select the
rows list via the dialog and specify the conditions for the filter.
Select from the dropdown menu the corresponding entries,
is a text, (is)
equal and enter e.g. the condition
floreysoft in the field.
To determine the working time amounts for the selected customer, add a calculation module by clicking on the Calculations icon in the initial variable section of the Filter building block.
Select the list for which the calculation is to be performed and specify the attributes: the variable name is
amount and the format is
//Always use the prefix to get or set values
entry.amount = Number(entry.hours) * Number(entry.hourlyRate);
Finally, we determine the sum of the amounts to be invoiced to the customer.
To do this, add an Aggregate building block by clicking on the Aggregate icon in the initial variable section of the Calculation building block.
In the dialog box, select the list for which the aggregation is to be performed and enter the variables: here is the name of the variable
sum and the format
// Use the entry prefix to access the current entry
total += Number(entry.amount);
Generate invoice as PDF
Now create a template for your invoice by adding a ...to PDF building block from the Document templates Section to the end of the automation. Ultradox creates an empty Google Doc in a new browser tab.
Ultradox Template Editor
In the Google Doc, create a table with the titles Date, Description, Hours and Amount, an empty row in which the variables are formatted and inserted using the Template Editor, and a row for the Total at the end of the table.
Open the Ultradox Template Editor in the sidebar via Add-ons in the menu bar to be able to select the corresponding variables from the automation for the invoice template.
The Template Editor links Ultradox with Google Docs. It gives you access to all variables in your Ultradox automation and helps you to create templates in Google Docs.
In the Template Editor, choose the Variables tab to be able to select all entries from the Timesheet spreadsheet.
Then click the cursor in the table field below the Date header and select
rows.date from the list of variables and set the formatting (
Date), Output pattern (
medium) and Output locale
English (United States) via the dialogue and confirm with Insert.
For the table field below the Description header, choose
rows.description and Apply with default settings.
For the table field under the Hours header, choose
rows.hours and enter a hash
#.# for Output pattern.
For the table field below the Amount header, choose
rows.amount and set the
Currency format, enter
USD for Output pattern and Output locale
English (United States).
For the table field for the total, choose
total and set the format
EUR for Output pattern and Output locale
English (United States).
For a preview of your invoice template click on the Preview icon in the sidebar menu of the Template Editor and Ultradox will open the generated PDF in a new browser tab.
What you have learned
- Filter lists according to criteria
- Calculate List Entries
- Aggregate List Entries
Find out more about
Further information about the use of our List building blocks can be found in the following instructions:
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: 13.02.21