Efficient Techniques for Choosing Multiple Cells in Google Sheets

The ability to set multiple ranges separated by commas in conditional formatting is unique to Google Sheets. Other features like Named Ranges and Range Protection do not offer this functionality, and Google Apps Script does not have a class for disjoint ranges. For more information, you can refer to the “Edit and format data in Google spreadsheets” section in the Google Docs editors Help. Additionally, this issue has been documented under Issue 4069.


Solution 1:

Update

In April 2018, the
Google Apps Script
Spreadsheet Service saw the addition of multiple new classes and methods. These included the introduction of Class RangeList and the addition of methods within Class Spreadsheet and Class Sheet to retrieve this class. For more information, refer to https://developers.google.cn/apps-script/releases/2018?hl=es-AR#april_11_2018.


Short answer

Currently, there is no capability to programmatically choose multiple cells.

Remarks

The ability to set multiple ranges separated by commas in the conditional formatting rule is a unique feature of Google Sheets. Unlike other features such as Named Ranges and Range Protection, this functionality is not available. Additionally, Google Apps Script does not include a class for disjoint ranges. However, certain tasks can still be accomplished by iterating through a list of references.

References

  • Assist with editing and formatting data in Google spreadsheets – Google Docs editors Help
  • Incorporate functionality to enable the utilization and manipulation of non-overlapping Ranges.


Solution 2:


To choose cells using a script:

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

The active range is determined by setting the last range in the list.

To input information within a dialogue 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:

“Rephrased MSDTHOT”:
The RangeList class is responsible for choosing a list of Range instances.

The Class Sheet function designates the provided list of ranges as the active ranges within the active sheet.

Prompt dialogs refer to pre-
built dialog box
that open within the editors of Google Docs, Sheets, or Forms.

Frequently Asked Questions