Multiple Google Sheets' Sources

Updated on:
September 6, 2023

Sometimes you might need to pull data from multiple Google Sheet tabs or multiple Google Sheets files. This guide will show you how to set up a Google Sheet tab that will work with Portant and combine information from multiple Sheets into the same Google Sheet tab.

In this article you’ll learn how to:

  1. Set up a Google Sheet Source that combines data from multiple sheets/tabs
  2. Add formulae to sheets which don't display an error when blank

Set up a Google Sheet Source that combines data from multiple sheets/tabs

To set up a workflow that uses data from 2 or more different sheets, first you need to combine all the data into one Google Sheet tab. This could be a new tab in an existing spreadsheet or you could make a new spreadsheet to combine all the data into.

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

an example of the Import Range Google Sheets formula
  • 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


You can also import data from the same spreadsheet using the Array Formula formula, you can use this formula to import ranges of cells from other tabs:

  • To import cells from columns A through to C from sheet 1 into another tab in the same spreadsheet (for all rows), you enter: =ARRAYFORMULA("sheet1!A:C")

After you have combined all the data into one spreadsheet using the =ImportRange() or =ArrayFormula() functions you are ready to connect the spreadsheet to Portant.

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 data to be entered. When you copy the formula, the blank rows can sometimes display an error or a zero, which can interfere with Portant. 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 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

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 started
Most Popular Templates

Save hours of manual work in just a few clicks

Transform your work life with responsive, fully customizable, and easy-to-use templates.