To get the last row with data in a specific column of a Google Sheet using Apps Script, you can use the getLastRow() method along with the getColumn() method. Here’s an example code snippet:
function getLastRowInColumn() { var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); var column = 1; // Replace with the desired column number (e.g., 1 for column A) var lastRow = sheet.getLastRow(); var range = sheet.getRange(1, column, lastRow); var values = range.getValues(); for (var i = lastRow - 1; i >= 0; i--) { if (values[i][0] !== "") { lastRow = i + 1; break; } } Logger.log('Last row with data in column A: ' + lastRow); }
In this code, the getLastRowInColumn function first retrieves the active sheet using SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(). You can modify this line to access a specific sheet if needed.
Next, you need to specify the column number you want to find the last row of data in. In the example code, the column variable is set to 1, which corresponds to column A. Adjust this value as per your desired column.
The getLastRow() method is then used to retrieve the last row number in the sheet, regardless of the column.
A range is defined using getRange() to encompass the entire specified column, starting from row 1 to the last row.
The getValues() method is used to retrieve the values within the range. This returns a two-dimensional array of values.
A loop is then used to iterate through the values from the last row to the first row in reverse order. If a non-empty cell is found in the specified column, the loop breaks and the last row variable is updated accordingly.
Finally, the last row number is logged using Logger.log().
You can call the getLastRowInColumn function from another function or set up a trigger to execute it based on your needs. When executed, it will determine the last row with data in the specified column and log the result.
Feel free to modify the code to suit your specific requirements and work with different columns.