Retrieving Data Range Values in Apps Script
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
In this blog post, we will explore how to retrieve data range values in Apps Script. We will learn how to access data from multiple sheets in a spreadsheet using a loop and the getDataRange() method.
Code
|
1 2 3 4 5 6 7 8 9 10 |
function getDataRangeValues() { const SS = SpreadsheetApp.getActiveSpreadsheet() const sheetNames = ['Sheet1', 'Sheet2'] for (let i = 0; i < sheetNames.length; i++) { const sheet = SS.getSheetByName(sheetNames[i]) const values = sheet.getDataRange().getValues() Logger.log(values) } } |
Code Explanation
The code declares a function named getDataRangeValues. Inside the function, the active spreadsheet is accessed using SpreadsheetApp.getActiveSpreadsheet(). An array sheetNames is defined with the names of the sheets to retrieve data from.
A for loop is used to iterate over the sheet names array. For each sheet, the getSheetByName() method is used to retrieve the sheet object. The getDataRange() method is then called on the sheet object to get the range of data. Finally, the getValues() method is used to retrieve the values in the data range, and the values are logged using Logger.log().
Example
Let’s say we have a spreadsheet with two sheets named “Sheet1” and “Sheet2”. Each sheet contains a table with some data. We want to retrieve all the values from these sheets and log them using the Logger.log() method.
When we execute the getDataRangeValues() function, it will loop through the sheet names array, retrieve the sheet objects, get the data range, and log the values from each sheet.
AI Prompt
Write a function that retrieves data range values from multiple sheets in a spreadsheet. Use a for loop to iterate over the sheet names array and retrieve the values using the getDataRange() and getValues() methods. Log the values for each sheet using Logger.log().
