Getting the Last Row of a Column which has value in Apps Script
This post is a corrected post after AI explains the code included in the book “Google Apps Script 101: Building Work Automation For Free”. Added a strikethrough when editing what AI has written, and added color when edited by author
Table of Contents
Summary
This blog post will guide you through the process of obtaining the last row of a specific column in Apps Script. By utilizing the getMaxRows() method, we can determine the maximum rows in a sheet and then iterate through the column from bottom to upward to find the last non-empty row.
Code
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
function getLastRowOfCol() { const SS = SpreadsheetApp.getActiveSpreadsheet(); const sheet = SS.getSheetByName('Sheet2'); const col = 'A'; let lastRow = sheet.getMaxRows(); Logger.log(`${col}1:${col + lastRow}`); let colValues = sheet.getRange(`${col}1:${col + lastRow}`).getValues(); for (; colValues[lastRow - 1] == '' && lastRow > 0; lastRow--) {} Logger.log(lastRow); } |
Code Explanation
This code defines a function named getLastRowOfCol. It begins by obtaining the active spreadsheet and accessing a specific sheet named “Sheet2”.
A variable col is set to the desired column letter, in this case, ‘A’. The getMaxRows() method is used to determine the maximum number of rows in the sheet.
A range is defined to include all the cells in the column from the first row to the last row. The getValues() method is then used to retrieve the values of all the cells in the range.
A for loop is implemented to iterate through the column values from the last row to the first row. It checks if the value in the last row is empty and decrements lastRow until a non-empty row is found.
The final value of lastRow is then logged to the Logger.
Example
Let’s say we have a spreadsheet with data in column A. We want to find the last row of this column.
Running the getLastRowOfCol function will log the last row number to the Logger.
AI Prompt
Write a function that retrieves the last row of a specific column in a sheet. The function should log the last row number to the Logger.
Example AI Prompt: “Write a function to get the last row of column A in a spreadsheet.”
