True Last Row and Last Column containing data on a Sheet

1
votes

I have a Sheet that gets self-refreshing data from an external source. This Sheet which may not have the same lastRow or lastColumn. I need to find a quickest way to determine what is the true lastRow and true lastColumn of the sheet that contains data. I need to exclude cells containing formulas or data validations. Also, as the data refreshes, the rows count and columns count change everytime and therefore sometimes the getLastRow() and getLastColumn() donot work correctly but include blanks.

I have seen some code which requires you to input a particular column or row number inorder to identify its last data row or data column. I want to know if there is a way to exclude such an input.

  function DoIt(){

    var sht = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    var Rng = sht.getDataRange().getValues();

    Rng.forEach(function (row,index) {
        if (row[0] == "") {                   // row[0] is the same as Column A. row[1] = B
            Rng.length = index;
        }
    });
    var newRange = sht.getRange(Rng.length,1);
    Logger.log(newRange.getA1Notation());
}

For example, in the below image, the lastRow should be row 11 as there is a value in cell D11 and lastColumn should be column F as there is value in cell F3. So [row: 11, col: 6] should be the immediate answer.

data range

Is there an efficient and quicker way to find the true lastrow and lastcolumn of a sheet?

2
google-apps-scriptgoogle-sheets
If you were to get the lastRow() and the lastColumn() for that sheet then row 11 and column 6 is what you would get. So what's the problem? – Cooper
@Cooper as new data gets pulled from an external source to the sheet, the earlier rows may get removed which results in the getLastRow() or getLastColumn() to report the wrong row number or column number. Or there may be blanks or data validations left behind. Its difficult to pinpoint why it doesnt work sometimes, but yes, i have encountered this situation with refreshing data. It seems sort of an earlier UsedRange (like in Excel) still persists in the Sheet. So incorrect lastrow or lastcolumn gets reported. – sifar
If the data is changing and you rerun sheet.getLastRow() does it return the correct result or is it because you not executing the function again after the change. – Cooper
have you tried using flush() before checking with getLastRow()? – Oleg Valter
I would need an minimal reproducible example to tackle this problem. Just the minimal example to reproduce the problem. – Cooper

2 Answers

1
votes

It's very likely that the problem occurs because your aren't comparing the last row / column at the same point in time.

As you are using var Rng = sht.getDataRange().getValues(); the last row is Rng.length and the last column is Rng[0].length. They should be the same as sht.getLastRow() and sht.getLastColumn().

If you are applying changes to a spreadsheet you should wait for the spreadsheet update process finish and the updates spread across Google data centers before reading again the last row / column.

You might consider to add a timestamp somewhere (maybe a property, maybe a cell) for the last change made by your time-driven trigger to be sure which spreadsheet "revision" are you reading.

0
votes

I made these one-line functions for finding the true lastColumn or true lastRow on a sheet which does not take into account specifying any particular row or column and gives me the correct result.

function FindLastRow(sht) {
  return sht.getRange(1,1).getDataRegion().getLastRow();
};

function FindLastColumn(sht) {
  return sht.getRange(1,1).getDataRegion().getLastColumn();
};
    
function test(){
  var sht = SpreadsheetApp.getActiveSpreadsheet().getActivesheet();
  lr = FindLastRow(sht);
  lc = FindLastColumn(sht);
}

Not sure of speeds! If someone can test and confirm, it would be worth the try!