Sync Data Between Two Google Sheets with Apps Script: Keep Spreadsheets in Sync Automatically

Most Australian businesses reach the same breaking point with Google Sheets at some point. You have a master spreadsheet at head office. You have a separate spreadsheet for each branch, location, or team. Someone updates the master every morning and then manually copies the relevant rows across to the branch files. Or the branch files send their figures back every Friday and someone merges them into the master by hand.

This is not a workflow problem. It is a scripting problem — and it has a straightforward solution.

Google Apps Script can sync data between two Google Sheets automatically, on a schedule, without anyone touching a keyboard. Whether you need a one-way push from a headquarters spreadsheet to a branch workbook, or a two-way sync where changes in either direction are reflected in the other file, Apps Script handles both with a modest amount of JavaScript.

This guide covers the complete picture: how to open an external spreadsheet by ID, how to read and write ranges across files, when to choose one-way versus two-way sync, how to handle conflicts, how to set up scheduled triggers, and how Apps Script compares to using IMPORTRANGE for the same job. You will also find two practical, copy-paste-ready examples drawn from real Australian business use cases.

Accessing an External Google Sheet with openById()

Every Google Sheet has a unique spreadsheet ID embedded in its URL. For a sheet at https://docs.google.com/spreadsheets/d/1aBcDeFgHiJkLmNoPqRsTuVwXyZ/edit, the ID is 1aBcDeFgHiJkLmNoPqRsTuVwXyZ.

The SpreadsheetApp.openById() method uses that ID to open any spreadsheet the running user has access to — not just the one the script is bound to. This is the fundamental mechanism that makes cross-file sync possible.

function openExternalSheet() {
  var EXTERNAL_SHEET_ID = 'YOUR_SPREADSHEET_ID_HERE';

  // Open the external spreadsheet
  var externalSpreadsheet = SpreadsheetApp.openById(EXTERNAL_SHEET_ID);

  // Access a specific sheet tab by name
  var sheet = externalSpreadsheet.getSheetByName('Sheet1');

  // Or access the first tab
  var firstSheet = externalSpreadsheet.getSheets()[0];

  Logger.log('Opened: ' + externalSpreadsheet.getName());
  Logger.log('Rows with data: ' + sheet.getLastRow());
}

Access requirements: The Google account that authorises the script must have at least Viewer access to read from an external sheet, and Editor access to write to it. If the account does not have the correct access, openById() will throw a "You do not have permission to access the requested document" exception.

Finding the spreadsheet ID: Open the target spreadsheet in your browser, then copy everything between /d/ and /edit in the URL.

Reading and Writing Ranges

With both the source and destination spreadsheets open, reading and writing data uses the same getRange() and getValues() / setValues() methods you would use on any sheet.

// Reading a range from the source sheet
var sourceValues = sourceSheet.getRange('A1:E100').getValues();
// Returns a 2D array: sourceValues[row][column]

// Writing that data to the destination sheet
destinationSheet.getRange('A1:E100').setValues(sourceValues);

A critical performance note: every call to getValues() or setValues() is a call to Google's API. Reading 500 rows one at a time with 500 individual getValue() calls will hit quota limits and run for minutes. Always read and write entire ranges in a single batch using getValues() and setValues(). For large datasets, Apps Script processes batches of up to 2,000,000 cells per operation.

// Efficient: read all data in one call
var data = sheet.getRange(1, 1, sheet.getLastRow(), sheet.getLastColumn()).getValues();

// Inefficient: do not do this
for (var i = 1; i <= sheet.getLastRow(); i++) {
  var value = sheet.getRange(i, 1).getValue(); // one API call per row
}

One-Way Sync: Pushing Data from Source to Destination

One-way sync is the simpler and more common pattern. Data flows in a single direction — from a master or headquarters spreadsheet into one or more destination files. The destination is always overwritten with whatever the source contains.

This is appropriate when:

• A head office manages a product catalogue and branches need current pricing
• An admin team maintains a staff directory that field teams access read-only
• A data team curates a reporting dataset that feeds multiple consumer workbooks

Here is a complete one-way sync script that reads all data from a named tab in the source spreadsheet and writes it into the matching tab in the destination spreadsheet:

