Video: how to enable formulas built by the Combine Sheets add-on

If you're about to pull data from multiple Google sheets into one using our Combine Sheets add-on, you will most likely try to get the result as a formula to keep it in-sync with the original tables. This video features 2 important steps you have to take before that formula starts working for you.

Video transcript: enable the functions used in the formula

When you join tables using the Combine Sheets add-on, you can make it put data together with a formula. This way, your resulting table will refer to all original sheets and return all changes automatically.

But before this happens, you should enable the functions used in the formula. In this video, I will show you 2 steps to do that and explain why this is necessary at all.

Combine Sheets: resulting message

When Combine Sheets pulls data using a formula, you'll see a short description and a demo of what to do to get a joint table. Let's open the resulting spreadsheet.

Contents of the resulting spreadsheet

This new file has two sheets. The one called Combined data is where your result will appear. For now, there are errors, since the functions employed by the add-on haven't been enabled yet.

These functions are:

  • a couple of custom ones built-in to the add-on
  • and a couple of standard ones for Google Sheets: QUERY and IMPORTRANGE

Let's go to another tab and see what we've got there:

  • a few notes of why you see errors on the second sheet
  • steps to follow in this particular order to turn on the functions
  • and some sort of a self-check: the statuses and colors will change once you enable everything
  • Spreadsheets and access statuses.

2 steps to enable functions

Step 1. Turn on custom functions

So! First things first, our custom functions won't work without the add-on.

Since the file is new, the add-on hasn't been run here yet — and that's exactly what you need to do — start the tool.

As soon as it loads, this status turns green and says Started. You may close the add-on now.

Step 2. Give IMPORTRANGE access

Then you need to fix these errors: they are actually thrown by the standard IMPORTRANGE function. It is used in spreadsheets to pull data ranges from file to file.

These errors mean that the function doesn't have the necessary rights to read from other files.

And that's the last thing you are to do: connect IMPORTRANGE to those spreadsheets.

We list all spreadsheets you combine right here, and you can see access statuses alongside them. Go ahead and click the first error, then press the Allow access button.

Once the function connects to the first spreadsheet, the cell will also turn green and say connected.

Then just repeat the same for the rest of the files.
Allow IMPORTRANGE access.

Get the result

When you're ready, return to the second sheet. You'll see all data perfectly combined by columns one under the other with the formula.

I truly encourage you to get your Combine Sheets or Power Tools from the Google store and try to combine as many tables as you need.

And, as always, if you have any questions, don't hesitate and contact us — we'll be happy to help.

You may also be interested in:

Post a comment



Thank you for your comment! When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to your query. We cannot guarantee that we will answer every question, but we'll do our best :)