SpreadsheetApp.getLastRows() provides empty records and not truncate to those with data-CodePudding

I'm new to JS and use of Google App Script, so pardon my lack of JS knowledge.

I got a simple .gs script to work. It converts content in one cell to a hyperlink (e.g. if text is "blah", it would be "https://example.com/blah").

The script retrieves the number of rows to operate on. For one sheet, this does it correctly and truncates it to the last entry (row=31) even though the sheet has 1000 rows. However, for another sheet, it does not and returns 999:

Here's the debug for when it wasn't right:

Apr 8, 2022, 11:20:53 AM    Debug   doiColumn: 10   numRows: 999

Here it is for another sheet that worked as intended:

Apr 8, 2022, 9:44:47 AM Debug   doiColumn: 5   numRows: 31

The docs say: "Returns the position of the last row that has content."

For illustration, here is a snippet of the code:

let spreadsheet = SpreadsheetApp.getActive();
let sheet = SpreadsheetApp.getActiveSheet();
const column = getColumn(sheet, column_name);
let range = sheet.getRange(2, column, sheet.getLastRow() - 1, 1)
console.log(range.getValues());
const numRows = range.getNumRows();
console.log(`column: ${column}   numRows: ${numRows}`)

I would like to make sure that .getLawRow() is truncated. Otherwise this script runs longer than it should.

CodePudding user response：

Sorry I could not fit it into a comment

It requires col = column number, sh = sheet (not the name) ,ss = Spreadsheet (not the name)

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;
  //const h = Utilities.formatString('col: %s len: %s', col, rcA.length - s);
  //Logger.log(h);
  //SpreadsheetApp.getUi().showModelessDialog(HtmlService.createHtmlOutput(h).setWidth(150).setHeight(100), 'Col Length')
}

There are other ways to do it but I prefer this one. It's been reliable for me

Using ranges like this sheet.getRange("A1:Z") will also provide you will a bunch of nulls from getLastRow() to getMaxRows() which then need to be filter out but if you do have a null within your data then that will also get removed and now you have bogus data because that row being removed now messes up your row order.

CodePudding user response：

Try to replace .getLastRow() by

.getLastDataRow(column)

and add this prototype function

Object.prototype.getLastDataRow = function(col){
  var lastRow = this.getLastRow();
  if (col == null){col=1}
  var range = this.getRange(lastRow,col);
  if (range.getValue() !== "") {
    return lastRow;
  } else {
    return range.getNextDataCell(SpreadsheetApp.Direction.UP).getRow();
  }  
};