function oneWaySync() {
  // Replace these with your actual spreadsheet IDs
  var SOURCE_SHEET_ID      = 'YOUR_SOURCE_SPREADSHEET_ID';
  var DESTINATION_SHEET_ID = 'YOUR_DESTINATION_SPREADSHEET_ID';
  var TAB_NAME             = 'Products'; // The tab to sync

  try {
    var sourceSpreadsheet      = SpreadsheetApp.openById(SOURCE_SHEET_ID);
    var destinationSpreadsheet = SpreadsheetApp.openById(DESTINATION_SHEET_ID);

    var sourceSheet      = sourceSpreadsheet.getSheetByName(TAB_NAME);
    var destinationSheet = destinationSpreadsheet.getSheetByName(TAB_NAME);

    if (!sourceSheet) {
      throw new Error('Source tab "' + TAB_NAME + '" not found.');
    }

    if (!destinationSheet) {
      // Create the tab in the destination if it does not exist
      destinationSheet = destinationSpreadsheet.insertSheet(TAB_NAME);
    }

    // Read all data from source
    var lastRow    = sourceSheet.getLastRow();
    var lastColumn = sourceSheet.getLastColumn();

    if (lastRow === 0) {
      Logger.log('Source sheet is empty. Nothing to sync.');
      return;
    }

    var sourceData = sourceSheet.getRange(1, 1, lastRow, lastColumn).getValues();

    // Clear the destination sheet and write fresh data
    destinationSheet.clearContents();
    destinationSheet.getRange(1, 1, lastRow, lastColumn).setValues(sourceData);

    Logger.log(
      'One-way sync complete. ' + lastRow + ' rows synced to destination.'
    );

  } catch (e) {
    Logger.log('Sync error: ' + e.message);
    // Optionally send an email alert
    MailApp.sendEmail(
      Session.getActiveUser().getEmail(),
      'Sheets Sync Error',
      'The one-way sync failed: ' + e.message
    );
  }
}

Two-Way Sync: Keeping Both Spreadsheets Current

Two-way sync is more complex because it has to decide which version of a value is authoritative when both sheets have changed. It is the right choice when:

• Multiple teams each have their own workbook but also need visibility of a shared master
• Branch managers update local data, and HQ aggregates from all branches
• A mobile-accessible sheet and a desktop reporting sheet both need to stay current

The cleanest implementation of two-way sync uses a timestamp column in each sheet. When a row is modified, the editor updates the timestamp. The sync script compares timestamps and copies whichever row is more recently updated into the other file.

function twoWaySync() {
  var SHEET_A_ID   = 'YOUR_SHEET_A_SPREADSHEET_ID';
  var SHEET_B_ID   = 'YOUR_SHEET_B_SPREADSHEET_ID';
  var TAB_NAME     = 'Inventory';

  // Assume column A = unique ID, last column = timestamp (updated by users on edit)
  var ID_COL        = 0; // column index 0 = column A
  var TIMESTAMP_COL = 4; // column index 4 = column E (adjust to match your sheet)

  var sheetA = SpreadsheetApp.openById(SHEET_A_ID).getSheetByName(TAB_NAME);
  var sheetB = SpreadsheetApp.openById(SHEET_B_ID).getSheetByName(TAB_NAME);

  var dataA = sheetA.getDataRange().getValues();
  var dataB = sheetB.getDataRange().getValues();

  // Build lookup maps keyed by the unique ID in column A
  var mapA = buildRowMap(dataA, ID_COL);
  var mapB = buildRowMap(dataB, ID_COL);

  var updatesForA = [];
  var updatesForB = [];

  // Compare every row that exists in either sheet
  var allIds = new Set([...Object.keys(mapA), ...Object.keys(mapB)]);

  allIds.forEach(function(id) {
    var rowA = mapA[id];
    var rowB = mapB[id];

    if (rowA && !rowB) {
      // Row exists in A but not B — push to B
      updatesForB.push(rowA);

    } else if (!rowA && rowB) {
      // Row exists in B but not A — push to A
      updatesForA.push(rowB);

    } else if (rowA && rowB) {
      // Row exists in both — compare timestamps
      var tsA = new Date(rowA[TIMESTAMP_COL]);
      var tsB = new Date(rowB[TIMESTAMP_COL]);

      if (tsA > tsB) {
        // Sheet A version is newer
        updatesForB.push(rowA);
      } else if (tsB > tsA) {
        // Sheet B version is newer
        updatesForA.push(rowB);
      }
      // If timestamps are equal, no action needed
    }
  });

  // Apply updates
  applyUpdatesToSheet(sheetA, updatesForA, ID_COL);
  applyUpdatesToSheet(sheetB, updatesForB, ID_COL);

  Logger.log(
    'Two-way sync complete. ' +
    updatesForA.length + ' rows updated in Sheet A, ' +
    updatesForB.length + ' rows updated in Sheet B.'
  );
}

function buildRowMap(data, idCol) {
  var map = {};
  for (var i = 1; i < data.length; i++) { // skip header row
    var id = data[i][idCol];
    if (id) map[id] = data[i];
  }
  return map;
}

