Iterating Through Sheets and Getting Values using while
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
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.
Code
|
1 2 3 4 5 6 7 8 9 10 11 12 |
function getA1C3ValuesWhileIteration() { const SS = SpreadsheetApp.getActiveSpreadsheet() const sheetNames = ['Sheet1', 'Sheet2'] let i = 0 while (i < sheetNames.length) { const sheet = SS.getSheetByName(sheetNames[i]) const value = sheet.getRange('A1:C3').getValues() Logger.log(value) i++ } } |
Code Explanation
The code defines a function named getA1C3ValuesWhileIteration. Inside the function, we first obtain the active spreadsheet using SpreadsheetApp.getActiveSpreadsheet(). We then create an array sheetNames containing the names of the sheets we want to iterate through.
A while loop is used to iterate through the sheetNames array. Inside the loop, we use SpreadsheetApp.getSheetByName() to access each sheet based on its name. We then use getRange() to specify the range of cells (‘A1:C3’) from which we want to retrieve values. Finally, getValues() is called to retrieve the values from the range, and Logger.log() is used to log the values.
Example
When you run the getA1C3ValuesWhileIteration 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().
AI Prompt
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().
