Multiple Google Sheets' Sources

Updated on:
November 27, 2021

Sometimes you might need to have data from multiple sheets in a data merge. This guide will show you how to set up a data merge to combine information from multiple Google Sheets into the same data merge.

In this article you’ll learn how to:

  1. Set up a data merge for multiple Google Sheets Sources
  2. Add formulae to sheets which don't display an error when blank

Set up a data merge for multiple Google Sheets Sources

To set up a data merge that uses data from 2 or more different sheets, you need to select which sheet is going to be the one you use to combine the data in. This could bean existing spreadsheet or you could make a new spreadsheet to combine all the data into. Portant can only connect to one Spreadsheet, this means that the data needs to be combined into one  spreadsheet which is connected to Portant Data Merge.

an example of the Import Range Google Sheets formula

You can import data from other spreadsheets using the ImportRange formula, you can use this formula to import single cells or ranges:

  • To import cells A1 sheet 1 of the abcd123abcd123 spreadsheet, you enter: =IMPORTRANGE("https://docs.google.com/spreadsheets/d/abcd123abcd123", "sheet1!A1")
  • To import cells A1 through C10 from sheet 1 of the abcd123abcd123 spreadsheet, you enter: =IMPORTRANGE("https://docs.google.com/spreadsheets/d/abcd123abcd123", "sheet1!A1:C10")

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.

An example of the Allowing Access to a sheet

After you have combined all the data into one spreadsheet using the ImportRange function you are ready to connect the spreadsheet to Portant data merge and run the automation.

How Add formulae to sheets which don't display an error when blank

Sometimes you may need to copy a formula on multiple rows for future dat to be entered. When you copy the formula, the blank rows can sometimes display an error or a zero, which can interfere with Portant Data Merge. To avoid these errors you can use the following formulae.

To avoid errors you can use the IFERROR formula:
=IFERROR("no error", "error")
Returns the first argument if it is not an error value, otherwise returns the second argument if present, or a blank if the second argument is absent e.g. =IFERROR(A2*100, "")

To avoid zeros you can use the IF formula:
=IF(logical_expression, value_if_true, value_if_false)
Returns one value if a logical expression is 'TRUE' and another if it is 'FALSE' e.g. =IF(A1="","",A1*100))

You can also combine these formula for a Full-Proof way of preventing against Errors or "0" values.
=IFERROR((IF(A1="","",A1*100)),"")


Feedback and feature suggestions

We created Portant only a few months ago, so any feedback or suggestions you have for us would be greatly appreciated. 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

More than mail merge

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 startedAn animation showing a google document being created from a Trello card.