Google Sheets – Usage of GetValues in Google Apps Script

google sheetsgoogle-apps-script

When searching for values in a table I am currently running a check on specific cells with the following code:

for (var i = 11; i < (lastrow+1); i++) {
    var equip = sheet.getRange(i, 33).getValue();
    var equip_name = sheet.getRange(i, 34).getValue();
    var mech_date = sheet.getRange(i, 39).getValue();
    var elec_date = sheet.getRange(i, 42).getValue();

    if (mech_date && elec_date == '') {
      output_mech_only.push([equip, equip_name, mech_date]);
    } else {
      if (mech_date == '' && elec_date) {
        output_elec_only.push([equip, equip_name, elec_date]);
      } else {
        if (mech_date && elec_date) {
          output_mech_elec.push([equip, equip_name, mech_date, elec_date]);
        }
      }
    }
  }

As it stands the code above works just fine, and serves its purpose. However in the interest of simplifying things I have been looking into how to use .getValues() instead to streamline the code. I have looked through the documentation and am unclear as to how I would reference the needed columns.

Is anyone able to clarify the usage of .getValues() in this particular instance?

Best Answer

This is how it could look like with getValues:

Each call to getRange(...).getValues() gets a double array of values in the rectangular block determined by the arguments of getRange: top row, leftmost column, number of rows, number of columns.

When accessing individual values, use two indices [i][j] keeping in mind that they begin with 0s. That is, array[0][0] will be the value in the upper left corner of the range.

Since two of the columns were consecutive, they are grabbed together into equip_array; the other two are grabbed separately. It would also be reasonable to get all of the columns at once, in an array spanning 10 columns from 33th to 42nd. But I think the code is easier to maintain (in case of spreadsheet structure changes) if non-consecutive columns are dealt with separately.

var equip_array = sheet.getRange(11, 33, lastRow - 10, 2).getValues();
var mech_date_array = sheet.getRange(11, 39, lastRow - 10, 1).getValues();
var elec_date_array = sheet.getRange(11, 42, lastRow - 10, 1).getValues();

for (i = 0; i < equip_array.length; i++) {
    var equip = equip_array[i][0];
    var equip_name = equip_array[i][1];
    var mech_date = mech_date_array[i][0];
    var elec_date = elec_date_array[i][0];

    // the rest as in your code

Related Solutions

Google-sheets – Chart usage per interval from periodic readings

To find the approximate usage, say, in June 2015, I would look at the last reading before this month began and at the first reading after it ended; then pro-rate the use accordingly. This guarantees the usage estimate will be based on an interval that's at least a month long.

I used the Match command to determine the relevant rows for each month, then Indirect to access the data. (Another possibility is Vlookup, but it's a bit too rigid for the present problem.) In the sample below:

column C contains the first day of each month (which can be generated with Date), with custom formatting that shows only the month and year.
column D has formula =MATCH(C2-1,A:A), etc. This is the last entry in A column preceding the month in column C.
column E is =TO_TEXT(C2), etc: this conversion to text is needed so that the chart comes out with month-based labels, and not with some other dates chosen by software.
column F contains the logic of computation: for example, F2 is

=(INDIRECT("B"&D3+1)-INDIRECT("B"&D2))/(INDIRECT("A"&D3+1)-INDIRECT("A"&D2))

meaning that one should take the first reading after the month ended, and subtract the last reading from before it began; then divide by the corresponding dates.

+-----------+--------+-----------+-------+------------+-------+
|   Date    | Meter  |   Month   | Match | Text Month | Usage |
+-----------+--------+-----------+-------+------------+-------+
| 4/25/2015 | 830    | May 15    |     2 | May 15     | 0.33  |
| 5/25/2015 | 844.79 | June 15   |     3 | June 15    | 0.20  |
| 7/1/2015  | 852.2  | July 15   |     3 | July 15    | 0.50  |
| 7/21/2015 | 856.26 | August 15 |     5 | August 15  |       |
| 8/3/2015  | 880    |           |       |            |       |
+-----------+--------+-----------+-------+------------+-------+

The above is also available as a shared spreadsheet.

Google Sheets – How to Resolve Running Time Exceeded Issues

You may be able to improve the performance by replacing individual row insertions in a loop by a batch operation post-loop (as Google themselves recommend):

var output = []; 
while (files.hasNext() ) {
 file = files.next();
 name = file.getName();
 urllink = file.getUrl();
 currentfolder = file.getParents();
 lastupdated = file.getLastUpdated();
 output.push([name, folder, urllink, lastupdated]);
}
if (output.length) {
  var lastRow = sheet.getRange.getLastRow();
  sheet.getRange(lastRow + 1, 1, output.length, 4).setValues(output);
}

Apart from that, you could try to split the task into smaller one: for example, "process the folders where the first character of the name is in the range A-E". Then set separate triggers for such functions, for different times. However, the algorithm gets a bit complicated since one should go through the subfolders under a folder that itself isn't a part of the currently processed range.

As a last resort, there's a way stop/resume the execution of a script, see this answer. You can try storing folder Id in script properties and resuming with that folder. But this also gets complicated because of the nested structure: there are file/folder iterators that you can't simply store and renew.