I am new to programming and I have such a simple question but I struggle to find the answer. I would like to dynamically overwrite cells from A1 on until the lenght of the array. This is the second for loop I am struggling with. The bination of ("A" + ii) for the range doesnt look "professional" :-) Thanks for your help.

function selectmyagency() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var agencynames = ss.getRange("B8:B42").getValues();
  var myagency = ss.getRange("C3").getValue();

  var arrayLength = agencynames.length;

  for (var i = 0; i < arrayLength; i++) {
    if(agencynames[i] == myagency){
      //doNothing
    } else {
      agencynames[i] = ".";
    }//endif 
  }//endfor

  //overwrite Cell in Spreadsheet
  for (var ii = 0; ii < agencynames.length; ii++) {
    SpreadsheetApp.getActiveSheet().getRange("A"+ii).setValue(agencynames[ii]);
    //SpreadsheetApp.getActiveSheet().getRange("A9").setValue(agencynames[ii]);
    //SpreadsheetApp.getActiveSheet().getRange("A10").setValue(agencynames[ii]);
  }
}//endfunction

I am new to programming and I have such a simple question but I struggle to find the answer. I would like to dynamically overwrite cells from A1 on until the lenght of the array. This is the second for loop I am struggling with. The bination of ("A" + ii) for the range doesnt look "professional" :-) Thanks for your help.

function selectmyagency() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var agencynames = ss.getRange("B8:B42").getValues();
  var myagency = ss.getRange("C3").getValue();

  var arrayLength = agencynames.length;

  for (var i = 0; i < arrayLength; i++) {
    if(agencynames[i] == myagency){
      //doNothing
    } else {
      agencynames[i] = ".";
    }//endif 
  }//endfor

  //overwrite Cell in Spreadsheet
  for (var ii = 0; ii < agencynames.length; ii++) {
    SpreadsheetApp.getActiveSheet().getRange("A"+ii).setValue(agencynames[ii]);
    //SpreadsheetApp.getActiveSheet().getRange("A9").setValue(agencynames[ii]);
    //SpreadsheetApp.getActiveSheet().getRange("A10").setValue(agencynames[ii]);
  }
}//endfunction

• javascript
• google-apps-script
• google-sheets

• Hey Andre, is the problem you are having that this is not working, or that it just doesn't look right? If you just want to blank out the entire agencyNames range, I can provide an answer for that that more efficient. – Douglas Gaskell Commented Apr 27, 2016 at 10:49
• to make the question on-topic, show us whats not working. – Zig Mandel Commented Apr 27, 2016 at 13:40

1 Answer 1

Instead of looping through the array and setting the ranges value one cell at a time, you can do this in a batch operation like so: ss.getRange("B8:B42").setValues(agencynames);

Do this after modifying the agencynames array, this will set all the values of that range to match your array as long as the array and the range are the same size. It's generally discouraged to make calls to a service in a loop when you can use a batch operation, for performances and readabilities sake.

For more information, refer to the Apps Script Best Practices

Edit: Your modified code:

function selectmyagency() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var agencynames = ss.getRange("B8:B42").getValues();
  var myagency = ss.getRange("C3").getValue();

  var arrayLength = agencynames.length;

  for (var i = 0; i < arrayLength; i++) {
    if(agencynames[i] == myagency){
      //doNothing
    } else {
      agencynames[i] = ".";
    }//endif 
  }//endfor

  //overwrite Cell in Spreadsheet
  ss.getRange("B8:B42").setValues(agencynames);
}//endfunction

A couple more pointers:

• There is no need to set an array length variable if you are only modifying the elements themselves and not the array.
• When you use getValues() you are getting an array of arrays not an array of values, even if it is only a single column. ie. [["value"],["value"],["value"]] instead of ["value","value","value"]. When setting or getting the values of this array, you probably want to useagencynames[i][0] instead of agencynames[i]