Add parameters to getLastRowOfCol function
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
In this blog post, we will explore the getLastRowOfCol function in Apps Script. This function is used to determine the last non-empty row in a specific column of a Google Sheets spreadsheet. In the previous post, the sheet and column were specified in the function, but we will change them into parameters so that they can be used in general cases.We will analyze the code, explain how it works, and provide an example to demonstrate its usage.
Code
|
1 2 3 4 5 6 7 8 9 |
function getLastRowOfCol(sheet, col) { 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--) { } return lastRow; } |
Code Explanation
The getLastRowOfCol function takes two parameters: sheet and col. Here, sheet refers to the sheet itself designated by getSheetByName, not the sheet name.It first retrieves the maximum number of rows in the sheet using the getMaxRows method.
A log statement is used to display the range of cells being accessed in the sheet, which includes the specified column from the first row to the last row.
The getRange and getValues methods are then used to retrieve the values of the cells within the specified range. These values are stored in the colValues variable.
A for loop is used to iterate through the colValues array starting from the last row. The loop continues until it reaches a non-empty cell or until the row number becomes zero. The last non-empty row number is stored in the lastRow variable.
Finally, the function returns the last non-empty row number.
Example
Let’s say we have a Google Sheets spreadsheet with the following data:
| Row 1 | Column A | Column B | Column C |
|---|---|---|---|
| Row 2 | Data 1 | Data 2 | Data 3 |
| Row 3 | Data 4 | Data 5 | |
| Row 4 | Data 6 | Data 7 | Data 8 |
| Row 5 | Data 9 | Data 10 |
If we call the getLastRowOfCol function with the sheet and column parameters, it will return the last non-empty row number for that column. For example, getLastRowOfCol(sheet, 'B') will return 4, as the last non-empty row in column B is row 4.
AI Prompt
Write a function that takes a Google Sheets sheet and a column as parameters. Inside the function, determine the last non-empty row in the specified column and return its row number. Use methods like getMaxRows, getRange, and getValues to achieve this. Include log statements to display the range of cells being accessed. Handle cases where the column may contain empty cells. Finally, test the function by calling it with appropriate parameters.
