I'm pretty new to this, so I'm not even sure if this is the most efficient way to do this, but I'm trying to save some space in my Google Apps Script by using a cell value to define a Range List.
Here's the code that I have right now:
function myFunction()
{
var ss = SpreadsheetApp.getActiveSpreadsheet();
var s = ss.getActiveSheet();
var aItems = s.getRange('F1').getValue();
var aItemList = s.getRangeList([aItems.getValue]);
Logger.log(aItemList);
}
The variable aItems is getting the the value of cell F1 on my sheet, which is 'E2', 'E5' (I also tried removing the single quotes surrounding the cell numbers, but that didn't change the result)
With aItemList I am attempting to create a Range List using the value of aItems to define the range. I get an exception error when I run the script stating that the range is not found.
Ultimately the purpose of the aItemList variable will be to have a variable that is storing a list of cells containing checkboxes that I can alternate between being True and False. On the actual sheet that I plan on using this for, there will be hundreds of checkboxes, so I want to avoid having to list them all out in the script as part of the array. I mention this because I have tried variations of this code that have successfully logged aItemList as the correct string, but do not allow me to set the cells value to true or false using aItemList as a reference.
If someone could let me know if this is even possible or not, I would greatly appreciate it. And/or if there is an even better method of accomplishing this task of storing specific cells into a variable as an array that would also be highly appreciated.
Try this:
function myFunction() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var s = ss.getActiveSheet();
var aItems = s.getRange('F1').getValue().split(',');
Logger.log(aItems);
let rgl = ss.getRangeList(aItems);
Logger.log(rgl.getRanges().map(r => r.getA1Notation()));
}