Simple Google script query on Spreadsheet takes too long due to unwanted calls

Question

I need to get data from the active cell of a Google Spreadsheet. The script to request the data so far is quite simple:

function getSelectedRow(){

  Logger.log("Looking up active row...")
  var cell = SpreadsheetApp.getActiveSheet().getActiveCell();
  var data = SpreadsheetApp.getActiveSheet().getSheetValues(cell.getRow(), 1, 1, 20);
  Logger.log(data)
}

However, running the script takes a long time. Although the execution transcript shows:

[14-10-17 11:25:56:480 ICT] Execution succeeded [0.668 seconds total runtime]

It paradoxically shows a start time 36 second before:

[14-10-17 11:25:19:145 ICT] Starting execution

In fact, the first log occurs at a time matching with the claimed execution time:

[14-10-17 11:25:55:811 ICT] Logger.log([Looking up active row..., []]) [0 seconds]

Yet between the start time and this first log, the following occurs:

[14-10-17 11:25:19:175 ICT] SpreadsheetApp.getActiveSheet() [0 seconds]
[14-10-17 11:25:19:326 ICT] Sheet.getDataRange() [0.15 seconds]
[14-10-17 11:25:22:210 ICT] Range.getValues() [2.882 seconds]
[14-10-17 11:25:22:241 ICT] SpreadsheetApp.getActiveSheet() [0 seconds]
[14-10-17 11:25:22:371 ICT] Sheet.getDataRange() [0.13 seconds]
[14-10-17 11:25:25:407 ICT] Range.getValues() [3.034 seconds]
[14-10-17 11:25:25:442 ICT] SpreadsheetApp.getActiveSheet() [0 seconds]
[14-10-17 11:25:25:612 ICT] Sheet.getDataRange() [0.169 seconds]
[14-10-17 11:25:28:369 ICT] Range.getValues() [2.756 seconds]
[14-10-17 11:25:28:408 ICT] SpreadsheetApp.getActiveSheet() [0 seconds]
[14-10-17 11:25:28:541 ICT] Sheet.getDataRange() [0.133 seconds]
[14-10-17 11:25:31:407 ICT] Range.getValues() [2.865 seconds]
[14-10-17 11:25:31:439 ICT] SpreadsheetApp.getActiveSheet() [0 seconds]
[14-10-17 11:25:31:571 ICT] Sheet.getDataRange() [0.131 seconds]
[14-10-17 11:25:34:313 ICT] Range.getValues() [2.741 seconds]
[14-10-17 11:25:34:346 ICT] SpreadsheetApp.getActiveSheet() [0 seconds]
[14-10-17 11:25:34:500 ICT] Sheet.getDataRange() [0.153 seconds]
[14-10-17 11:25:37:357 ICT] Range.getValues() [2.856 seconds]
[14-10-17 11:25:37:387 ICT] SpreadsheetApp.getActiveSheet() [0 seconds]
[14-10-17 11:25:37:526 ICT] Sheet.getDataRange() [0.138 seconds]
[14-10-17 11:25:40:293 ICT] Range.getValues() [2.766 seconds]
[14-10-17 11:25:40:325 ICT] SpreadsheetApp.getActiveSheet() [0 seconds]
[14-10-17 11:25:40:478 ICT] Sheet.getDataRange() [0.152 seconds]
[14-10-17 11:25:43:509 ICT] Range.getValues() [3.031 seconds]
[14-10-17 11:25:43:543 ICT] SpreadsheetApp.getActiveSheet() [0 seconds]
[14-10-17 11:25:43:690 ICT] Sheet.getDataRange() [0.146 seconds]
[14-10-17 11:25:46:523 ICT] Range.getValues() [2.832 seconds]
[14-10-17 11:25:46:558 ICT] SpreadsheetApp.getActiveSheet() [0 seconds]
[14-10-17 11:25:46:689 ICT] Sheet.getDataRange() [0.13 seconds]
[14-10-17 11:25:49:363 ICT] Range.getValues() [2.673 seconds]
[14-10-17 11:25:49:404 ICT] SpreadsheetApp.getActiveSheet() [0 seconds]
[14-10-17 11:25:49:538 ICT] Sheet.getDataRange() [0.133 seconds]
[14-10-17 11:25:52:571 ICT] Range.getValues() [3.033 seconds]
[14-10-17 11:25:52:606 ICT] SpreadsheetApp.getActiveSheet() [0 seconds]
[14-10-17 11:25:52:756 ICT] Sheet.getDataRange() [0.149 seconds]
[14-10-17 11:25:55:774 ICT] Range.getValues() [3.016 seconds]

So basically, the script effectively takes a long time to run because it keeps making calls to the spreadsheet. I don't know what's causing those calls to be made, there is no call I made other than the one in question in the function.

Can you help me understand and solve this? Thanks.

score 1 · Accepted Answer · answered Oct 17 '14 at 07:22

I'm pretty sure you have some spreadsheet calls that are outside of the function you show, some sort of "global variable" that uses Sheet.getDataRange()and Range.getValues() somewhere...

these "outside" calls run when any function is called and that's what you see in the execution transcript.

Clean up your code or wrap these calls into a function and you'll get an execution transcript as below :

[14-10-17 09:16:12:921 CEST] Starting execution
[14-10-17 09:16:12:941 CEST] Logger.log([Looking up active row..., []]) [0 seconds]
[14-10-17 09:16:12:941 CEST] SpreadsheetApp.getActiveSheet() [0 seconds]
[14-10-17 09:16:12:942 CEST] Sheet.getActiveCell() [0 seconds]
[14-10-17 09:16:12:943 CEST] SpreadsheetApp.getActiveSheet() [0 seconds]
[14-10-17 09:16:12:943 CEST] Range.getRow() [0 seconds]
[14-10-17 09:16:13:048 CEST] Sheet.getSheetValues([1, 1, 1, 20]) [0.104 seconds]
[14-10-17 09:16:13:049 CEST] Logger.log([[[0.0, xxx0, some values here, some values there, another one, , , , , , , , , , , , , , , ]], []]) [0 seconds]
[14-10-17 09:16:13:050 CEST] Execution succeeded [0.108 seconds total runtime]

Ah yes, correct! I'm guilty as charged: http://stackoverflow.com/questions/26352289/global-variable-defined-in-function-appears-not-defined I was too stubborn made a call each time I looked up a header index, instead of looking up the header row once and extracting the header indexes. Now we're down to about 4 seconds. Thanks. — neydroydrec, Oct 17 '14 at 07:54

score 0 · Answer 2 · answered Oct 17 '14 at 05:45

0

See if this modified code runs faster

function getSelectedRow(){

  Logger.log("Looking up active row...")
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getRange(sheet.getActiveCell().getRow(), 1, 1, 20).getValues();
  Logger.log(data)
}

answered Oct 17 '14 at 05:45

azawaza

3,065
1
17
20

Nope. The same problem occurs again. Thanks for trying. – neydroydrec Oct 17 '14 at 06:27
How is this function called? By user from add-on/menu or by script from a trigger/another function? – azawaza Oct 17 '14 at 06:29

Simple Google script query on Spreadsheet takes too long due to unwanted calls

2 Answers2