Portant Docs
Launch PortantInstall App in Hubspot ↗︎
Portant Docs
Portant Docs
  • 👋Welcome
  • 🟢Product Updates
  • 🌱Getting Started
    • How to Sign up
      • Sign-up without a Google account
    • Quickstart
    • Ask for Help
      • Support Tickets
      • Support Codes
      • Screenshots
      • Developer Console
      • 400 Error
      • Resolve: API developer key is invalid error
      • How to request a transfer of your Portant account
    • Templates
    • Delete Multiple Workflows at Once
  • How to Set Up Your Account's Global Formatting Settings
  • Change Language Settings
  • 💫Personalise Documents
    • Adding Hyperlinks
    • Adding Images
    • Use custom tag names
    • Tag Formulas
      • Global tags
      • Tag Formulas
      • Tag IF Statements
    • Invalid Tags
    • Portant AI Content
    • Dynamic Google Sheets Charts
    • Tag Checker
    • Add online payment links
    • Add Stripe Payment Links to Your eSign Portal
  • 🌐Sources
    • Google Forms
      • Add formula fields to your Google Form workflow
      • {{tags}} for Google Forms
    • Google Sheets
      • Google Sheets Auto-create
      • How to source data from multiple Sheets
      • Saving Document Links to your Sheet
      • Data Grouping
    • HubSpot
      • How to install the Portant App in HubSpot
        • How to Add Portant App Cards to HubSpot
      • Create a Document in HubSpot
      • How to manage and select HubSpot Properties
        • Full list of available HubSpot Properties
      • Trigger HubSpot Workflows from Portant
      • Add a Portant Action to a HubSpot Workflow
      • How to view created documents in HubSpot
      • Add multiple line items into a single document
      • Add Deal Tags using Portant document statuses
      • Portant eSignatures + HubSpot
      • Review documents using the Review Block in HubSpot
    • Zapier
    • Glide
    • Webhooks
      • Webhook Data Grouping
    • Xero
    • Copper
      • Installing the Copper integration
      • Create your first Copper workflow
      • Generating your first document from Copper
    • Typeform
    • Tally
  • 💡Use Cases
    • eSignatures Requests
    • Incident Report
    • Quarterly Business Reviews
    • Invoices
    • Convert Form Responses to PDFs
  • ✒️eSignatures
    • Getting Started
    • Request Signatures (without a source)
    • Request Multiple Signatures
    • Customise Signature Request Email
    • Create Preview Links for Signature Requests
    • Resend signature requests
    • Cancel Signature
    • Add Audit to eSignatures
    • Request Info On Signing
    • eSigning Laws & Compliance
      • United States: E-SIGN Act and UETA ✅
      • Canada: PIPEDA and Electronic Transactions Act ✅
      • EU: eIDAS Regulation for Electronic Signatures ✅
      • UK: eIDAS Regulation and Electronic Communications Act 2000 ✅
      • Australia: Electronic Transactions Act (ETA) ✅
      • New Zealand: Electronic Transactions Act (ETA) 2002 ✅
      • Israel: Electronic Signature Law 2001
  • ⚡Automation
    • Workflow Filters
    • Re-run an automation
    • Review Block
    • Converting docs to different file types
      • Microsoft Word (.docx)
      • Microsoft Powerpoint
      • Create PDF outputs
      • Save an output as a Microsoft Excel file (.xlsx)
  • ✉️Emails
    • Share a Doc via Email
    • Email Tracking
    • Send from aliases
    • Send Outlook Emails
    • Customize Your Email Content with HTML Editing
  • 📄Outputs
    • Customize Output File names
    • Auto-filing Output Files
    • Changing Output Folder
    • Search and filter your outputs
  • Deleting Outputs in Portant
  • 🎁Portant Add-ons
    • Data Merge
      • How to set-up a Google Sheets source
        • Data Merge Settings for Google Sheets sources
        • Automatically create documents
      • How to set-up a Google Forms source
        • Data Merge Settings for Google Forms
        • Automatically create documents
        • Edit Response and Pre-filled Response Links
      • Inserting Images
      • Advanced Settings
        • Editing tag names
        • Timezone, date and time format settings
        • Editing the Output Folder
        • Conditional logic for emails
        • Conditional Logic for Data Merges
        • Data Grouping in Portant Data Merge
    • Document Merge - Trello Power-up
  • 👥Teams
    • Creating Your Team
    • Sharing Workflows
    • Creating Folders in the Workflows Tab
    • Managing team permissions
    • Whitelabeling eSign Portal
  • 💳Billing
    • Update Billing
  • Developer
    • Build with Portant
