Get data of all rows and columns from multiple sheets with 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
This blog post will guide you on how to fetch data from multiple sheets in Apps Script. It provides a function that retrieves data of all rows and columns from each sheet in a given spreadsheet and logs the values.
Code
|
1 2 3 4 5 6 7 8 9 10 |
function getWholeData() { const SS = SpreadsheetApp.getActiveSpreadsheet(); const sheetNames = ['Sheet1', 'Sheet2']; for (let i = 0; i < sheetNames.length; i++) { const sheet = SS.getSheetByName(sheetNames[i]); const value = sheet.getRange(1, 1, sheet.getMaxRows(), sheet.getMaxColumns()).getValues(); Logger.log(value); } } |
Code Explanation
The getWholeData function retrieves the active spreadsheet using SpreadsheetApp.getActiveSpreadsheet(). It defines an array sheetNames with the names of the sheets to fetch data from.
A loop is used to iterate through each sheet in the sheetNames array. Inside the loop, the getSheetByName method is used to get a reference to the sheet. The getRange method is then used to get all the values in the sheet.
Let’s take a closer look at getRange(1, 1, sheet.getMaxRows(), sheet.getMaxColumns()).
You can use the getRange() method to get the data range of all rows and columns of a sheet.
It is used in the form of getRange(row, column, numRows, numColumns), where “1, 1, sheet.getMaxRows(), sheet.getMaxColumns()” is from the first row to the last row and from the first column to the last column indicates a range.
Use the getValues() method to get the values in that range and assign them to the value variable.
The retrieved values are logged using Logger.log.
Example
Let’s say we have a spreadsheet with two sheets named “Sheet1” and “Sheet2”. Each sheet contains data in different ranges.
By running the getWholeData function, we can retrieve and log the values from both sheets:
|
1 2 3 4 5 6 7 8 9 |
[ [Sheet1 Value1, Sheet1 Value2, Sheet1 Value3, ...], [Sheet1 Value4, Sheet1 Value5, Sheet1 Value6, ...], ... [Sheet1 ValueN, Sheet1 ValueN+1, Sheet1 ValueN+2, ...] ] [ [Sheet2 Value1, Sheet2 Value2, Sheet2 Value3, ...], [Sheet2 Value4, Sheet2 Value5, Sheet2 Value6, ...], ... [Sheet2 ValueN, Sheet2 ValueN+1, Sheet2 ValueN+2, ...] ] |
AI Prompt
Write a function that fetches data of all rows and columns from multiple sheets in a spreadsheet using getMaxRows and getMaxColumns and logs the values. The function should use an array of sheet names and iterate through each sheet to retrieve the values. Log the values using the Logger.log method.
