Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.
  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?

share|improve this question

2 Answers 2

up vote 3 down vote accepted

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);
}
share|improve this answer

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);
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.