Inserting values repeatedly in Google Sheets using 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 learn how to insert values repeatedly into a Google Sheets using Apps Script. We will explore a code snippet that inserts values into specific cells of a sheet and also provide an example demonstrating its usage.
Code
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
function insertValues() { const SS = SpreadsheetApp.getActiveSpreadsheet() const sheet = SS.getSheetByName('Sheet1') const values = [1, 'Google Sheets', true, new Date(), '=LEN(B2)', '=query(\'Sheet2\'!A1:E4,"select *")'] const ranges = ['B1', 'B2', 'B3', 'B4', 'B5', 'B6'] const valuesLabel = ['Number', 'String', 'Boolean', 'Current Date', 'Length of B2 Cell String', 'Query Function to Get Sheet2 A1:E4'] const rangesLabel = ['A1', 'A2', 'A3', 'A4', 'A5', 'A6'] for (i = 0; i < values.length; i++) { let cellLabel = sheet.getRange(rangesLabel[i]) cellLabel.setValue(valuesLabel[i]) let cell = sheet.getRange(ranges[i]) cell.setValue(values[i]) } } |
Code Explanation
The code defines a function named insertValues. It retrieves the active spreadsheet and the sheet named ‘Sheet1’ to work with.
Two arrays, values and ranges, are declared to store the values to be inserted and the corresponding cell ranges. Another two arrays, valuesLabel and rangesLabel, store labels for each value and cell range.
A loop iterates through the arrays, setting the labels and values in their respective cells using the setValue method of the Range class.
Example
When you run the insertValues function, it will insert the following values into the specified cells:
- Number: 1 (cell B1)
- String: “Google Sheets” (cell B2)
- Boolean: true (cell B3)
- Current Date: current date (cell B4)
- Length of B2 Cell String: length of the string in cell B2 (cell B5)
- Query Function to Get Sheet2 A1:E4: results of the query function on Sheet2 (cell B6)
AI Prompt
Write a function that inserts values into specific cells of a Google Sheets using Apps Script. The function should use the provided arrays to set labels and values in their respective cells.
When you have an array like the one below, write an Apps Script function that correctly inserts the labels and values using loops.
|
1 2 3 4 5 6 7 |
const values = [1, '구글 시트', true, new Date(), '=LEN(B2)', '=query(\'시트2\'!A1:E4,"select *")'] const ranges = ['B1', 'B2', 'B3', 'B4', 'B5', 'B6'] const valuesLabel = ['숫자', '문자열', '불리언', '현재 날짜', 'B2셀 문자열의 길이 함수', '시트2의 A1:E4 가져오기 쿼리함수'] const rangesLabel = ['A1', 'A2', 'A3', 'A4', 'A5', 'A6'] |
