Generate invoice

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.

Topics

  • Filtering, calculating and aggregating lists

Prerequisites

  • You have already added Ultradox to your account so that you can create new Ultradox files from Google Drive

Goals

  • Get an overview of the possibilities of our List building blocks

Instructions

Create timesheets

Load all rows from worksheet 2017 in spreadsheet

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.

Filter list

Only keep items in rows, that match the condition “floreysoft

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, customer, is a text, (is) equal and enter e.g. the condition floreysoft in the field.

Perform calculation

Perform calculations to add attributes to each entry the list rows

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

The JavaScript code that performs the calculation is entered in the Calculation field:

//Always use the prefix to get or set values

entry.amount = Number(entry.hours) * Number(entry.hourlyRate);

Performing Aggregation

Perform aggregations on all entries in the list rows

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

The JavaScript code that executes this is entered in the Aggregation field:

// Use the entry prefix to access the current entry

total += Number(entry.amount);

Generate invoice as PDF

Generate PDF from Vorlage Rechnung

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.

VARIABLEN

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 Currency, enter 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: 2/13/21