Multiple Google Sheets' Sources

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:

• 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.

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