Insert Values into Column 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

1. Summary
2. Code
3. Code Explanation
4. Example
5. AI Prompt

Summary

In this blog post, we will learn how to use Apps Script to insert array values into an array a column in Google Sheets. We will write a function that takes an array of values and inserts them into a specified column in a Google Sheet.

Code

Code Explanation

The code defines a function named insertValuesArrayCol. It retrieves the active spreadsheet and the sheet named ‘Sheet3’ using the SpreadsheetApp class.

An array of values is created and assigned to the values variable. This array contains various data types, including numbers, strings, booleans, dates, and a formula referencing another cell. The ranges variable specifies the range where the values will be inserted, in this case, column A and rows 1 to 6.

The getRange method is used to get the range specified by the ranges variable, and the setValues method is used to insert the values into the range.

Example

Suppose we have a Google Sheet with a column A labeled ‘Data’. By executing the insertValuesArrayCol function, the following values will be inserted into column A:

AI Prompt

Write a function that inserts the values [1, ‘Google Sheets’, true, new Date(), ‘=LEN(B2)’, ‘=query(\’Sheet2\’!A1:E4,”select *”)’] into column A of a Google Sheet named ‘Sheet3’.

In Google Sheets named ‘Sheet3’, [1, ‘Google Sheet’, true, new Date(), ‘=LEN(B2)’, ‘= Write a function that inserts the values of query(\’sheet2\’!A1:E4,”select *”)’] vertically into column A at once.

(Even if you write a prompt like this, AI might write the code to insert values one by one with a for statement without using setValues.)