-1

I'm currently using google app scripts. I have 3 lists that are in columns, when they are edited I need to check the amount in that column. There are three people assigned per column, if one column drops to 1 while the others have 3, then I will redistribute the people among the list.

So, can I 'getLastRow' on a given column? The range class has the getLastRow available, however, the range function itself doesn't seem to return a cell that has a value, for instance

Logger.log(sheet.getRange("B:B").getLastRow());.

returns '999.0' - while the last row with content is row 6.

Any ideas? Thanks.

Jay Jenkins
  • 413
  • 1
  • 5
  • 15

1 Answers1

1

There's no other way except to getValues() and check each value.

var lastRow = -1;
sheet.getRange("B:B")
          .getValues()
          .some(function(e){
                lastRow++;
                return !(e[0])
            });
Logger.log(lastRow);

If you're also going to use all those values in your script, You can use map

var flattenedValues = sheet.getRange("B:B").getValues().map(function(e){
    return e[0]; //flatten Array
});
var lastRow = flattenedValues.indexOf('');
Logger.log(lastRow);

Also using

sheet.getRange("B1:B"&sheet.getLastRow());

will help avoiding bunch of empty values.

All the above functions assume that there aren't any blanks. If present,Loop in reverse to find the lastRow.

References:

Community
  • 1
  • 1
TheMaster
  • 45,448
  • 6
  • 62
  • 85
  • Thanks for your reply, that's a pity though, it can provide the global last row but not the column last row... why have the function in the range class at all? How, then, will adding 'getLastRow' in your last suggestion help avoid empty values? – Jay Jenkins Aug 05 '18 at 20:44
  • Someone provided an excellent solution. Thanks for your answer. – Jay Jenkins Aug 05 '18 at 20:52
  • @jamie It'll help avoiding some,but not all//That's great too. – TheMaster Aug 05 '18 at 21:03