Iterated variable (i) for argument on range in Google Scripts API

Question

  for (i=0; i<numItems; i++) {
    var cell = sheet.getRange(12,i);
    cell.setFormula(i);
  }

This simple formula should go to every place on the column 12 where there is data, and replace it with the number which i is, correct? The argument it is giving me is The coordinates or dimensions of the range are invalid.

How on earth am I supposed to use the i for the argument in range?

Answer 1

There are some inaccuracies in your code. I will try to guide you through the code.

1. Your code alone will not work, but you probably know that
   • first declare the active sheet (or spreadsheet)
     var sh = SpreadsheetApp.getActiveSheet();
   • determine the last row in the sheet
     var row = sh.getLastRow();
   • get the range and get the values
     var aData = sh.getRange(1, 2, row, 1).getValues();
2. The getRange(row, col) will range a particular cell and not a range. After having set the getRange(), you need to get the content by adding the getValues(). The setFormula() is specifically meant for adding a formula, like "=MAX(A:A)".
   Here's the ittaration, how I would perform it:
   • first, you need to hold the "new" data in an array
     var aNew = new Array();
   • set up the itteration
     for (i=0; i<aData.length; i++) {
   • set up an IF statement
     if(aData[i][0] != "") {
   • add "new" data into array via the push method
     aNew.push([i+1]);
3. Finally add the newly gathered data to the active sheet. This is done via the setValues() command.
   • sh.getRange(1, 1, aNew.length, 1).setValues(aNew);

The complete code and example file: itteration code

function myFunction() {
  var sh = SpreadsheetApp.getActiveSheet();
  var row = sh.getLastRow();
  var aData = sh.getRange(1, 2, row, 1).getValues(); 

  var aNew = new Array();  
  for (i=0; i<aData.length; i++) {
    if(aData[i][0] != "") {
      aNew.push([i+1]);
    }
  }
  sh.getRange(1, 1, aNew.length, 1).setValues(aNew);
}

Answer 2

I was getting the same. The problem is that there is no row 0...

You should use i+1:

var cell = sheet.getRange(12,i+1);