google sheets script - select multiple a1 notation range

Question

I'm opting for batch update of row colors using google apps script. However I cannot go for the usual range function, as the rows to be colored are not consecutive. So I thought, a1 notation would be helpful but unfortunately it looks like I can only pass one range of a1 notation and not multiple:

var a1Notations="A1:C1,A3:C3,A10,C10";
sheet.getRange(a1Notations).setBackground("red");

But I'm getting "Range not found" error.

Any ideas how can I make this work?

Thanks!

Answer 1

To multi-select a number of ranges and change the color:

var sheet = SpreadsheetApp.getActiveSheet();
var rangeList = sheet.getRangeList(['A1:C1','A3:C3','A10','C10']);
sheet.setActiveRangeList(rangeList).setBackground("red");

If you want to type a list in a dialog box:

function promptRangesList() {
  var ui = SpreadsheetApp.getUi();
  var response = ui.prompt('Comma-separated ranges list', 'E.g.: A1:C1,A3:C3,A10,C10 or C3,C7,C17,C56', ui.ButtonSet.OK_CANCEL);
  Logger.log(response.getSelectedButton());
  // Process the user's response.
  if (response.getSelectedButton() == ui.Button.OK) {
    if (response.getResponseText()!=='') {
      var list = response.getResponseText().split(',');
      Logger.log(list);
      var rangeslist = SpreadsheetApp.getActiveSheet().getRangeList(list).setBackground("red");
      Logger.log(rangeslist);
      rangeslist.activate(); 
    } else {
      Logger.log('getResponseText empty');
    }    
  } else if (response.getSelectedButton() == ui.Button.CANCEL) {
    Logger.log('CANCELED');
  } else {
    Logger.log('The user clicked the close button in the dialog\'s title bar.');
  }  
}

Reference:

Class RangeList - Selects the list of Range instances

Class Sheet - Sets the specified list of ranges as the active ranges in the active sheet.

Prompt dialogs - A prompt is a pre-built dialog box that opens inside a Google Docs, Sheets, or Forms editor.

Answer 2

Put the range notations into an array, then loop through the array:

function setMultiRanges() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getActiveSheet();

  var a1Notations=["A1:C1","A3:C3","A10","C10"];

  var i=0,
      arryLngth = a1Notations.length;

  for (i=0;i<arryLngth;i+=1) {
    //Logger.log(a1Notations[i]);
    //Logger.log(typeof a1Notations[i]);

    sh.getRange(a1Notations[i]).setBackground("red");
  };
};

Answer 3

Set each range independently

 sheet.getRange("A1:C1").setBackground("red");
 sheet.getRange("A3:C3").setBackground("red");
 sheet.getRange("A10:C10").setBackground("red");

Answer 4

According to the doc of Google Sheets' API V4 the way to select multiple ranges is to pass an array of ranges.

var ranges = ["'EVERYTHING'!A:A", "'EVERYTHING'!Z:Z"];

// I use Node.js, so my call to the API looks like this:
service.spreadsheets.values.batchGet({
    spreadsheetId: spreadsheetId,
    ranges: ranges
}, function(err, result) { ...

Then what you get is an array of data, [{first range}, {second range}, {etc...}]