hookedonwinter hookedonwinter - 1 year ago 154
Javascript Question

Google Script Editor getRange takes 10 seconds on 1 column, ~0 on another

I'm building a budgeting spreadsheet that aggregates data from 10+ sheets into one master sheet. One of the calls is

sheet.getRange(1, 1, max_rows);
(or something close to that). That call takes approximately 10 seconds on the first column, but closer to 0 seconds on other columns. Each of those calls is on the same size sheet (about 75 rows).

I run this call on each sheet, so while 10 seconds is ok once, 100+ seconds is a long runtime!

Here is a snippet of the code. I've tried both
set 1
set 2
, to the same result.
are set earlier in the script.

var city_sheet = ss.getSheetByName(cities[i]);
var city_rows = city_sheet.getMaxRows();

// set one
var cat_range = city_sheet.getRange(1,1,city_rows).getValues(); // this one takes 10 seconds
var subcat_range = city_sheet.getRange(1,2,city_rows).getValues(); // ~0 sec
var value_range = city_sheet.getRange(1,4,city_rows).getValues(); // ~0 sec

// set two
var city_data = city_sheet.getRange(1, 1, city_rows, 4); // this one takes about 10 seconds

I then loop through the data in the range. Any thoughts as to how to speed this up?

Here's a gist of the whole script: https://gist.github.com/anonymous/b6a8985c9ea5e553182e


Answer Source

I would change the line:

var city_rows = city_sheet.getMaxRows();


var city_rows = city_sheet.getLastRow();

getLastRow() will find the last row of data. getMaxRows() can return lots of rows with nothing in them.

For example. If you have lots of formulas copied down in a column.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download