Getting the last non-empty cell in a column-CodePudding

I have a script that is adding or substracting a value (picked up in M2 cell) in each cell of a selected range (I mean a range that I can select with the mouse) :

function moreLess() {
 var ss = SpreadsheetApp.getActive();
 var sel = ss.getSelection().getActiveRangeList().getRanges();
 for (var i=0; i<sel.length; i  ) {
 var range = sel[i];
 var values = range.getValues();
 var number = SpreadsheetApp.getActive().getSheetByName("sommaire_redac").getRange('M2').getValue();
 for (var j=0; j<values.length; j  ) {
   for (var k=0; k<values[0].length; k  ) {
     values[j][k]  = number;
   }
 }
 range.setValues(values);
}
}

This works. But instead of selecting all the cells in which I want add or substract a value, I would like to select only one cell, and have a script that would select a range from this selected cell to the last non-empty cell of the column.

For example, instead of selecting cells T30:T36 like this…

with the code I have today

… I would like to select only T30, like this…

with the code I'd like to have

… and then I would like the script to get the last non-empty cell of the column (T36) and select the range T30:T36.

I mean I would like to get exactly the same result by selecting only T30 cell, that I today obtain by selecting T30:T36.

Thanks !

CodePudding user response：

Using your own script, you could add a second line between var ss and var sel like this:

 var ss = SpreadsheetApp.getActive();
 ss.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate()
 var sel = ss.getSelection().getActiveRangeList().getRanges();

It will select the remaining rows of that column ;)

CodePudding user response：

This script helps you to find the last empty row of a column.

function lastEmpty(col) {
  const sss = SpreadsheetApp.getActiveSpreadsheet();
  const ss = sss.getActiveSheet();
  
  const getlastEmptyRow = (colValues) => {
    return colValues.length - colValues.reverse().findIndex(row => !!row[0]);
  }
  const colValues = ss.getRange(col ":" col).getValues();
  const lastEmptyRow = getlastEmptyRow(colValues);

  return lastEmptyRow;
}

console.log(`last empty row in column T is ${lastEmpty('T')}`)