{"id":15438,"date":"2023-08-01T09:00:00","date_gmt":"2023-08-01T00:00:00","guid":{"rendered":"https:\/\/www.autooffice.co.kr\/blog\/2023\/08\/01\/iterating-through-sheets-and-getting-values-in-apps-script\/"},"modified":"2026-08-21T16:53:04","modified_gmt":"2026-08-21T07:53:04","slug":"iterating-through-sheets-and-getting-values-in-apps-script","status":"publish","type":"post","link":"https:\/\/www.autooffice.co.kr\/en\/blog\/2023\/08\/01\/iterating-through-sheets-and-getting-values-in-apps-script\/","title":{"rendered":"Iterating Through Sheets and Getting Values using while"},"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 iterating through multiple sheets in Google Sheets using Apps Script. We will learn how to use a while loop to iterate through an array of sheet names, access each sheet, and retrieve values from a specific range of cells.<\/p>\n

    Code<\/h2>\n
    function getA1C3ValuesWhileIteration() {\n  const SS = SpreadsheetApp.getActiveSpreadsheet()\n  const sheetNames = ['Sheet1', 'Sheet2']\n\n  let i = 0\n  while (i < sheetNames.length) {\n    const sheet = SS.getSheetByName(sheetNames[i])\n    const value = sheet.getRange('A1:C3').getValues()\n    Logger.log(value)\n    i++\n  }\n}<\/code><\/pre>\n
    Code Explanation<\/h2>\n
    The code defines a function named getA1C3ValuesWhileIteration<\/code>. Inside the function, we first obtain the active spreadsheet using SpreadsheetApp.getActiveSpreadsheet()<\/code>. We then create an array sheetNames<\/code> containing the names of the sheets we want to iterate through.<\/p>\n
    A while loop is used to iterate through the sheetNames<\/code> array. Inside the loop, we use SpreadsheetApp.getSheetByName()<\/code> to access each sheet based on its name. We then use getRange()<\/code> to specify the range of cells (‘A1:C3’) from which we want to retrieve values. Finally, getValues()<\/code> is called to retrieve the values from the range, and Logger.log()<\/code> is used to log the values.<\/p>\n
    Example<\/h2>\n
    When you run the getA1C3ValuesWhileIteration<\/code> function, it will iterate through the sheets named ‘Sheet1’ and ‘Sheet2’ in the active spreadsheet. For each sheet, it will retrieve the values from the range A1:C3 and log them using Logger.log()<\/code>.<\/p>\n
    AI Prompt<\/h2>\n
    Write a function that iterates through an array of sheet names, accesses each sheet, and retrieves values from a specific range of cells (e.g., ‘A1:C3’). Log the values using Logger.log()<\/code>.<\/p>\n","protected":false},"excerpt":{"rendered":"
    This blog post will guide you through the process of iterating through multiple sheets in Google Sheets using Apps Script. We will learn how to use a while loop to iterate through an array of sheet names, access each sheet, and retrieve values from a specific range of cells.<\/p>\n","protected":false},"author":1,"featured_media":15456,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_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":[118],"tags":[512,355,7,528],"class_list":["post-15438","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-apps-script","tag-ai-assisted","tag-apps-script","tag-spreadsheet","tag-while"],"translation":{"provider":"WPGlobus","version":"3.0.5","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\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