How to get use get range to get all data except certain rows in Sheets App

[Amietee Fuondjing]

Anyone got a bright idea on how to get a range from particular a cell until where there is no more data? I saw getDataRange but that would return the names of my columns as well. And with getRange I would have to specify the last row but in my scenario my spreadsheet dynamically grows so that would mean constantly changing the code. I want to be able to capture all the data without getting that first row of column labels. Thanks.

[CBMServices Web]

It is easiest to just get all and ignore the header row in your software. But if you must just get the data excluding the header row do the following:

var sheetdata = sheet.getRange(2,1,sheet.getLastRow(), sheet.getLastColumn()).getValues();

This would pull all data starting at row 2, column 1 down to last row and last column that have data filled in.

On Mon., Jul. 11, 2022, 12:06 p.m. Amietee Fuondjing, <amietee....@siriusxm.com> wrote:

Anyone got a bright idea on how to get a range from particular a cell until where there is no more data? I saw getDataRange but that would return the names of my columns as well. And with getRange I would have to specify the last row but in my scenario my spreadsheet dynamically grows so that would mean constantly changing the code. I want to be able to capture all the data without getting that first row of column labels. Thanks.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/5ab51af7-8d0b-422f-bd5c-54f3748d6f9bn%40googlegroups.com.

[Amietee Fuondjing]

Thank you sir!

[Edward Ulle]

It should be var sheetdata = sheet.getRange(2,1,sheet.getLastRow()-1,sheet.getLastColumn()).getValues();

[Robert McLendon]

Why does getLastRow() need to be reduced by one, but not getLastColumn()?

[Stuart Smith]

Because the range is being started on row 2 rather than 1, to skip 1  header row.  If you didn't subtract 1 from getLastRow(), yo'ud end up fetching an extra unused row at the bottom.  By contrast, the range starts on column 1, skipping nothing, so you can just use getLastColumn().

Hope that helps!

[game over]

https://fabform.io/a/how-to-find-google-sheet-id