CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
andio
andio

Reputation: 1778

Unreasonably slow apps script when accessing data from a google sheet cell

I have this simple script :

function counter() {
  var time1 = new Date();
  function debugme(text){
    var time2= new Date();
    var timeval = time2.getTime() - time1.getTime();
    Logger.log(text +" : "+timeval+" ms")
    time1 = new Date();
  }

  var Sheet = SpreadsheetApp.getActiveSheet();
  debugme("get sheet");
  var firstrow = Sheet.getRange('B3').getValue();
  debugme("get cell data");
}

output :

get sheet : 6 ms
get cell data : 27142 ms

I only try to get data from a cell but the get cell data : 27142 ms is just very slow. I have lot of formula in my sheet but that B3 has no formula (static value). Does anyone have any clue what cause this problem ?

EDIT :

Here's the link : https://docs.google.com/spreadsheets/d/1KmJKGZscvzUbS7y7trsaE1vcbpYsSB0yQ_A63yEdeA0/edit?usp=sharing

Just press the BIG RED BUTTON to execute script.

Upvotes: 2

Views: 2285

Answers (2)

ziganotschka
ziganotschka

Reputation: 26796

Your sheet is very bulky, just loading it took in my case more than one minute.

I deleted in a copy of your spreadsheet all the empty rows below row 98 and this alone reduced the execution time for me from 20468 ms to 542 ms.

I can see erroneous formulas in column AD, and there can be other things slowing your script down - like other formulas, data validation, protected ranges etc.

I recommend you to either delete all redundant cells in your existing spreadsheet or - even better - copy all still relevant data (contents only) into a new spreadsheet.

Your code per se should not take much more than 500 ms to execute in a reasonably simple spreadsheet.

Upvotes: 1

Rilves
Rilves

Reputation: 156

As others stated, this might be a problem specific to your sheet. However, accessing the data of a sheet is often slower than doing a sheet.getDataRange().getValues() and then accessing your values from the Array you get from it. It's especially useful if you need to access values at several points in your code, or if you want to loop through a sheet.

Upvotes: 0

Related Questions