How to Combine Multiple Google Sheets Imports Into One Master Sheet

Before starting, make sure you have the following in place.

Step 1 — Get the IDs of your source spreadsheets

Open the first source sheet in your browser and look at the URL:

https://docs.google.com/spreadsheets/d/2bN6MVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgVE2upms/edit

The ID is the long string between /d/ and /edit:

2bN6MVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgVE2upms

Copy the ID and save it somewhere. Repeat for every source sheet you want to combine. While you are there, also note the exact tab name (e.g. Sheet1) of the all the tabs inside each source that hold the data you want to import — you will need this in Step 3.

Step 2 — Open the Apps Script editor in your Master sheet

Open the Master sheet and, in the top menu, click Extensions → Apps Script.

A new tab opens with the Apps Script code editor. Delete any default content already in the editor so you start with an empty file.

Step 3 — Paste the script

Paste the code below into the editor. Replace each YOUR_SOURCE_SHEET_ID placeholder with an ID you copied in Step 1, and update sourceTab and masterTab to match your tab names.

const SOURCES = [
  { id: "YOUR_SOURCE_SHEET_1_ID", sourceTab: "YOUR_SOURCE_TAB_NAME", masterTab: "TAB_NAME_TO_USE_IN_IMPORT SHEET" },
  { id: "YOUR_SOURCE_SHEET_2_ID", sourceTab: "YOUR_SOURCE_TAB_NAME", masterTab: "TAB_NAME_TO_USE_IN_IMPORT SHEET" },
];

function pullAllData() {
  const master = SpreadsheetApp.getActiveSpreadsheet();

  SOURCES.forEach(({ id, sourceTab, masterTab }) => {
    try {
      const sourceSheet = SpreadsheetApp.openById(id).getSheetByName(sourceTab);

      if (!sourceSheet) {
        console.log(`Tab "${sourceTab}" not found in spreadsheet ${id}`);
        return;
      }

      const data = sourceSheet.getDataRange().getValues();

      if (data.length === 0) {
        console.log(`No data in "${sourceTab}" from spreadsheet ${id}`);
        return;
      }

      let masterSheet = master.getSheetByName(masterTab);
      if (!masterSheet) {
        masterSheet = master.insertSheet(masterTab);
      }

      masterSheet.clearContents();
      masterSheet.getRange(1, 1, data.length, data[0].length).setValues(data);
      console.log(`Pulled ${data.length} rows into "${masterTab}"`);

    } catch (e) {
      console.error(`Failed to pull from ${id}: ${e.message}`);
    }
  });
}

Each entry in SOURCES represents one source sheet:

Add as many entries as you have source sheets. Save the script with the floppy disk icon or Ctrl+S.

Step 4 — Run it once manually to grant permissions

At the top of the editor, make sure pullAllData is selected in the function dropdown, then click Run.

Google will prompt you to authorise the script:

1. Click Review permissions.
2. Choose the Google account that owns the Master sheet.
3. Click Allow to grant access to your spreadsheets.

After authorising, the script runs. Open the execution log at the bottom of the editor and check that you see Pulled X rows into "..." for each source. Errors are printed there too if any source fails.

Step 5 — Set up the automatic trigger

In the Apps Script editor, click the clock icon in the left sidebar to open Triggers.

In the bottom right corner, click + Add Trigger and fill in the form:

Click Save.

Google will show the same authorisation prompt as in Step 4 — this time for the trigger itself. Click Review permissions, and click Allow again. The trigger will not run on its schedule until you complete this second authorisation.

Once both permissions are granted, the Master sheet will pull data from every source on the schedule you chose, with no add-ons and no further changes needed.

Troubleshooting

If the Master sheet is not updating as expected, check these first.

If the issue persists, contact support@profitmetrics.io