Google Apps Script getRanges returns a list with a single Range

Question

I am trying to get the range of selected cells in Google Apps Scripts and chain them into an HtmlOutput.

One of the columns in my spreadsheet has HTML input. For example: <p>Hello <em>World</em></p>. The idea is to simply give a nice preview of the HTML.

My use case is that the user selects a range of cells (multiple rows, same column) by dragging their cursor across, and then previews content for the selected rows.

I am trying to do this with this code:

function previewModal() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var ranges =  sheet.getActiveRangeList().getRanges();
  var html = HtmlService.createHtmlOutput('');
  for (var i = 0; i < ranges.length; i++) {
    var contentCell = ranges[i];        
    var htmlContent = contentCell.getValue();
    html.append('<div>').append(htmlContent).append('</div>').append('<hr>');
  }
  html.setWidth(1000).setHeight(1000);
  SpreadsheetApp.getUi().showModalDialog(html, 'Preview');
}

However, when I run this, I see that ranges.length is equal to 1, regardless of how many cells I have selected. And the cell that ranges contains is the "active" cell -> this is the one which you first clicked, before dragging across to cover more cells. As a result, my HTML output just contains the value from that 1 cell and not the range.

What I mean by "Active Cell" and "Active Range":

What I know so far:

sheet.getActiveRangeList() works as intended and returns the entire "Active Range" of cells. I know this since I am able to call a .clear on this and see the selected range of cells clear out!
sheet.getActiveRangeList().getRanges() fails -> this is supposed to return an array of Range objects: Range[], which it does, but the array only has the "Active Cell"

tehhowch tehhowch · Accepted Answer · 2018-09-07T18:59:10

A contiguous selection is a single Range. Use Range#getValues instead, to access all the values of a given Range. Range#getValue() will always return only the top-left value, even if there is more than 1 cell in the Range.

function previewModal() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet(),
        ranges =  sheet.getActiveRangeList().getRanges(),
        htmlPrefix = "",
        html = ranges.reduce(function (rgSummary, rg) {
          var summary = rg
              .getValues() // 2D array
              .reduce(arrayToHtmlReduceCallback_, "");
          return rgSummary + summary;
        }, htmlPrefix);
  const output = HtmlService.createHtmlOutput(html).setWidth(1000).setHeight(1000);
  SpreadsheetApp.getUi().showModalDialog(output, 'Preview');
}

function arrayToHtmlReduceCallback_(acc, row, i, allArrayRows) {
  const rowSummary = row[0]; // Using only the content of the first column.
  return acc + '<div>' + rowSummary + '</div><hr>';
}

Additional Reading:

Google Apps Script getRanges returns a list with a single Range

What I know so far:

1 Answers