Inserting Values to Dynamic 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
Summary
In this blog post, we will explore how to insert values from one sheet to another in a dynamic range using Apps Script. We will retrieve values from a source sheet, determine the size of the data, and then set the same values in a target sheet.
Code
|
1 2 3 4 5 6 7 8 9 10 |
function insertValuesToDynamicRange() { const SS = SpreadsheetApp.getActiveSpreadsheet() const T_SHEET2 = SS.getSheetByName('Sheet2') const T_SHEET4 = SS.getSheetByName('Sheet4') const values = T_SHEET2.getDataRange().getValues() let cell = T_SHEET4.getRange(1, 1, values.length, values[0].length) cell.setValues(values) } |
Code Explanation
The code defines a function named insertValuesToDynamicRange. It retrieves the active spreadsheet and assigns it to the SS variable. It then gets the sheets named ‘Sheet2’ and ‘Sheet4’ and assigns them to the T_SHEET2 and T_SHEET4 variables, respectively.
The getDataRange method is used to retrieve the range of data in T_SHEET2. The values in the range are retrieved using the getValues method and stored in the values variable.
A Range object is created in T_SHEET4 using the getRange method, specifying the starting row and column, as well as the number of rows and columns based on the size of the values array.
getRange(1, 1, values.length, values[0].length): This part is a method to select a range of data from sheet ‘T_SHEET4’. The values passed as arguments are as follows.
1st argument: starting row number of data range (starting from 1)
2nd argument: starting column number of data range (starting from 1)
3rd argument: number of rows of data range (row length of values array)
4th argument: Number of columns in the data range (column length of the first element (row) of the values array)
Finally, the setValues method is used to set the retrieved values in the target range.
Example
Let’s say we have a spreadsheet with two sheets: ‘SourceData’ and ‘TargetData’. The ‘SourceData’ sheet contains a table of values. We want to copy these values to the ‘TargetData’ sheet in a dynamic range.
When we execute the insertValuesToDynamicRange function, it will retrieve the values from ‘SourceData’, determine the size of the data, and set the same values in the ‘TargetData’ sheet starting from cell A1 and spanning the same number of rows and columns as the source data.
AI Prompt
Write a function that inserts values from one sheet to another in a dynamic range. Retrieve the active spreadsheet, get the sheets by name, retrieve the values from the source sheet, determine the size of the data, and set the same values in the target sheet.
