Google Forms Response Sheets' Sources

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:

1. Set up a data merge for a Google Forms Response Sheet
2. Add conditional content based on an answer in the form

Set up a data merge for Google Forms Response Sheet

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:

‍

Add conditional content based on an answer in the form

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")

• condition1 – The first condition to be evaluated. This can be a boolean, a number, an array or a reference to any of those.
• value1 – The returned value if condition1 is TRUE.
• condition2, value2, … – Additional conditions and values if the first one is evaluated to be false.

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

Feedback and feature suggestions

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