exceeded maximum execution time in google-apps-script

Question

I have two spreadsheets. I want to match column C of spreadsheet1 with column A of spreadsheet2. Both of these spreadsheet has records more than 8000. Due to huge number of records my script constantly gives exceeded maximum execution time error. Here is the script

function compare() {

var ss=SpreadsheetApp.getActiveSpreadsheet();
  var sheetList1=ss.getSheetByName("spreadsheet1");
  var sheetList2=ss.getSheetByName("spreadsheet2");

   var sheet1Data=sheetList1.getRange(2,3,sheetList1.getLastRow(),1).getValues();

   var sheet2Data=sheetList2.getRange(1,1,sheetList2.getLastRow(),1).getValues();

   for (i in sheet2Data){
    var row = 2;
      for (j in sheet1Data){
        if (sheet1Data[j][0]==sheet2Data[i][0]){
           sheetList1.getRange("A"+row).setValue('Inactive');           
        }
        row++;
      }


   }

}

any suggestions for optimizing this script. Or how to handle this error ? Thanks in advance :)

EDIT

Thanks for the wonderful reply. There is one issue. If I push data in newSheet1Data array before the if statement then it write Inactive twice. i.e if there are two rows it writes inactive to four rows.Like

newSheet1Data.push(sheet1Data[j]); 
        if (sheet1Data[j][2]==sheet2Data[i][0]){
          newSheet1Data[j][0]='Inactive';
        }

If I push data inside if statement and no match occur then it does not find row and give this error TypeError: Cannot set property "0.0" of undefined to "Inactive". Like

if (sheet1Data[j][0]==sheet2Data[i][0]){
           newSheet1Data.push(sheet1Data[j]);
           newSheet1Data[j][0]='Inactive';           
        }

Serge insas Serge insas · Accepted Answer · 2013-06-20T11:25:46

You should avoid any calls to spreadsheet API in a loop, especially when you have so much data in it.

The idea is to play only with arrays and to write the result back once it is finished. The code below does it (based on you code if data in col C sheet1 is the same as data in sheet2 col A then write a string in sheet1 col A ). I hope I made no mistake but I didn't have the opportunity to test my code... it might need some debugging ;-)

function compare() {
  var ss=SpreadsheetApp.getActiveSpreadsheet();
  var sheetList1=ss.getSheetByName("Sheet1");
  var sheetList2=ss.getSheetByName("Sheet2");
  var sheet1Data=sheetList1.getDataRange().getValues();// get the whole sheet in an array
  var sheet2Data=sheetList2.getRange(1,1,sheetList2.getLastRow(),1).getValues();
  var newSheet1Data = [] ; // create a new array to collect data
   for (i=0;i<sheet1Data.length;++i){
      for (j=0;j<sheet2Data.length;++j){
      if(i!=j){continue};
        newSheet1Data.push(sheet1Data[i]); // add the full row to target array
        if (sheet1Data[i][2]==sheet2Data[j][0]){
          newSheet1Data[i][0]='Inactive';//if condition is true change column A
          break
        }
      }
   }
  newSheet1Data.shift();// remove first row (probably headers ?)
  sheetList1.getRange(2,1,newSheet1Data.length,newSheet1Data[0].length).setValues(newSheet1Data);  // write back to sheet1 in one batch
}

EDIT : after seing your doc I understand what you want more exactly... here is the new code (tested on your SS)

function compare() {
  var ss=SpreadsheetApp.getActiveSpreadsheet();
  var sheetList1=ss.getSheetByName("Sheet1");
  var sheetList2=ss.getSheetByName("Sheet2");
  var sheet1Data=sheetList1.getDataRange().getValues();// get the whole sheet in an array
  var sheet2Data=sheetList2.getRange(1,1,sheetList2.getLastRow(),1).getValues();
  var newSheet1Data = [] ; // create a new array to collect data
  for (i=0;i<sheet1Data.length;++i){
    newSheet1Data.push(sheet1Data[i]); // add the full row to target array
       for (j=0;j<sheet2Data.length;++j){
         if (sheet1Data[i][2]==sheet2Data[j][0]){
          newSheet1Data[i][0]='Inactive';//if condition is true change column A
          break;// don't continue after condition was true, this will speed up the process
        }
      }
   }
  newSheet1Data.shift();// remove first row (probably headers ?)
  sheetList1.getRange(2,1,newSheet1Data.length,newSheet1Data[0].length).setValues(newSheet1Data);  // write back to sheet1 in one batch
}

exceeded maximum execution time in google-apps-script

1 Answers