{"id":15468,"date":"2023-08-02T09:00:00","date_gmt":"2023-08-02T00:00:00","guid":{"rendered":"https:\/\/www.autooffice.co.kr\/blog\/2023\/08\/02\/retrieving-a-column-values-from-sheet2\/"},"modified":"2025-09-15T18:40:02","modified_gmt":"2025-09-15T09:40:02","slug":"retrieving-a-column-values-from-sheet2","status":"publish","type":"post","link":"https:\/\/www.autooffice.co.kr\/en\/blog\/2023\/08\/02\/retrieving-a-column-values-from-sheet2\/","title":{"rendered":"Get the value of column A by applying getLastRowOfCol(sheet, col)"},"content":{"rendered":"

This post is a corrected post after AI explains the code included in the book “Google Apps Script 101: Building Work Automation For Free”<\/a>. Added a strikethrough<\/s> when editing what AI has written, and added color<\/span> when edited by author<\/p>\n

Table of Contents<\/h2>\n
    \n
  1. Summary<\/a><\/li>\n
  2. Code<\/a><\/li>\n
  3. Code Explanation<\/a><\/li>\n
  4. Example<\/a><\/li>\n
  5. AI Prompt<\/a><\/li>\n<\/ol>\n

    Summary<\/h2>\n

    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.<\/p>\n

    Code<\/h2>\n
    function getSheet2AColValues() {\n  const SS = SpreadsheetApp.getActiveSpreadsheet();\n  const sheet = SS.getSheetByName('Sheet2');\n  const col = 'A';\n\n  let lastRowOfCol = getLastRowOfCol(sheet, col);\n  Logger.log(`${col}1:${col + lastRowOfCol}`);\n\n  let value = sheet.getRange(`${col}1:${col + lastRowOfCol}`).getValues();\n  Logger.log(value);\n}<\/code><\/pre>\n
    Code Explanation<\/h2>\n
    The code defines a function getSheet2AColValues<\/code> that retrieves the values from column A in Sheet2. The function uses the SpreadsheetApp.getActiveSpreadsheet()<\/code> method to get the active spreadsheet, and getSheetByName()<\/code> method to get the Sheet2 by name.<\/p>\n
    The getLastRowOfCol()<\/code> function is called to determine the last row of column A. The Logger.log()<\/code> method is used to log the range of values being retrieved from column A.<\/p>\n
    The getRange()<\/code> method is used to select the range of values from column A, and the getValues()<\/code> method retrieves the values from that range. Finally, the retrieved values are logged using the Logger.log()<\/code> method.<\/p>\n
    Example<\/h2>\n
    Let’s say we have a Google Sheet named Sheet2, and column A contains the following values:<\/p>\n\n\n\n\n\n\n\n
    A<\/th>\n<\/tr>\n
    Value 1<\/td>\n<\/tr>\n
    Value 2<\/td>\n<\/tr>\n
    Value 3<\/td>\n<\/tr>\n
    <\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    When you run the getSheet2AColValues()<\/code> function, it will log the following:<\/p>\n
    A1:A3\n[[\"Value 1\"], [\"Value 2\"], [\"Value 3\"]]<\/code><\/pre>\n
    AI Prompt<\/h2>\n
    Write a function that retrieves the values from column A in Sheet2 and logs them using the Logger class.<\/s><\/p>\n
    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.<\/span><\/p>\n
    function getLastRowOfCol(sheet, col) { \n  let lastRow = sheet.getMaxRows(); \n  Logger.log(`${col}1:${col + lastRow}`) \n\n  let colValues = sheet.getRange(`${col}1:${col + lastRow}`).getValues(); \n  \n  for (; colValues[lastRow - 1] == '' && lastRow > 0; lastRow--) { } \n  return lastRow; \n}<\/pre>\n
    Write a function that uses this function to get all the values from column A on sheet2 and log an array.<\/span><\/p>\n","protected":false},"excerpt":{"rendered":"
    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.<\/p>\n","protected":false},"author":1,"featured_media":15495,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_kad_post_transparent":"","_kad_post_title":"","_kad_post_layout":"","_kad_post_sidebar_id":"","_kad_post_content_style":"","_kad_post_vertical_padding":"","_kad_post_feature":"","_kad_post_feature_position":"","_kad_post_header":false,"_kad_post_footer":false,"footnotes":""},"categories":[5,118],"tags":[512,69,534,7],"class_list":["post-15468","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-spreadsheet","category-apps-script","tag-ai-assisted","tag-script","tag-getlastrowofcolparam","tag-spreadsheet"],"translation":{"provider":"WPGlobus","version":"3.0.2","language":"en","enabled_languages":["ko","en"],"languages":{"ko":{"title":true,"content":true,"excerpt":true},"en":{"title":true,"content":true,"excerpt":true}}},"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.autooffice.co.kr\/en\/wp-json\/wp\/v2\/posts\/15468","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.autooffice.co.kr\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.autooffice.co.kr\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.autooffice.co.kr\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.autooffice.co.kr\/en\/wp-json\/wp\/v2\/comments?post=15468"}],"version-history":[{"count":4,"href":"https:\/\/www.autooffice.co.kr\/en\/wp-json\/wp\/v2\/posts\/15468\/revisions"}],"predecessor-version":[{"id":17228,"href":"https:\/\/www.autooffice.co.kr\/en\/wp-json\/wp\/v2\/posts\/15468\/revisions\/17228"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.autooffice.co.kr\/en\/wp-json\/wp\/v2\/media\/15495"}],"wp:attachment":[{"href":"https:\/\/www.autooffice.co.kr\/en\/wp-json\/wp\/v2\/media?parent=15468"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.autooffice.co.kr\/en\/wp-json\/wp\/v2\/categories?post=15468"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.autooffice.co.kr\/en\/wp-json\/wp\/v2\/tags?post=15468"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}