Updating Destination Sheet by Source Sheet in Google Spreadsheet using Google Apps Script


This is a sample script for updating the destination sheet by the source sheet in Google Spreadsheet using Google Apps Script.

The sample situation is as follows.

Sample script

function myFunction() {
  const sheetNames = ["Sheet1", "Sheet2"];

  // Retrieve values from source and destination sheets.
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const [srcSheet, dstSheet] = sheetNames.map((s) => ss.getSheetByName(s));
  const [srcValues, dstValues] = [srcSheet, dstSheet].map((s) =>

  // Create an array for updating the destination sheet.
  const srcObj = srcValues.reduce((o, r) => ((o[r[0]] = r), o), {});
  const values = [
    ...dstValues.map(([a, ...v]) => {
      if (srcObj[a]) {
        const temp = srcObj[a];
        delete srcObj[a];
        return temp;
      return [a, ...v];

  // Update the destination sheet.
  dstSheet.getRange(1, 1, values.length, values[0].length).setValues(values);
  • In this sample script, “Sheet1” and “Sheet2” are the source and destination sheets, respectively.

  • When this script is run, the top image is obtained.