Powered by GitBook
On this page

Was this helpful?

  1. Sources
  2. Google Sheets

How to source data from multiple Sheets

How to combine multiple sheets into one data merge using IMPORTRANGE or ARRAYFORMULA functions

PreviousGoogle Sheets Auto-createNextSaving Document Links to your Sheet

Last updated 5 months ago

Was this helpful?

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 that don't display an error when blank

  3. Use an alternative like that’s much simpler than formula combination to sync live data from multiple sheets (and keep your datasets live, on refresh straight from the source)

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, 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 formulas 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 to add formula 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 formulas for a Full-Proof way of preventing Errors or "0" values. =IFERROR((IF(A1="","",A1*100)),"")

Use Coefficient to Sync Live Data From Multiple Sheets & Sources

Coefficient makes linking multiple Google Sheets data a lot easier, more straightforward, and automatic.

With Coefficient, you can choose your Google Sheets data source file, add filters to refine the information you want to pull in, and import the data with a few clicks, and use GPT Copilot to write formulas or build pivots and charts – all from within one easy-to-use Google Sheets extension.

Updating your linked spreadsheets is also a breeze since Coefficient lets you configure auto-refresh schedules. Not only do these auto-refresh schedules allow you to keep your Google Sheets synchronized, but they also allow you to power each sheet with live data directly from your source (and keep it on refresh), whether it’s your CRM, accounting platform, and more.

All of this means – no more manual updates when your data changes or gets new information. Here’s a quick 2-minute video walkthrough of the process.

We’ll walk through a similar example below, with images and step-by-step details.

Importing Data from Another Google Sheet with Coefficient

Let’s go over how Coefficient can help you combine multiple spreadsheets into one using an easier and more streamlined process.

Once you have Coefficient installed, click Import Data on the Coefficient pane.

Then, select Google Sheet.

Choose any Google Sheet you want from your Google Drive or other documents you have shared access to.

For this example, let’s pull up the same User Subscriptions list we used in the previous steps and click Select.

Coefficient takes a few seconds to access the data and read the column information. You’ll then see an import panel and the option to filter the data as it comes in.

Once you’re done, click Import.

There you have it. Coefficient imports your data almost instantly, and you don’t even need to deal with functions and formulas throughout the entire process.

So maybe now you’re wondering, “How can I get Google Sheets to auto-update a reference to another sheet?”

Coefficient has you covered since you can automatically refresh that data on a schedule, so you won’t need to do it manually.

The IMPORTRANGE solution allows you to import entire columns at once, but you’ll need to specify bounds for it or it will disrupt your dataset if your data moves too much.

Also, IMPORTRANGE doesn’t offer a built-in way to filter data, so you’ll need to do this separately.

Coefficient takes care of both problems for you by building the filter into the import, including importing any populated rows or columns seamlessly.

If you’re looking to build reports based upon the multiple tabs of data you’ve pulled into one sheet, you can take advantage of GPT Copilot in Coefficient, which can build formulas, pivots, and charts for you. Here’s an example of turning simple text instructions into formulas.

Formula Builder will return a formula like below.

And just like that, you’ve blended data from multiple sources and analyzed it, all without ever leaving Google Sheets!

Feedback and feature suggestions

You can .

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

🌐
get started with Coefficient by submitting your email here
contact@portant.co
Coefficient
An example of the Allowing Access to a sheet
an example of the Import Range Google Sheets formula