2

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.

  • Considering my intention was to format the cells, this worked for me: Format (menu) > Conditional Formatting support.google.com/docs/answer/78413 It would still be useful to know how to select cells, so I'll leave the question here. – simbasounds Mar 20 '16 at 13:22
0

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

3

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.