{"id":15466,"date":"2023-08-02T09:00:00","date_gmt":"2023-08-02T00:00:00","guid":{"rendered":"https:\/\/www.autooffice.co.kr\/blog\/2023\/08\/02\/getting-the-last-row-of-a-column-in-apps-script\/"},"modified":"2025-09-15T18:40:49","modified_gmt":"2025-09-15T09:40:49","slug":"getting-the-last-row-of-a-column-in-apps-script","status":"publish","type":"post","link":"https:\/\/www.autooffice.co.kr\/en\/blog\/2023\/08\/02\/getting-the-last-row-of-a-column-in-apps-script\/","title":{"rendered":"Getting the Last Row of a Column which has value in Apps Script"},"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

    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()<\/code> method, we can determine the maximum rows in a sheet and then iterate through the column from bottom to upward <\/span>to find the last non-empty row.<\/p>\n

    Code<\/h2>\n
    function getLastRowOfCol() {\n  const SS = SpreadsheetApp.getActiveSpreadsheet();\n  const sheet = SS.getSheetByName('Sheet2');\n\n  const col = 'A';\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  Logger.log(lastRow);\n}<\/code><\/pre>\n
    Code Explanation<\/h2>\n
    This code defines a function named getLastRowOfCol<\/code>. It begins by obtaining the active spreadsheet and accessing a specific sheet named “Sheet2”.<\/p>\n
    A variable col<\/code> is set to the desired column letter, in this case, ‘A’. The getMaxRows()<\/code> method is used to determine the maximum number of rows in the sheet.<\/p>\n
    A range is defined to include all the cells in the column from the first row to the last row. The getValues()<\/code> method is then used to retrieve the values of all the cells in the range.<\/p>\n
    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<\/code> until a non-empty row is found.<\/p>\n
    The final value of lastRow<\/code> is then logged to the Logger.<\/p>\n
    Example<\/h2>\n
    Let’s say we have a spreadsheet with data in column A. We want to find the last row of this column.<\/p>\n
    Running the getLastRowOfCol<\/code> function will log the last row number to the Logger.<\/p>\n
    AI Prompt<\/h2>\n
    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.<\/s><\/p>\n
    Example AI Prompt: “Write a function to get the last row of column A in a spreadsheet.”<\/p>\n","protected":false},"excerpt":{"rendered":"
    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 to find the last non-empty row.<\/p>\n","protected":false},"author":1,"featured_media":15482,"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,533,7],"class_list":["post-15466","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-spreadsheet","category-apps-script","tag-ai-assisted","tag-script","tag-getlastrowofcol","tag-spreadsheet"],"translation":{"provider":"WPGlobus","version":"3.0.3","language":"en","enabled_languages":["ko","en"],"languages":{"ko":{"title":true,"content":true,"excerpt":true},"en":{"title":true,"content":true,"excerpt":true}}},"aioseo_notices":[],"aioseo_head":"\n\t\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t