Get the value of column A by applying getLastRowOfCol(sheet, col)
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 learn how to retrieve column values from Sheet2 in Google Sheets using Apps Script. We will explore the code that retrieves the values from a specific column and logs them using the Logger class.
Code
|
1 2 3 4 5 6 7 8 9 10 11 |
function getSheet2AColValues() { const SS = SpreadsheetApp.getActiveSpreadsheet(); const sheet = SS.getSheetByName('Sheet2'); const col = 'A'; let lastRowOfCol = getLastRowOfCol(sheet, col); Logger.log(`${col}1:${col + lastRowOfCol}`); let value = sheet.getRange(`${col}1:${col + lastRowOfCol}`).getValues(); Logger.log(value); } |
Code Explanation
The code defines a function getSheet2AColValues that retrieves the values from column A in Sheet2. The function uses the SpreadsheetApp.getActiveSpreadsheet() method to get the active spreadsheet, and getSheetByName() method to get the Sheet2 by name.
The getLastRowOfCol() function is called to determine the last row of column A. The Logger.log() method is used to log the range of values being retrieved from column A.
The getRange() method is used to select the range of values from column A, and the getValues() method retrieves the values from that range. Finally, the retrieved values are logged using the Logger.log() method.
Example
Let’s say we have a Google Sheet named Sheet2, and column A contains the following values:
| A |
|---|
| Value 1 |
| Value 2 |
| Value 3 |
When you run the getSheet2AColValues() function, it will log the following:
|
1 2 |
A1:A3 [["Value 1"], ["Value 2"], ["Value 3"]] |
AI Prompt
Write a function that retrieves the values from column A in Sheet2 and logs them using the Logger class.
Now, since you are creating a new function by applying one function, you must first present the function to AI and tell it to apply it.
|
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; } |
Write a function that uses this function to get all the values from column A on sheet2 and log an array.
