CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
Cyril Duchon-Doris
Cyril Duchon-Doris

Reputation: 14029

Google spreadsheet Script - iterate on rows of active selection, but all columns

I have read many similar questions but I still can't get my code to work and I believe this is because of a slight difference in what I want to achieve :

I want to perform iterations on the rows that correspond to the currently selected cells (I don't care about the cells themselves, just about their row). I also need to reach cells that are outside the current selection, but in the same row.

Eg suppose I select A2:A3 in my spreadsheet, then I want to "recognize" I'm on rows 2 and 3, and perform operations like

E2 = A2 + B2 + C2 * D2
E3 = A3 + B3 + C3 * D3

So far I was trying something like this but it doesn't seem to work

function myfunction(){
  var range = sheet.getActiveRange()
  var firstRow = range.getRow()
  var numRows = range.getNumRows();

  for (var i = 0; i < numRows; i++) {
    var absoluteRow = firstRow + i

NOTE : actually I believe the script itself is good, but the way I activate it is maybe the problem :

I generate a new menu item "MyFunction" in the top toolbar, and I attached my function to this menu item, but is it passing the activeRange correctly ?

SpreadsheetApp.getUi()
  .createMenu("MyMenu")
  .addItem("MyFunction","myFunction")
  .addToUi()

Upvotes: 0

Views: 1408

Answers (1)

Eugene
Eugene

Reputation: 1895

This code works properly for me 

  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var range = sheet.getActiveRange()
  var firstRow = range.getRow()
  var numRows = range.getNumRows();
  Logger.log("firstRow "+firstRow);
  Logger.log("numRows "+numRows);

  for (var i = 0; i < numRows; i++) {
    var absoluteRow = firstRow + i;
    Logger.log("absoluteRow "+absoluteRow); 
  }

The results: 

[16-09-01 13:44:44:762 EEST] firstRow 2
[16-09-01 13:44:44:763 EEST] numRows 5
[16-09-01 13:44:44:764 EEST] absoluteRow 2
[16-09-01 13:44:44:764 EEST] absoluteRow 3
[16-09-01 13:44:44:765 EEST] absoluteRow 4
[16-09-01 13:44:44:765 EEST] absoluteRow 5
[16-09-01 13:44:44:766 EEST] absoluteRow 6

Please see if you receive the proper results when you launch it from the script editor.

Upvotes: 1

Related Questions