How to select multiple, non-consecutive cells in Google Sheets using a list of the cell addresses

Question

I have a very long, comma-separated list of cell address coordinates, shortened here for the sake of brevity: C3,C7,C17,C56.

I would like to select these cells. Is this possible to do by pasting the values into a field somewhere?

If not, then via a script or add-on?

EDIT:
Considering my intention was to format the cells, this worked for me: Format (menu) > Conditional Formatting Apply conditional formatting rules It would still be useful to know how to select cells, so I'll leave the question here.

Answer 1

Update

On April 2018, several new classes and methods were added to the Google Apps Script Spreadsheet Service including Class RangeList and methods to get this class were added to Class Spreadsheet and Class Sheet. Ref. https://developers.google.cn/apps-script/releases/2018?hl=es-AR#april_11_2018

---

Short answer

At this time it's not possible to programmatically select multiple cells.

Remarks

The conditional formatting rule allowing to set several ranges separated by commas is an oddity of Google Sheets as other features like Named Ranges, Range Protection, doesn't allow this and Google Apps Script doesn't have a class for disjoint ranges. Anyway some, tasks could be done by looping through a list of references.

References

• Edit and format data in Google spreadsheets - Google Docs editors Help
• Issue 4069: Add support to allow use and manipulate disjoint Ranges

Answer 2

To select cells with a script:

var sheet = SpreadsheetApp.getActiveSheet();
var rangeList = sheet.getRangeList(['C3', 'C7', 'C17', 'C56']);
sheet.setActiveRangeList(rangeList);

The last range in the list is set as the active range.

To type in a dialog box:

function promptRangesList() {
  var ui = SpreadsheetApp.getUi();
  var response = ui.prompt('Comma-separated ranges list', 'E.g.: A1,B2:B4,C1 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);
      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.