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.
Comments
12 comments
You mention to add a new sheet to the responses spreadsheet for the calculations, but shouldn't it be the invoice template where you add the calculations?
Hi SensorsONE,
You can either do your calculations on a separate sheet as explained from points 1 to 3, or calculate directly on the invoice template (see point 4.). Both will work! We have added this information on the article, thanks for your comment.
Is there a way to perform simple date calculations in a Google Doc?
Hi Randy Baril,
Unfortunately, we did not find anyway to perform calculations in a Google Doc yet. We will continue to look for a solution and we'll keep you updated.
In the meantime let us know if you manage to pull this up!
Is there a way to have the calculations done outside Google Docs and then send the results back to Form-Publisher through some web response before the PDF is created?
What we are trying to to do programmatically: automate formula creation, vlookup contents, personalized messages/results/conclusions for aggregated/anonymous data.
Hi,
There is no way to get out of the Google Drive environment so (slide/sheet/doc) bfore the pdf creation, and the only type of file that allows formulas is Google Spreadsheet.
I need to combine Form Publisher's advanced formulas on a second sheet with Yet Another Mail Merge's ability to send an HTML email from my Google drafts folder.
I can pull question data into the email template using the <<Markers>> but how to I access the VALUE data in a formula populated cell in my second 'operations' sheet, as shown in the Hotel Reservation tutorial?
Thanks in advance
Hi,
You can only pull this data in another sheet of your template, not elsewhere.
I've been using Form Publisher successfully for a year now to generate new purchase order spreadsheets and pdf copies as well as emailing them to the relevant people. However I can not figure out how to simultaneously output the data to a running spreadsheet of all purchase orders. So I've been manually entering the relevant data into my running log spreadsheet after each new PO spreadsheet is created. How to automate that?
Will the calculated result also populate in the form on screen so the user can see the result of any calculations before they hit submit which raises the relevant off etc.?
Hi,
No those calculated result are done on the server side once the user submit is data.
Hello! Is there a way to use formula to auto populate a date schedule? We use form publisher for pet boarding and would like a sign off for each day they are here, so the client fills out the start and end date, similar to above example, but each date in-between would need to get populated on spread sheet cell. thank you!
Please sign in to leave a comment.