CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
sifar
sifar

Reputation: 1148

True Last Row and Last Column containing data on a Sheet

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?

Upvotes: 1

Views: 1120

Answers (2)

sifar
sifar

Reputation: 1148

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!

Upvotes: 0

Wicket
Wicket

Reputation: 38131

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.

Upvotes: 1

Related Questions