{"id":15467,"date":"2023-08-02T09:00:00","date_gmt":"2023-08-02T00:00:00","guid":{"rendered":"https:\/\/www.autooffice.co.kr\/blog\/2023\/08\/02\/exploring-getlastrowofcol-function-in-apps-script\/"},"modified":"2025-09-15T18:42:48","modified_gmt":"2025-09-15T09:42:48","slug":"exploring-getlastrowofcol-function-in-apps-script","status":"publish","type":"post","link":"https:\/\/www.autooffice.co.kr\/en\/blog\/2023\/08\/02\/exploring-getlastrowofcol-function-in-apps-script\/","title":{"rendered":"Add parameters to getLastRowOfCol function"},"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 explore the getLastRowOfCol<\/code> 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.<\/span>We will analyze the code, explain how it works, and provide an example to demonstrate its usage.<\/p>\n

    Code<\/h2>\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}<\/code><\/pre>\n
    Code Explanation<\/h2>\n
    The getLastRowOfCol<\/code> function takes two parameters: sheet<\/code> and col<\/code>. Here, sheet refers to the sheet itself designated by getSheetByName, not the sheet name.<\/span>It first retrieves the maximum number of rows in the sheet using the getMaxRows<\/code> method.<\/p>\n
    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.<\/p>\n
    The getRange<\/code> and getValues<\/code> methods are then used to retrieve the values of the cells within the specified range. These values are stored in the colValues<\/code> variable.<\/p>\n
    A for loop is used to iterate through the colValues<\/code> 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<\/code> variable.<\/p>\n
    Finally, the function returns the last non-empty row number.<\/p>\n
    Example<\/h2>\n
    Let’s say we have a Google Sheets spreadsheet with the following data:<\/p>\n\n\n\n\n\n\n\n
    Row 1<\/th>\nColumn A<\/th>\nColumn B<\/th>\nColumn C<\/th>\n<\/tr>\n
    Row 2<\/td>\nData 1<\/td>\nData 2<\/td>\nData 3<\/td>\n<\/tr>\n
    Row 3<\/td>\nData 4<\/td>\nData 5<\/td>\n<\/td>\n<\/tr>\n
    Row 4<\/td>\nData 6<\/td>\nData 7<\/td>\nData 8<\/td>\n<\/tr>\n
    Row 5<\/td>\nData 9<\/td>\n<\/td>\nData 10<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    If we call the getLastRowOfCol<\/code> function with the sheet and column parameters, it will return the last non-empty row number for that column. For example, getLastRowOfCol(sheet, 'B')<\/code> will return 4, as the last non-empty row in column B is row 4.<\/p>\n
    AI Prompt<\/h2>\n
    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<\/code>, getRange<\/code>, and getValues<\/code> 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.<\/p>\n","protected":false},"excerpt":{"rendered":"
    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. We will analyze the code, explain how it works, and provide an example to demonstrate its usage.<\/p>\n","protected":false},"author":1,"featured_media":15489,"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-15467","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\/15467","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=15467"}],"version-history":[{"count":4,"href":"https:\/\/www.autooffice.co.kr\/en\/wp-json\/wp\/v2\/posts\/15467\/revisions"}],"predecessor-version":[{"id":17232,"href":"https:\/\/www.autooffice.co.kr\/en\/wp-json\/wp\/v2\/posts\/15467\/revisions\/17232"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.autooffice.co.kr\/en\/wp-json\/wp\/v2\/media\/15489"}],"wp:attachment":[{"href":"https:\/\/www.autooffice.co.kr\/en\/wp-json\/wp\/v2\/media?parent=15467"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.autooffice.co.kr\/en\/wp-json\/wp\/v2\/categories?post=15467"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.autooffice.co.kr\/en\/wp-json\/wp\/v2\/tags?post=15467"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}