最新消息:雨落星辰是一个专注网站SEO优化、网站SEO诊断、搜索引擎研究、网络营销推广、网站策划运营及站长类的自媒体原创博客

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

programmeradmin1浏览0评论

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
Share Improve this question edited Apr 27, 2016 at 11:05 Douglas Gaskell 10.1k12 gold badges80 silver badges135 bronze badges asked Apr 27, 2016 at 10:35 Andre KirchhoffAndre Kirchhoff 11 gold badge1 silver badge3 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

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]
发布评论

评论列表(0)

  1. 暂无评论