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
1 Answer
Reset to default 4Instead 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 ofagencynames[i]