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.
In some specific use cases, you want to display data on your generated files calculated from complex formulas on your Google Sheets template. With Form Publisher, you can insert formulas behind your markers to show not only raw information but also calculated data, like for example the number of days between two dates, or the cost of a product based on the number selected by the user on your form.
This tutorial will show you how to use this option on your spreadsheet and configure Form Publisher.
What you'll learn
In this tutorial, you will learn how to automatically generate with Form Publisher a spreadsheet with modified information from the form.
Time to complete
Between 15 min & 30 min to set up (but depends on how much operations you want to set up on your form's data).
First select 'Sheets' as the template type in Form Publisher. Then just configure Form Publisher as you like.
Running a simple example
In our example, we will show you how to configure an invoice generator for hotel reservation. So our form looks like that:
To help you get started with this tutorial, click on the button below to copy our template:
It contains the spreadsheet template and the Google Form. To make it work, you will need to set up Form Publisher on your copy of the form. You can reuse it to configure your own template.
1. Add a new sheet to your responses spreadsheet (optional)
This is not mandatory, but as you probably don't want your main responses sheet to be spoiled by your calculations, we recommend to create a new sheet:
Otherwise you can also write your calculations directly on the main template sheet.
2. Save your markers data
To use formulas on your markers data in your spreadsheet, save each marker individually in one cell. 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 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.
Note: When you setup your formula, the cells will be filled by an error message like '#VALUE!'. Don't worry, it is only because markers are interpreted as text in your spreadsheet template, but when the data replace markers (in the generated document), all your formula will be calculated.
In our example, we want to convert the hotel reservation into an invoice. In the new sheet dedicated to your operations, set up the price for every service, rooms of two adults, rooms of two children and breakfast as following:
To calculate the price of the reservation, we need to know the number of nights that will be booked and the period of reservation. Note that our price also grows during summer.
So first, we use this formula: '=DAYS360(start_date, end_date' to calculate how many nights will be booked:
To set the time factor, we check the month of the reservation by using '=MONTH(date)' that will give you the month number. Then, we check if the month is July or August, if so we change the factor to 2* to reflect the price increase:
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.
Here's how our spreadsheet looks like after adding all the calculations:
4. Set up the main template sheet
On the main sheet, design your invoice as you want, and fill it with data from the operation sheet:
As we are generating a PDF file, we don't want to include the calculation sheet. So best will 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 select 'Hide sheet':
Testing the example
If John Doe wants to book the hotel for his family from 7/7/2017 to 7/21/2017, he will first fill the form as follows:
When he submits the form, Form Publisher will generate our invoice in PDF (only the visible sheet) and the spreadsheet file (including the hidden operations sheet):