Sometimes you might need to conditionally insert other content based on form answers. e.g. If the users selects an office location, add the address, phone number and email address for the location WITHOUT having to ask the user to select these details as well.
This guide will show you how to set up a Portant Workflow based on a Google Forms Response Sheet and add conditional content to it.
In this article you’ll learn how to:
To set up a data merge that uses a Google Form response sheet, you need to switch it on from the Google Forms. First open up your Google Form, then click the 'Responses' tab in the centre of the page. Then click the Google Form icon in the top Right corner of the page 'Create Spreadsheet'.
After you have clicked this icon, you can select if you'd like to make a new spreadsheet or add it as a tab in an existing spreadsheet.
This means, any time you receive a response for your form, the data will be inserted into this Google Sheet. Portant can then check the spreadsheet every 2 minutes and create documents any time it finds new data. To do this, when you set up your workflow in Portant, please select to turn on auto-create:
Ok cool. So what we have made so far is just a slightly more complicated version of a Google Forms integration. However, now I'm going to show you how to make it more powerful by addign conditional content based on answers in the form.
The first thing we need to do is to copy the response sheet into a new tab in your spreadsheet. We need to do this becasue when Google adds a new row into the response tab, it clears all the data in the existing row.
We recommend to use an array formula to do this: =ARRAYFORMULA(Sheet1!A:X) with A being the first column of your response and X being the last column of your responses
You can also insert the responses into a different spreadsheet you use =IMPORTRANGE("https://docs.google.com/spreadsheets/d/abcd123abcd123", "sheet1!A:X") The first time you connect to a sheet you may need to allow access , you only need to do this once per spreadsheet and then all other formula referencing this sheet will work.
After you have combined all the data into one spreadsheet using the =ArrayFormula() or =ImportRange() functions you are ready to add formulae to the other columns for your conditional content.
You might want to base your content on only one condition, but the majority of the time it will be based on multiple conditions. To do this we use the =IFS() formula: =IFS(condition1, value1, [condition2, value2, …])/. e.g. =IFS(A1>90, "A", A1>80, "B", A1>70, "C")
There is more information on this formula here: https://support.google.com/docs/answer/7014145
When you have written the condition and are happy with the result, copy and paste for the whole column. This will result in some errors for the blank rows. These errors will stop Portant Auto-create from working correctly, so we need to remove them by using the formula =IFERROR()
We can place the IFS formula in brackets and then add "", so it displays a blank value if there is an error, like this =IFERROR ( ( *IFS Formula* ) , "" ) When you have written the condition and are happy with the result, copy and paste for the whole column. This will resolve all the errors for the blank rows which were previously present.
There is more information about this formula here: https://support.google.com/docs/answer/3093304
We created Portant in 2021 and the feedback we have received since then has been very helpful and greatly appreciated. If you have any feedback please feel free to send us an email at contact@portant.co or use our contact form here: https://www.portant.co/support
Thanks,
Blake and James
You can do a lot more than just mail merge with Portant. Portant is a fully-fledged online document automation solution that can optimise many different document flows. You can use our online document automation platform to optimise your repetitive work, so you can get back to doing the work you enjoy.
Get startedTransform your work life with responsive, fully customizable, and easy-to-use templates.