Google Scripts fill array of values and their locations from cells

Question

I'm using Google Scripts to manipulate the values in a spreadsheet. Here's what I've got:

var Habits = sheet.getRange("C5:D100");

var HABITS = [Habits.getCell(1, 2).getValue(),
          Habits.getCell(2, 2).getValue(),
          Habits.getCell(3, 2).getValue(),
          Habits.getCell(4, 2).getValue(),
          Habits.getCell(5, 2).getValue(),
          Habits.getCell(6, 2).getValue(),
          Habits.getCell(7, 2).getValue(),
          Habits.getCell(8, 2).getValue(),
          Habits.getCell(9, 2).getValue(),
          Habits.getCell(10, 2).getValue(),
          Habits.getCell(11, 2).getValue(),
          Habits.getCell(12, 2).getValue(),
          Habits.getCell(13, 2).getValue(),
          Habits.getCell(14, 2).getValue(),
          Habits.getCell(15, 2).getValue(),
          ];

So you can probably see what I'm trying to do. Trying to get all the values in a series of cells as an array. But the range of values might fluctuate and I don't want to have to make changes to the script. Here's one solution that I've tried but I can't get to work (But it leaves HABITS undefined):

var Habits = sheet.getRange("C5:D100");
var HABITS

for (var i = 0; i < Habits.getHeight; i++){

  if (Habits[i].getCell(i,1).isBlank()){
    break;
  }
  else{
    HABITS[i] = Habits.getCell(i,2).getValue();
  }
}

The second problem is, as I get these values, I need to write new values back in their place, so somehow I need to record the location of each in a similar array.

Here's something I tried to change the values (this will replace a "checked habit" and make it "unchecked" aka- replace an X in a spreadsheet with nothing), but it doesn't work with HABITS being undefined and therefore of no length:

for (var i = 0; i < HABITS.length; i++) {
  if (HABITS[i] == "X"){
    Favorloc.setValue(Favorloc.getValue() + 1);
    Habits[i].setValue();
  }
}

Any suggestions? (Also, if you need to see my full script or spreadsheet to get a better idea of what I'm doing, let me know)

score 3 · Accepted Answer · answered Aug 05 '14 at 03:01

always worth having a dig around in the documentation :)

var range = sheet.getDataRange(); // REFERENCE THE ENTIRE AREA CONTAINING DATA ON THIS SHEET
var values = range.getValues(); // RETURN THE DATA FROM THE RANGE INTO AN ARRAY

then you can do the same in reverse, making sure your array and the area that you want to assign it to are exactly the same shape - I use the 'offset' method of the Range class to do that.

Google Scripts fill array of values and their locations from cells

1 Answers1