Let’s say you had several spreadsheets saved in Google Drive, and we wanted to combine them into one spreadsheet; how would we do this?
As explained in 📺this video, we can use formulas, but this gets tedious when dealing with a large number of sheets. Instead, we can use a Google Sheet add-on known as Power Tools.
The first thing to note is that the data must have matching headers. They don’t have to be in the same order across all spreadsheets, but they need to match. We can combine the data in a static way or make sure that it’s dynamic by auto-inserting the formulas.
In our case, we are going to combine three spreadsheets that contain lists of companies. Here’s a look at one of the sheets to better understand what we’re working with.
There are two other similar sheets – Australian companies & USA companies. Now let’s combine them.
Combining Static Spreadsheets
These are the steps to follow when combining the data is a one-time operation that doesn’t need to update when any of the sheets changes:
- Launch Power Tools via Add-ons > Power Tools. (If you haven’t installed Power Tools yet, you can follow this link to install.
- In the sidebar on the right, select “Merge & Combine.”
- Select “Combine sheets” and click on “Add files from Drive” in the pop-up that appears.
- Add all the sheets from which you would like to fetch data.
- Make sure the boxes are next to the files and click “Next.”
- In the following pop-up that appears next, make sure that “Use a formula to combine sheets” is not checked. (This is because we are working with static data). Now click combine.
After a few seconds, a new sheet within the spreadsheet will appear with the combined data.
Combine Dynamic Spreadsheets
If we need the combined data to be dynamic, Power Tools can automatically insert the formulas we need. To do this, follow the steps we used in the previous example but, this time, make sure that the box labeled “Use a formula to combine sheets” is checked.
Upon combining, the sheet containing combined data will appear with errors since no user has granted permissions yet. (Remember we need to give access when using IMPORTRANGE). An extra sheet will appear, and we can then grant permissions there. That should correct the errors.
Since we’re using formulas, any changes to the source files will reflect on the combined sheet.
