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.
Goal
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).
Prerequisites
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):
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.