Google Sheets is a powerful tool that can help you manage and analyze data. One common operation you might need to perform is finding the last row in a dataset. This might seem like a simple task, but it can be tricky if your data has blank rows or columns. In this blog post, we’ll explore how to accurately find the last row in Google Sheets using Google Apps Script.
Using Google Apps Script
Google Apps Script is a JavaScript cloud scripting language that provides easy ways to automate tasks across Google products and third party services. It’s built into Google Sheets and can be used to perform more complex operations.
In the context of this article, we’ll use Google Apps Script to find the last row in a sheet. Here’s a simple function that does that:
function getLastRow() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var lastRow = sheet.getLastRow();
return lastRow;
}
The above script starts by getting the active spreadsheet and the active sheet. The SpreadsheetApp.getActiveSpreadsheet().getActiveSheet() line returns the currently active sheet in the active spreadsheet (the spreadsheet the user currently has open).
Next, it calls the getLastRow() method on the sheet object to get the last row that has content. Keep in mind that this only returns the last row with content, not necessarily the last row of the sheet.
Finally, the function returns the last row number.
Working with Blank Rows
If your data contains blank rows, the method above might not work as expected. It will return the last row with content, which might not be the actual last row if there are blank rows at the end of your data.
In this case, you’ll need to loop through your data backwards and stop at the first non-empty cell. Here’s a function that does that:
function getLastRowWithContent() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var data = sheet.getDataRange().getValues();
for (var i = data.length - 1; i >= 0; i--) {
for (var j = 0; j < data[i].length; j++) {
if (data[i][j]) return i + 1;
}
}
return -1;
}
The above script gets all the data in the sheet with sheet.getDataRange().getValues(), then loops through the data backwards. If it finds a cell that is not empty, it returns the row number of that cell (adding 1 because array indexes are 0-based). If it doesn’t find any non-empty cells, it returns -1.
By using these methods, you can accurately find the last row in your Google Sheets data, even if your data contains blank rows.
Conclusion
Google Sheets, combined with Google Apps Script, is a powerful tool that can help you manage and analyze your data. By learning how to navigate your data and find specific rows or cells, you can make the most of this tool and simplify your data management tasks.