function applyUpdatesToSheet(sheet, updates, idCol) {
  if (updates.length === 0) return;

  var data = sheet.getDataRange().getValues();

  updates.forEach(function(updateRow) {
    var id = updateRow[idCol];
    var found = false;

    for (var i = 1; i < data.length; i++) {
      if (data[i][idCol] === id) {
        // Update existing row
        sheet.getRange(i + 1, 1, 1, updateRow.length).setValues([updateRow]);
        found = true;
        break;
      }
    }

    if (!found) {
      // Append new row
      sheet.appendRow(updateRow);
    }
  });
}

Setting up the timestamp column: Add an onEdit trigger to each spreadsheet that automatically stamps the current date and time into the timestamp column whenever a row is edited:

function onEdit(e) {
  var TIMESTAMP_COL = 5; // Column E (1-indexed for setValues)
  var TAB_NAME      = 'Inventory';

  var sheet = e.source.getActiveSheet();
  if (sheet.getName() !== TAB_NAME) return;

  var editedRow = e.range.getRow();
  if (editedRow === 1) return; // ignore header row

  // Write the current timestamp into the timestamp column for that row
  sheet.getRange(editedRow, TIMESTAMP_COL).setValue(new Date());
}

Handling Conflicts

A conflict occurs when the same row has been modified in both spreadsheets between sync runs. The timestamp approach above resolves most conflicts by deferring to the most recently modified version. However, you may want a more nuanced strategy for sensitive data:

Last-write-wins (default in the script above): The row with the newer timestamp is treated as authoritative. Simple and predictable, but a stale update from a slow editor can overwrite a more recent legitimate change if clocks are not synchronised accurately.

Log conflicts for manual review: Instead of automatically resolving, write conflicting rows to a dedicated "Conflicts" tab for a human to adjudicate:

function logConflict(sheetA, sheetB, rowA, rowB) {
  var logSheet = SpreadsheetApp.getActiveSpreadsheet()
    .getSheetByName('Conflict Log');

  if (!logSheet) {
    logSheet = SpreadsheetApp.getActiveSpreadsheet()
      .insertSheet('Conflict Log');
    logSheet.appendRow([
      'Timestamp', 'ID', 'Sheet A Value', 'Sheet B Value', 'Resolved?'
    ]);
  }

  logSheet.appendRow([
    new Date(),
    rowA[0],                // ID column
    JSON.stringify(rowA),   // full row from Sheet A
    JSON.stringify(rowB),   // full row from Sheet B
    'Pending'
  ]);
}

Field-level conflict detection: For more granular control, compare field by field rather than row by row. If Name and Phone are the same but Address differs, you might auto-merge the non-conflicting fields and only flag the genuinely conflicting ones.

Setting Up Time-Based Triggers for Scheduled Sync

Running a sync script manually defeats the purpose. Apps Script's time-based triggers let you schedule any function to run automatically — daily, hourly, or at whatever interval your business requires.

To create a trigger from the Apps Script editor:

1. Open your script project at script.google.com.
2. Click the clock icon in the left sidebar (Triggers).
3. Click Add Trigger in the bottom-right corner.
4. Configure the trigger:
5. Function to run: oneWaySync or twoWaySync
6. Event source: Time-driven
7. Type: Day timer (for daily) or Hour timer (for near-real-time)
8. Time of day: Choose off-peak hours (e.g., 3am to 4am AEST for overnight sync)
9. Click Save and authorise the trigger if prompted.

To set up triggers programmatically (useful when deploying across multiple spreadsheets):

function createSyncTriggers() {
  // Remove any existing triggers for the sync function to avoid duplicates
  var existingTriggers = ScriptApp.getProjectTriggers();
  existingTriggers.forEach(function(trigger) {
    if (trigger.getHandlerFunction() === 'oneWaySync') {
      ScriptApp.deleteTrigger(trigger);
    }
  });

  // Create a daily trigger at 3am
  ScriptApp.newTrigger('oneWaySync')
    .timeBased()
    .everyDays(1)
    .atHour(3)
    .create();

  Logger.log('Sync trigger created: daily at 3am.');
}

Recommended sync frequencies by use case:

Apps Script imposes quotas on trigger execution: for Workspace accounts, scripts can run for up to 6 minutes per execution and up to 9 hours of total trigger runtime per day. For large datasets, test your sync script's runtime before setting aggressive schedules.

IMPORTRANGE vs Apps Script: Pros and Cons

Before building a sync script, it is worth understanding when the built-in IMPORTRANGE formula is sufficient — and when it is not.

IMPORTRANGE

