-1

I'm writing an ArrayFormula so I can fill many rows in my sheet with one formula. This formula needs to use an Apps Script that I wrote, which does some calculations based on the row number.

But whenever I use sheet.getActiveCell() or sheet.getCurrentCell() in the script, I get back the cell where the formula is written (i.e. at the top of my sheet), not the cell corresponding to the row currently being filled in by the ArrayFormula.

How can my Apps Script know which row is currently being filled in by the ArrayFormula?

Mike Miller
  • 3,368
  • 5
  • 36
  • 49
  • 3
    Please post a sample code of what you're trying to achieve – David Salomon Nov 24 '21 at 22:21
  • The active cell is not determined by where a script or a formula is performing some action it's either where you last placed it or in the case of openById it's always in A1 to start with. – Cooper Nov 24 '21 at 22:29
  • 1
    Working with scripts and Array Formula's can be quite a nuisance since it often makes it impossible to use getLastRow() in any meaningful way for use by most scripts. – Cooper Nov 24 '21 at 22:33
  • @Cooper My answer [here](https://stackoverflow.com/questions/46883862/arrayformula-is-breaking-the-getlastrow-funtion-possible-workarounds/46884012) may be helpful. – TheMaster Nov 24 '21 at 23:57
  • Is this a custom function? Provide sample code and sample implementation logic. – TheMaster Nov 24 '21 at 23:58

1 Answers1

0

I sometimes used this function in replace of getLastRow() since the last row in the sheet is almost always determined by the array formula.

function getColumnHeight(col, sh, ss) {
  var ss = ss || SpreadsheetApp.getActive();
  var sh = sh || ss.getActiveSheet();
  var col = col || sh.getActiveCell().getColumn();
  var rcA = [];
  if (sh.getLastRow()){ rcA = sh.getRange(1, col, sh.getLastRow(), 1).getValues().flat().reverse(); }
  let s = 0;
  for (let i = 0; i < rcA.length; i++) {
    if (rcA[i].toString().length == 0) {
      s++;
    } else {
      break;
    }
  }
  return rcA.length - s;
}
Cooper
  • 59,616
  • 6
  • 23
  • 54
  • You can define the `s` this way: `let s = rcA.findIndex(x => x.toString() != '')`. No need the loop, etc. – Yuri Khristich Nov 25 '21 at 00:19
  • @YuriKhristich thanks for that suggestion I wrote that a long time ago and haven't reviewed for a while. – Cooper Nov 25 '21 at 01:10
  • Actually after reviewing it carefully I decided that it's better this way because it will return zero if the column is completely empty. Were as with your suggested modification it returns getLastRow()+1. Ir can probably be fixed but I'm just going to leave it as it is. – Cooper Nov 25 '21 at 02:52