Why does this for loop not work in Google scripts

Question

i wrote the following loop based on the code found here:

How do I iterate through table rows and cells in javascript?

function myRowLooper() {
var inputSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('INPUT');
var inputRange = inputSheet.getRange(2,inputSheet.getLastColumn(),inputSheet.getLastRow());

    for (var i = 0, row; row = inputRange.rows[i]; i++) {
       Logger.log(row);
         for (var j = 0, col; col = row.cells[j]; j++) {
          Logger.log(col);
  }  
 }
}

but when I apply it to Google scripts it throws an error: "TypeError: Cannot read property "0" from undefined."

What's causing this?

what's the output of Looger.log for row 0 ? – Ahmed Masud Aug 11 '16 at 16:56 — Ahmed Masud, Aug 11 '16 at 16:56

score 0 · Answer 1 · answered Aug 11 '16 at 17:15

Because you can't get any value from 'inputRange.rows[i]'.

You may do something like this :

function myRowLooper() {
  var inputSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');  
  for(var i = 1; i<=inputSheet.getLastRow(); i++){
    for(var j = 1; j<=inputSheet.getLastColumn(); j++){
      var cell = inputSheet.getRange(i,j).getValue();
      Logger.log(cell);
    }
  }
}

Hope this will help you.

Thanks.

score 0 · Answer 2 · answered Aug 11 '16 at 19:04

Your code is extremely sloppy. You are trying to combine variables and condense unnecessarily and it's leading to errors in your code. There's no use in added "efficiency" if it leads to errors and mistakes.

Try something like this --

function yourFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Name");
  var lastRow = sheet.getLastRow();
  var lastColumn = sheet.getMaxColumns();
  var conditionToCheckColumn = (lastColumn - 1);
  var conditionRange = sheet.getRange(2, checkColumn, (lastRow - 1), 1);
  var check = checkRange.getValues();
  for (var i = 0; i < check.length; i++) {
    if (check[i] == condition) {
      continue;
    } else {
      //your code here
    }
  }
}

This will pull a range at which you can check it's value/ condition and if matches, it does nothing. If it does not match, it will perform code. It will then loop to the next row until the last row in your check range that has data.

Be warned - functions count as data despite the cell being visibly empty. If your sheet uses functions like =QUERY, you will have an infinitely looping code unless your =QUERY (or other fx()) has a specific upper limit.

Thanks for your response. Having read the documentation I can see the value of your approach. However, when I run the code it says that 'checkColumn', 'checkRange' and 'condition' are not defined. — Takeshi Patterson, Aug 18 '16 at 10:41
Yes, you will need to specify exactly where and how you want to define your ranges and your conditions. The code provided is intentionally vague and will not work right out the gate for you. Please review the code and modify to fit your actual needs. — MasterCrander, Aug 18 '16 at 18:15

Why does this for loop not work in Google scripts

2 Answers2