Google Apps Script working with selection

Question

I would like to add a menu item to a Google Sheet that performs a task on the selected cells. A web search, including Stack Overflow, indicates that the getActiveRange() method is the proper approach. But when I query the returned Range object, regardless of what cells I have selected, it always reports the first row equal to 1 and the number of rows equal to 1. What should I be using?

Here is a stripped-down script that reproduces the issue:

var ss    = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];

function onOpen() {
  var myMenu = [];
  myMenu.push({name: "Detect selection", functionName: "detectSelection"});
  ss.addMenu("My Menu", myMenu);
}

function detectSelection() {
  var range    = sheet.getActiveRange();
  var firstRow = range.getRow();
  var lastRow  = range.getLastRow();
  var numRows  = range.getNumRows();
  sheet.getRange(1,1).setValue("First Row");
  sheet.getRange(1,2).setValue(firstRow);
  sheet.getRange(2,1).setValue("Last Row");
  sheet.getRange(2,2).setValue(lastRow);
  sheet.getRange(3,1).setValue("Number of Rows");
  sheet.getRange(3,2).setValue(numRows);
}

I can select any group of cells I wish, choose "Detect selection" from "My Menu", and the first row, last row, and number of rows will always be reported as 1.

range.getRow() and range.getColumn() is relative to the upper left corner. — Cooper

Cooper Cooper · Accepted Answer · 2019-11-29T23:06:39

Try this:

Compare it to what you have and I think you can figure it out for yourself. The learning experience will be worth it. You can select any range you want.

function runOne() {
  var ss=SpreadsheetApp.getActiveSpreadsheet();
  var sh=ss.getSheetByName('Sheet1');
  var rg=sh.getActiveRange();
  sh.clearContents();
  var vA=[['rg.getRow()',rg.getRow()],['rg.getColumn()',rg.getColumn()],['rg.getLastRow()',rg.getLastRow()],['rg.getLastColumn()',rg.getLastColumn()],['rg.getNumRows()',rg.getNumRows()],['rg.getNumColumns()',rg.getNumColumns()]];
  var vB=[];
  for(var i=0;i<rg.getHeight();i++) {
    vB[i]=[];
    for(var j=0;j<rg.getWidth();j++) {
      vB[i][j]=Utilities.formatString('%s,%s',rg.getRow()+i,rg.getColumn()+j);
    }
  }
  sh.getRange(rg.getRow(),rg.getColumn(),vB.length,vB[0].length).setValues(vB);
  sh.getRange(sh.getLastRow()+1,rg.getColumn(),vA.length,vA[0].length).setValues(vA);
}

Google Apps Script working with selection

1 Answers