Iterating over cells in Google Spreadsheet Custom Function

Question

I am trying to build a custom function in Google Spreadsheet, that would basically do this: - say custom function called func() is placed in cell D2 and invoked as =func(B2) - provided a particular cell reference (say B2) as a starting point it would iterate over all fields that follow B2 down the column (so B3, B4, B5) while the value of those fields equals to a particular symbol (say pipe |). - For each iteration where this condition succeeds (i.e B3 == '|') it could add up/aggregate values from the cell it was placed, down the column. So if cells B3, B4,B5 contain | and then B6 doesn't it would return value of D3+D4+D5.

So for example if in this spreadsheet:

enter image description here

In the cells B10 the function should produce value of 8 (1+3+4) and in the cell B15 the function should produce value of 11 (5+6).

I've came up with something like this:

function sumByPipe(startRange) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getRange(startRange)
  var sum = 0;
  for (var row_num = 1; row_num < 128;  row_num ++) {

    var cell = range.getCell(row_num, 1);
    var cellValue = cell.getValue();

    if (cellValue == '|') {
      sum += 1;
    }

  }
  return sum;
}

and got stuck in 2 places really:

Function seems to work in the debugger, but when I invoke it from the spreadsheet it fails on the getRange() function call saying no such range exist. If I replace it with static call of say getRange('A2') that part works but then it fails on the getCell() saying index out of range.
How do I actually get the value of the next cell down the column from where the function itself is placed?

Really quite lost on these two and would appreciate any advice. Thank you!

Alan Wells Alan Wells · Accepted Answer · 2015-07-04T00:45:12

This works. I tested it:

function sumByPipe(startRange) {

  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getRange(startRange)
  var sum = 0;

  var startColumn = range.getColumn();
  var startRow = range.getRow();

  for (var row_num = startRow; row_num < startRow+128;  row_num++) {

    var cellWithPipe = sheet.getRange(row_num, startColumn-1).getValue();
    var cellValue = sheet.getRange(row_num, startColumn).getValue();

    if (cellWithPipe === '|') {
      sum += cellValue;
    } else {
      //If pipe is no longer present, stop and return sum
      return sum;
    }
  }
}

Iterating over cells in Google Spreadsheet Custom Function

1 Answers