IMPORTRANGE("spreadsheet_url", "Sheet1!A1:F500") pulls a range from another Google Sheet directly into your spreadsheet as a live formula. No scripting required.

Advantages of IMPORTRANGE:
- Zero setup — works immediately in any cell
- Always reflects the source data in near-real-time (refreshes every few minutes)
- No code to maintain or debug
- No trigger quota to worry about

Limitations of IMPORTRANGE:
- Read-only. You can pull data in, but you cannot push changes back. Two-way sync is not possible.
- Performance degrades with large ranges (10,000+ rows). The formula can cause slow load times and "Loading..." placeholders.
- Breaks silently if the source spreadsheet is renamed, moved, or access is revoked — the cell just shows an error.
- No transformation logic. You get exactly what the source has. Filtering, mapping, or reformatting requires additional formula layers.
- One connection per authorisation. Each new IMPORTRANGE URL requires the recipient to click "Allow access".
- Not suitable for archiving. IMPORTRANGE is a live mirror, not a snapshot. If the source changes, the destination immediately reflects it — there is no way to preserve historical state.

Apps Script Sync

Advantages of Apps Script:
- Full read/write access — both one-way and two-way sync are possible
- Transformation at sync time — filter columns, map field names, reformat data as it moves
- Snapshot capability — copy data at a point in time and preserve it even if the source changes
- Logging and error handling — know exactly when a sync ran, how many rows transferred, and what failed
- Selective column sync — copy only the columns the destination needs, not the entire sheet
- Conflict resolution logic — decide programmatically which version wins when both sides change

Limitations of Apps Script:
- Requires writing and maintaining JavaScript
- Not truly real-time — runs on a schedule, so there is always a lag between a change and its propagation
- Subject to Apps Script quotas (execution time, trigger runtime, calls per day)
- If a trigger fails silently, data goes out of sync until the next successful run

The practical rule of thumb: Use IMPORTRANGE when you need a read-only live mirror of another sheet and the data volume is under a few thousand rows. Use Apps Script when you need two-way sync, write-back capability, transformation logic, a sync history log, or when IMPORTRANGE performance is causing issues.

Syncing Specific Columns Only

Sometimes you only need a subset of columns from the source — for example, you want branch staff to see product names and prices but not internal cost prices or supplier contacts.

Modify the read step to extract only the columns you need by index:

function syncSelectedColumns() {
  var SOURCE_SHEET_ID      = 'YOUR_SOURCE_SPREADSHEET_ID';
  var DESTINATION_SHEET_ID = 'YOUR_DESTINATION_SPREADSHEET_ID';
  var TAB_NAME             = 'Products';

  // Column indices (0-based) to include in the sync
  // e.g., columns A (0), B (1), and D (3) — skip C which has internal cost data
  var COLUMNS_TO_SYNC = [0, 1, 3];

  var sourceSheet      = SpreadsheetApp.openById(SOURCE_SHEET_ID).getSheetByName(TAB_NAME);
  var destinationSheet = SpreadsheetApp.openById(DESTINATION_SHEET_ID).getSheetByName(TAB_NAME);

  var sourceData = sourceSheet.getDataRange().getValues();

  // Extract only the specified columns from each row
  var filteredData = sourceData.map(function(row) {
    return COLUMNS_TO_SYNC.map(function(colIndex) {
      return row[colIndex];
    });
  });

  destinationSheet.clearContents();
  destinationSheet.getRange(1, 1, filteredData.length, COLUMNS_TO_SYNC.length)
    .setValues(filteredData);

  Logger.log(
    'Column-filtered sync complete. ' +
    filteredData.length + ' rows, ' +
    COLUMNS_TO_SYNC.length + ' columns.'
  );
}

Logging Sync History

For operations that run automatically overnight, you want a record of what happened without having to open the Apps Script execution log each time. A sync history tab gives you visibility into every run — when it ran, how many rows were transferred, and whether any errors occurred.

function logSyncHistory(rowsSynced, errors) {
  var LOG_SHEET_NAME = 'Sync Log';
  var spreadsheet    = SpreadsheetApp.getActiveSpreadsheet();
  var logSheet       = spreadsheet.getSheetByName(LOG_SHEET_NAME);

  if (!logSheet) {
    logSheet = spreadsheet.insertSheet(LOG_SHEET_NAME);
    logSheet.appendRow(['Timestamp', 'Rows Synced', 'Errors', 'Status']);
    logSheet.getRange(1, 1, 1, 4).setFontWeight('bold');
  }

  var status = errors.length > 0 ? 'Completed with errors' : 'Success';

  logSheet.appendRow([
    new Date(),
    rowsSynced,
    errors.length > 0 ? errors.join('; ') : 'None',
    status
  ]);

  // Keep only the last 90 log entries to avoid unbounded growth
  var maxRows   = 90;
  var totalRows = logSheet.getLastRow();
  if (totalRows > maxRows + 1) { // +1 for header
    logSheet.deleteRows(2, totalRows - maxRows - 1);
  }
}

