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
- Configure a Google Form
- Knowing how to configure Form Publisher
- Understanding markers
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 creating 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 the spreadsheet, save each marker individually in one cell. In our example, we created five markers in our new sheet called Operations.
Then, use those cells to 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 works, from the simplest operations to some complex ones. You can check for the function you need in the list of all formulas supported in the 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 calculates how many nights will be booked.
3.2 How to know the period of the 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 is 50$/night
- If the month is between July and August, the price is doubled, so 100$/night
So first, we use this formula: =IF(AND(month_number > 6, month_number < 9),2, 1)
To help you understand better, this formula means that 'If the month chosen is between January and June and between September and December the price is 1 (the real price). Otherwise, it is 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 room's price.
In 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, 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 a PDF file.
