Google Apps Script: get values of range, edit and set values to range

Question

I want to build a menu function to edit the selected values in a google sheet:

function fDoSomethingWithRange(){
  //var theSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();

  var theSheet = SpreadsheetApp.getActiveSheet();
  var theRange = theSheet.getActiveRange();
  var theValues = theRange.getValues();
  var theArray = new Array(theRange.getHeight());
  //theSpreadsheet.toast(theValues);

  for (var i = 0;i<theRange.getHeight();i++){
    theArray[i]=new Array(theRange.getWidth());
    };

  for (var h=0;h<theRange.getHeight();h++){  
    for (var w=0;w<theRange.getWidth();w++){

      var i = h + (w * theRange.getHeight());
      theArray[h][w] = parseInt(theValues[i]) * 2; // just an example return double values
    };
  };
  theRange.setValues(theArray);
};

I get stuck on returning the array; the second column is empty and I just don't see why. Anybody?

thanks, Arvid

Arvid Arvid · Accepted Answer · 2015-03-03T11:25:57

Ok, found it myself... finally :-)

getValues() gets a two-dimensional array. I was fooled by the toast message which displays just a row of values, suggesting getValues() gets a one-dimensional array.

function fDoSomethingWithRange(){
  //var theSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();

  var theSheet = SpreadsheetApp.getActiveSheet();
  var theRange = theSheet.getActiveRange();
  var theValues = theRange.getValues();
  var theArray = new Array(theRange.getHeight());
  //theSpreadsheet.toast(theValues);

  for (var i = 0;i<theRange.getHeight();i++){
    theArray[i]=new Array(theRange.getWidth());
    };

  for (var h=0;h<theRange.getHeight();h++){  
    for (var w=0;w<theRange.getWidth();w++){

      var i = h + (w * theRange.getHeight());
      theArray[h][w] = parseInt(theValues[h][w]) * 2; // just an example return double values
    };
  };
  theRange.setValues(theArray);
};

Google Apps Script: get values of range, edit and set values to range

1 Answers