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.
A B C D E F G H I J K L M N O P Q R S T U V W
200001 [email protected] [email protected]
200001 [email protected] [email protected]

Spreadsheet #2 (Destination)

  • Contains non-duplicate rows.
A B C D
200001 [email protected] [email protected]

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]);
    }
  }

}