Google-sheets – Usage of GetValues

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