Inserting Values to a Small Range 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

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

Summary

In this blog post, we will explore how to insert values from one range to another in Apps Script. We will write a function that retrieves values from a specific range in one sheet and inserts them into a small range in another sheet.

Code

Code Explanation

The code defines a function named insertValuesToSmallRange. It retrieves the active spreadsheet and assigns it to the SS variable. It also gets references to two sheets named ‘Sheet2’ and ‘Sheet4’ and assigns them to T_SHEET2 and T_SHEET4 variables respectively.

Using the getDataRange method, the code retrieves all the values from the range in ‘Sheet2’ and assigns them to the values variable.

The code specifies the range ‘A1:E1’ in ‘Sheet4’ using the getRange method and assigns it to the cell variable. Finally, it sets the values from ‘Sheet2’ into the specified range in ‘Sheet4’ using the setValues method.

Example

Let’s say we have a spreadsheet with two sheets: ‘Data’ and ‘Summary’. The ‘Data’ sheet contains a range of values in cells A1:E5, and we want to insert these values into cells A1:E1 in the ‘Summary’ sheet.

We can use the insertValuesToSmallRange function to achieve this. Here’s how:

Running the above code will retrieve the values from the ‘Data’ sheet and insert them into the specified range in the ‘Summary’ sheet.

AI Prompt

Write a function that retrieves values from a specific range in one sheet and inserts them into a small range in another sheet.

Function Signature: function insertValuesToSmallRange()

Required variables: SS, T_SHEET2, T_SHEET4, values, ranges, cell

Hint: Use the getSheetByName, getDataRange, getRange, and setValues methods.