This article will explain how to insert markers formulas in your Google Sheets template. Form Publisher will then display the calculated data in your generated document. Please read this tutorial to learn more about this feature.
Use advanced formulas
timer Between 15 min and 30 min.
- Insert advanced formulas in your Google Sheet
- Generate a PDF
1. Create your template (Google Sheet)
2. Insert markers formulas
3. Submit your form to receive the generated file
It contains the 'Hotel Reservation' Google Form and the Google Sheet template. Set up Form Publisher on your copy of the form.
1. Add a new sheet to your responses spreadsheet (optional)
We recommend to create a new sheet if you want to separate your main responses sheet and your calculations. Click ‘Add Sheet’:
In our example, we decided to create a new sheet called 'Operations'.
2. Save your markers data
To use formulas on markers data in spreadsheet, save each marker individually in one cell. In our example, we created 5 markers in our new sheet called 'Operations':
Then, use those cells set up your formula.
3. Set up formulas
Once you saved all the data in these cells, you can start to make operations on them. Any spreadsheet formula will work, from the simplest operations to some really complex ones. You can check for the function you need in the list of all formulas supported in spreadsheet.
In our example, we want to convert the hotel reservation into an invoice. In our sheet called 'Operations' we set up the price for every service:
- Room of two adults
- Room of two child
- Breakfast price
3.1 How to calculate the number of nights reservation?
To calculate the price of the reservation, we need to know:
- The number of nights that will be booked
- The period of reservation (as we decided that our price also grows during summer)
The formula is: =DAYS360(start_date,end_date)
This formula will calculate how many nights will be booked:
3.2 How to know the period of reservation?
To know the period of reservation we need to know the month number.
To know the month we use this formula: =MONTH(date)
3.3 How to know the time factor?
To set the time factor, we check if the month is July or August. In our example:
- If the month is between January and June OR between September and December the price will be 50$/night
- If the month is between July and August the price will be doubled so 100$/night
So first, we use this formula: =IF(AND(month_number > 6, month_number < 9),2, 1)
To help you better understand, this formula means: "If the month chosen is between January and June AND between September and December the price will be 1 (the real price). Otherwise, it will be 2 (doubled price).
3.4 How to calculate the other values?
After that we multiply our factor by the number of nights, the number of rooms and the price of the room.
In exactly the same way, we calculate the price for all the breakfast. To get the total, we add the price for rooms to the one for breakfast.
After adding all the formulas, our Google Sheet template looks like that:
4. Set up the main template sheet
On the main sheet (the invoice you want to send to your recipients), design your invoice as you want, and fill it with data from the 'Operations' sheet.
In our example, we completed each cell corresponding to our sheet called 'Operations':
As we are generating a PDF file, we don't want to include the calculation sheet. So best would be to hide this sheet in our template. Thus it won't appear anymore in the generated PDF, but you can still access it from the generated Google Sheets. To do so, simply right click on this sheet, then click 'Hide sheet':
5. Submit your Google Form to test
Once you have configured Form Publisher click on the eye at the top right corner:
A window opens. Fill the information needed, click ‘SUBMIT’:
A confirmation message appears:
Finally, you received an email with the invoice as PDF file: