Simple Google Script taking too much time,

Question

I was running a Google script for few months without any problem. Suddenly the script is taking too much time to execute. I tried to run the script for different spreadsheets and noticed that for most its working fine but for few its taking too much time.

The following code read values of A1 cell of all the sheets in two spreadsheets "SS 1" and "SS 2".

      function Test()
      {
        Logger.log("SS 1 Start")
        var ss = SpreadsheetApp.openById("ID SS 1") //Spreadsheet 1

         var sheets = ss.getSheets()
        Logger.log(sheets.length)
        for(var i = 0; i<sheets.length; i++)
        {
         Logger.log(sheets[i].getRange("A1").getValue())
        }
        Logger.log("SS 1 Stop")

        Logger.log("SS 2 Start")
        var ss = SpreadsheetApp.openById("ID SS 2") //Spreadsheet 2
        var sheets = ss.getSheets()

        Logger.log(sheets.length)
        for(var i = 0; i<sheets.length; i++)
        {
           Logger.log(sheets[i].getRange("A1").getValue())
        }
        Logger.log("SS 2 Stop")
       }

SS 1 has 15 sheets and the script takes almost a minute to read values from A1 cell, while it takes only 10 seconds on SS 2 which has 21 sheets. What could be the problem? The logs are attached

  [17-10-27 19:40:56:116 IST] SS 1 Start
  [17-10-27 19:40:56:279 IST] 15.0
  [17-10-27 19:40:59:894 IST] PS
  [17-10-27 19:41:03:324 IST] WL
  [17-10-27 19:41:06:748 IST] PERIOD
  [17-10-27 19:41:10:218 IST] 
  [17-10-27 19:41:13:812 IST] 
  [17-10-27 19:41:16:987 IST] 
  [17-10-27 19:41:20:568 IST] 
  [17-10-27 19:41:24:136 IST] 
  [17-10-27 19:41:27:803 IST] 
  [17-10-27 19:41:31:214 IST] 
  [17-10-27 19:41:34:740 IST] 
  [17-10-27 19:41:38:538 IST] 
  [17-10-27 19:41:41:987 IST] 
  [17-10-27 19:41:45:693 IST] 
  [17-10-27 19:41:49:313 IST] 
  [17-10-27 19:41:49:314 IST] SS 1 Stop
  [17-10-27 19:41:49:314 IST] SS 2 Start
  [17-10-27 19:41:49:421 IST] 21.0
  [17-10-27 19:41:50:034 IST] 
  [17-10-27 19:41:50:422 IST] BCHEM
  [17-10-27 19:41:50:831 IST] BIO
  [17-10-27 19:41:51:210 IST] BMS
  [17-10-27 19:41:51:606 IST] BOT
  [17-10-27 19:41:52:017 IST] CHEM
  [17-10-27 19:41:52:578 IST] CS
  [17-10-27 19:41:52:995 IST] ELEC
  [17-10-27 19:41:53:401 IST] FT
  [17-10-27 19:41:53:803 IST] INS
  [17-10-27 19:41:54:277 IST] MB
  [17-10-27 19:41:54:825 IST] PHY
  [17-10-27 19:41:55:217 IST] PS
  [17-10-27 19:41:55:621 IST] ZOO
  [17-10-27 19:41:56:031 IST] PHY-ED
  [17-10-27 19:41:56:433 IST] HC
  [17-10-27 19:41:56:978 IST] MATHS
  [17-10-27 19:41:57:374 IST] 
  [17-10-27 19:41:57:769 IST] 
  [17-10-27 19:41:58:588 IST] PERIOD
  [17-10-27 19:41:59:031 IST] 
  [17-10-27 19:41:59:031 IST] SS 2 Stop

Partha S. Pal

Ed Nelson Ed Nelson · Accepted Answer · 2017-10-27T18:58:26

I set up some test spreadsheets and ran your script. It finished in a little over 7 seconds with a 41% to 51% split in times for the 2 which is the same as the 15/22 number of sheets which is what I expected. I suggest you create new spreadsheets and try it again.

Simple Google Script taking too much time,

1 Answers