CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsselection
Bill Spotz
Bill Spotz

Reputation: 33

Google Apps Script working with selection

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.

Upvotes: 1

Views: 361

Answers (1)

Cooper
Cooper

Reputation: 64032

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);
}

Upvotes: 1

Related Questions