Call logSyncHistory(rowsSynced, errors) at the end of your sync function, passing the count of synced rows and any error strings you have collected during the run. The log sheet will show the last 90 entries in reverse chronological order.

Practical Examples for Australian Businesses

HQ-to-Branch Product Catalogue Sync

A retail business with a head office in Melbourne and four branches across Victoria, New South Wales, and Queensland maintains a master product catalogue in a central Google Sheet. Branch managers need current pricing and stock availability each morning, but should not be able to edit the master.

Setup: The master sheet has tabs for each category. A one-way sync script runs nightly at 3am AEST, reading from each category tab and writing to the corresponding tabs in each branch's workbook. The COLUMNS_TO_SYNC approach is used to exclude the "Cost Price" and "Supplier Code" columns that are commercially sensitive.

Branch managers arrive each morning to a workbook that reflects that day's pricing without any manual intervention. If pricing changes mid-day, branch managers see the update the following morning.

Apps Script project: Bound to the master spreadsheet with a daily time trigger. The script iterates over an array of [branch_name, destination_spreadsheet_id] pairs, running the sync for each branch in sequence.

Master Inventory Across Multiple Warehouse Locations

A wholesale distributor operates three warehouse locations — Brisbane, Sydney, and Adelaide — each maintaining their own inventory spreadsheet. The operations manager needs a consolidated master view that reflects what is at each location, and warehouse staff need to be able to update their local counts.

Setup: A two-way sync script runs every four hours. Each warehouse sheet has a Location column (pre-filled and locked so staff cannot change it) and a Last Updated timestamp column that fills automatically on edit via an onEdit trigger. The sync script pulls all three warehouse sheets into the master using buildRowMap() keyed on SKU, and resolves conflicts by last-modified timestamp. Rows that appear in one location but not others are added to the master with the originating location code.

The operations manager's master view is refreshed every four hours. Warehouse staff continue using their own familiar spreadsheet without needing to see or interact with the master.

Affiliate & Partner Programs

If you are building Apps Script workflows at scale, or advising clients on their Google Workspace setup, the following programs may be of interest:

• Google Workspace Referral Program: CloudGeeks is a Google Workspace partner. If your business is not yet on Google Workspace, or is looking to upgrade, you can sign up through our referral link: https://referworkspace.app.goo.gl/. Apps Script, including all the sync capabilities covered in this guide, is included at no additional cost on every Workspace plan from Business Starter upward.
• Google Cloud Partner Program: https://cloud.google.com/partners — for technology partners and resellers building on Google Cloud infrastructure alongside Apps Script automations.
• Sheetgo Affiliate Program: Sheetgo is a no-code alternative for teams that want spreadsheet-to-spreadsheet connections without writing JavaScript. Their affiliate program is available at https://sheetgo.com. Sheetgo plans start at approximately AUD $14/month for the Solo tier (annual billing).
• Zapier Partner Program: https://zapier.com/platform/partner-program — for agencies and consultants building automated data workflows for clients. Zapier complements Apps Script for event-driven sync scenarios where data needs to flow based on a trigger rather than a schedule.

Conclusion

Keeping two Google Sheets in sync is one of those problems that looks simple until you try to solve it manually at scale. A head office spreadsheet that eight branch managers are supposed to update from, or an inventory master that three warehouses all need to contribute to, quickly becomes an exercise in copy-paste frustration and version control confusion.

Apps Script solves this properly. SpreadsheetApp.openById() gives you programmatic access to any sheet you can view or edit. getValues() and setValues() move entire datasets in a single efficient call. Time-based triggers put the whole process on autopilot. And a sync log sheet means you always know exactly what ran, when, and whether it succeeded.

The choice between one-way and two-way sync comes down to who owns the data. If one spreadsheet is the single source of truth and the others are consumers, one-way sync is simpler and sufficient. If multiple teams need to both read and contribute, two-way sync with timestamp-based conflict resolution is the right architecture.

Start with the one-way script if you are new to cross-sheet scripting — it is easier to set up and easier to debug. Once you are confident the sync is reliable, extend it to two-way if your workflow demands it.

Either way, your team will stop copying data between spreadsheets by hand, and your stakeholders will stop asking why the branch numbers do not match the master.

Need help with your Google Workspace setup? Contact our team for a free consultation.