How can I use a variable string with getRangeList() to select noncontinguous rows in a Sheet?

Issue

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?


Solution

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

Answered By – Mike Steelson

Answer Checked By – Jay B. (AngularFixing Admin)

Leave a Reply

Your email address will not be published.