Google Sheets: import data from multiple sheets automatically

See in this article:

1. Import data from several sheets of one spreadsheet
2. Import data from new sheets in a source spreadsheet automatically
3. Import data from several spreadsheets into one spreadsheet

You can use the Google Sheets data flow to merge multiple sheets of an internal or external Google Sheets doc into one master view. 

There are two main cases when you need to combine data from several sheets:

1. Import data from several sheets of one spreadsheet (e.g. monthly analytics).
2. Import data from several spreadsheets into one spreadsheet (e.g. combine suppliers' or customers' data in several spreadsheets into one master sheet).

For each of these cases, you can use Coupler.io and this guide.

Import data from several sheets of one spreadsheet

For this purpose, you need to set up a Google Sheets data flow and complete the following steps:

1. Select "Google Sheets" as a source.
2. Connect your Google account.
3. Select the spreadsheet you would like to import several sheets from.
4. Select several sheets you would like to combine data from in the 'Sheets' field:

Example #1 (both sources have the same columns):

Example #2 (some columns are the same for both sources, and some are unique per each source):

Import data from new sheets in a source spreadsheet automatically

If you need to add more sheets in the future and their names have the same pattern, you can specify a regular expression instead of manually selecting the sheet name from a list:

Learn more about regular expressions to define what expression you need to specify to fit your needs. Here is the list of most common cases:

• All sheets: .*
• 2021-01, 2021-02, 2021-03, ...: 2021.+
• 2020-12, 2021-01, 2021-02,...: 202.+
• Dec 2020, Jan 2021, Feb 2021, All 2020, 12-2020, 01-2021: .*202.$
• All sheets with a name that starts with "sales data": sales data.+
• All sheets with a name that contains “sales data”: .*sales data.*
• All sheets with a name that ends with "sales data": .*sales data

Import data from several spreadsheets into one spreadsheet

If your data is stored in different spreadsheets, you need to follow the next steps:

1. For each spreadsheet, create a separate Google Sheets source in your Coupler.io data flow. See more on Combining data from multiple sources.
2. Preview & transform your data based on your needs. For example, this step lets you rename your columns for a unified final dataset, filter/sort the data in any way, add additional formula-based columns, and much more. See more on Preview data and transformation.
3. Combine the spreadsheets into one using the preferred data combining mode.