Is there a way to optimize this script to run faster? I am getting the error "Exceeded maximum execution time"

I am using Google App Scripts to import data from Spreadsheet #1 to Spreadsheet #2. But I am getting the error "Exceeded maximum execution time". What am I doing wrong? Is there a better way to optimize the code?

Script Logic

• Create Array #1, for column B, on Spreadsheet #1, which contains Employee ID's
• Create Array #2, for column A, on Spreadsheet #2, which contains Employee ID's already imported.
• If Array #1 contains an ID not found in Array #2, append a new row on Spreadsheet #2 with the ID, email1, and email2 columns.

Spreadsheet #1 (Source)

• Contains duplicate data in rows.
• There are 50,000 rows currently with 100 new rows added daily.

Spreadsheet #2 (Destination)

• Contains non-duplicate rows.

Working Script (for data less than 5,000 rows).

// Source sheet settings
let sourceSheetId = '1qW5UDn0O*******************';
let sourceTab = 'Source';
let sourceColumn = 2;
let sourceEmail1Column = 20;
let sourceEmail2Column = 19;

// Destination sheet settings
let destinationTab = 'Destination';
let destinationColumn = 1;
let destinationEmail1Column = 2;
let destinationEmail2Column = 4;


function newEmployeeIds() {
  let ss = SpreadsheetApp.getActiveSpreadsheet();
  let destinationSheet = ss.getSheetByName(destinationTab);

  let sourceSS = SpreadsheetApp.openById(sourceSheetId);
  let sourceSheet = sourceSS.getSheetByName(sourceTab);

  let existingIdsArray = destinationSheet.getRange(2,destinationColumn,destinationSheet.getLastRow()-1,1).getValues();
  let existingIds = existingIdsArray.flat();

  let sourceIdArray = sourceSheet.getRange(2,sourceColumn,sourceSheet.getLastRow()-1,1).getValues();
  let sourceIds = [...new Set(sourceIdArray.flat())];

  let email1Array = sourceSheet.getRange(2,sourceEmail1Column,sourceSheet.getLastRow()-1,1).getValues();
  let email2Array = sourceSheet.getRange(2,sourceEmail1Column,sourceSheet.getLastRow()-1,1).getValues();

  for (i=0;i<sourceIds.length;i++){
    if (existingIds.indexOf(sourceIds[i]) == -1){
    let newRow = destinationSheet.getLastRow()+1;
    destinationSheet.getRange(newRow,destinationColumn).setValue(sourceIds[i]);
    let index = sourceIdArray.flat().indexOf(sourceIds[i]);
    destinationSheet.getRange(newRow,destinationEmail1Column).setValue(email1Array[index][0]);
    destinationSheet.getRange(newRow,destinationEmail2Column).setValue(email2Array[index][0]);
    }
  }

}

Solution 1:

I believe your goal is as follows.

• You want to reduce the process cost of your script.

In this case, how about the following modification? In this modification, at first, the values are retrieved from the source and destination sheets, and create an array for putting to the destination sheet, and put it to the sheet.

Modified script:

function newEmployeeIds2() {
  const srcSheetName = 'Source';
  const dstSheetName = 'Destination';
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const [srcSheet, dstSheet] = [srcSheetName, dstSheetName].map(s => ss.getSheetByName(s));

  // Retrieve values from source sheet.
  const srcValues = srcSheet.getDataRange().getValues();

  // Retrieve values from destination sheet and create an object for searching the ID.
  const dstLastRow = dstSheet.getLastRow();
  const dstObj = dstSheet.getRange("A1:A" + dstLastRow).getValues().reduce((o, [a]) => (o[a] = true, o), {});

  // Create an array for putting to the destination sheet.
  const values = srcValues.filter(r => !dstObj[r[1]]).map(r => [r[1], "", r[18], r[19]]);

  // Put the values to the destination sheet.
  dstSheet.getRange(dstLastRow + 1, 1, values.length, values[0].length).setValues(values);
}

References:

• map()
• filter()
• setValues(values)

Added:

From your following replying,

Unfortunately I am receiving the error "Cannot read property 'getDataRange' of null (const srcValues = srcSheet.getDataRange().getValues();)". I believe this is because your code assumes the source and destination sheet are in the same Spreadsheet. In reality, the source sheet is in a different spreadsheet. I tried changing const srcSheetName = 'Source'; to const srcSheetName = SpreadsheetApp.openById('1qW5UDn0O*******************').getSheetByName('Source'); but still an error when running the code. Do you know whats wrong?

From your replying, I modified the script as follows.

Modified script:

Before you run teh script, Please set the Spreadsheet ID1.

function newEmployeeIds2() {
  const srcSheetName = 'Source';
  const dstSheetName = 'Destination';
  const srcSheet = SpreadsheetApp.openById("SpreadsheetID1").getSheetByName(srcSheetName);

  const dstSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(dstSheetName);
  // or const dstSheet = SpreadsheetApp.openById("SpreadsheetID2).getSheetByName(dstSheetName);

  // Retrieve values from source sheet.
  const srcValues = srcSheet.getDataRange().getValues();

  // Retrieve values from destination sheet and create an object for searching the ID.
  const dstLastRow = dstSheet.getLastRow();
  const dstObj = dstLastRow == 0 ? {} : dstSheet.getRange("A1:A" + dstLastRow).getValues().reduce((o, [a]) => (o[a] = true, o), {});

  // Create an array for putting to the destination sheet.
  const values = srcValues.filter(r => !dstObj[r[1]]).map(r => [r[1], "", r[18], r[19]]);

  // Put the values to the destination sheet.
  dstSheet.getRange(dstLastRow + 1, 1, values.length, values[0].length).setValues(values);
}