Google SheetsJavascript -> Loop through array and set the values to the cells - Stack Overflow

admin管理员组

文章数量:1325761

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

Share Improve this question Follow edited Apr 27, 2016 at 11:05 Douglas Gaskell 10.1k1212 gold badges8080 silver badges135135 bronze badges asked Apr 27, 2016 at 10:35 Andre KirchhoffAndre Kirchhoff 111 gold badge11 silver badge33 bronze badges 2

• 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

Add a ment  | 

1 Answer 1

Sorted by: Reset to default 4

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]

本文标签： Google SheetsJavascript gt Loop through array and set the values to the cellsStack Overflow