CODEWITHSUNDEEP
google-apps-script
Peter Saunders
Peter Saunders

Reputation: 23

get last non-blank row of column google sheets

I need to go to the last non-blank cell in a column. For a long while, I've been using this code quite happily:

ss.getRange('A500').activate();
ss.getCurrentCell().getNextDataCell(SpreadsheetApp.Direction.UP).activate();

but now it crashes saying: "We're sorry, a server error occurred. Please wait a bit and try again." Days later I still get the same message!

When I debug, it won't go past the 2nd line above, so I guess that's where the problem is.

I've searched for a solution, but can only find code that relies on no gaps in the column. My column has many blank cells above the last cell with data in it.

Please help.

Upvotes: 2

Views: 3379

Answers (2)

Rafael De Acha
Rafael De Acha

Reputation: 31

const data = ss.getRange('A:A').getValues();
let ar=data.map(x => x[0]); //turns 2D array to 1D array, so we can use indexOf
const lastRow=ar.indexOf('');

Upvotes: 3

TheMaster
TheMaster

Reputation: 50855

A simple loop should do the job:

var data = ss.getRange('A1:A500').getValues();
for(var i = data.length - 1; i>=0; --i){//bottom-up loop
  if(data[i][0] !== '') break;
}
console.log(i+1);//row number of the non-blank row in a column.

Upvotes: 1

Related Questions