I need to be able to create variable strings that will be used by getRangeList() to select non-contiguous rows in a Google Sheet. The string will be different every time it's run. It might be gathering 20 rows one time and 4 the next (always in 2-row sets, incidentally). When I record a macro to select sample rows 8 and 9 and then 14 and 15, it gives me this:
function testMe() {
var spreadsheet = SpreadsheetApp.getActive();
spreadsheet.getRangeList(['8:9', '14:15']).activate();
};
Running this does fine--selecting the four rows. I hoped that this meant I could simply use script to create a string like "'8:9', '14:15'" as a variable and embed it in the code, as in:
function testMe() {
var spreadsheet = SpreadsheetApp.getActive();
var myRange = "'8:9', '14:15'";
spreadsheet.getRangeList([myRange]).activate();
};
This gives me the error "Exception: Range not found."
I considered that the problem was in the special characters and tried this instead:
function testMe() {
var spreadsheet = SpreadsheetApp.getActive();
var myRange = "\'8:9\', \'14:15\'";
spreadsheet.getRangeList([myRange]).activate();
};
This gives me the same error: "Exception: Range not found."
I wondered if the problem was with the brackets inside the parentheses and tried this:
function testMe() {
var spreadsheet = SpreadsheetApp.getActive();
var myRange = "[\'8:9\', \'14:15\']";
spreadsheet.getRangeList(myRange).activate();
};
I got this error: "Exception: The parameters (String) don't match the method signature for SpreadsheetApp.Spreadsheet.getRangeList."
I got the same error when I tried it with the brackets embedded in the string with the first example--the one without the slashes for the special characters, so:
function testMe() {
var spreadsheet = SpreadsheetApp.getActive();
var myRange = "['8:9', '14:15']";
spreadsheet.getRangeList(myRange).activate();
};
How can I create variable strings that will work with getRangeList() to select non-contiguous pairs of rows?
CodePudding user response:
Try
function testMe() {
var spreadsheet = SpreadsheetApp.getActive();
var myRange = []
myRange.push("8:9")
myRange.push("14:15")
spreadsheet.getRangeList(myRange).activate();
};
myRange is an array