Inserting Values into Array Row 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 explore how to insert values into an array row in Google Sheets using Apps Script. We will learn how to use the setValues method to insert an array of values into a specified range in a sheet.
Code
|
1 2 3 4 5 6 7 8 9 10 |
function insertValuesArrayRow() { const SS = SpreadsheetApp.getActiveSpreadsheet() const sheet = SS.getSheetByName('Sheet3') const values = [[1, 'Google Sheets', true, new Date(), '=LEN(B2)', '=query(\'Sheet2\'!A1:E4,"select *")']] const ranges = 'A1:F1' let cell = sheet.getRange(ranges) cell.setValues(values) } |
Code Explanation
The code defines a function named insertValuesArrayRow. It first gets the active spreadsheet and then retrieves a specific sheet named ‘Sheet3’.
An array of values is created and assigned to the variable values. This array contains multiple values of different data types.
Here, the array must be 2-dimensional.
The range A1:F1 is specified as the target range where the values will be inserted.
A range object is obtained using getRange method, and then the setValues method is used to insert the values into the specified range.
Example
Let’s say you have a Google Sheet with the following structure:
| A | B | C | D | E | F |
|---|---|---|---|---|---|
You can use the insertValuesArrayRow function to insert an array of values into the first row of the sheet:
|
1 |
insertValuesArrayRow(); |
After running the function, the first row of the sheet will be populated with the specified values:
| A | B | C | D | E | F |
|---|---|---|---|---|---|
| 1 | Google Sheets | true | Current Date | =LEN(B2) | =query(‘Sheet2’!A1:E4,”select *”) |
AI Prompt
Write a function that inserts an array of values into the first row of a Google Sheet. Use the setValues method and specify the range as A1:F1. The array of values should contain a number, a string, a boolean, a date, a formula, and a query function.
