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 · Accepted Answer · 2013-06-20 22:23:20Z

2

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
}

edited Jun 20, 2013 at 22:23

answered Jun 20, 2013 at 11:25

Serge insas

46.9k7 gold badges113 silver badges135 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Serge insas Over a year ago

sorry I had made a typo in code, last line od code : must of course use the sheetName to use .getRange(), I updated my answer.

Serge insas Over a year ago

I finally tested it, this version works as expected, the loops were not suitable for this logic.

Serge insas Over a year ago

Are you sure ? Data is pushed only once... if you get 2 copies of a row I really don't know why. Could you share a copy of your spreadsheet to see what happens or at least a sample of data to est on.? My test runs fine. Btw the error you get after changing the place of the push is normal, the row you try to write to doesn't exist at this time. It has to come first.

Serge insas Over a year ago

Also : I added a line in the code to speed it up (a bit more !) see the break; + comment

Noor Over a year ago

Its taking still 5 minutes time. if data become little more I guess the script will stop working again.

|

Collectives™ on Stack Overflow

exceeded maximum execution time in google-apps-script

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related