{"id":15555,"date":"2023-08-11T09:00:00","date_gmt":"2023-08-11T00:00:00","guid":{"rendered":"https:\/\/www.autooffice.co.kr\/blog\/2023\/08\/11\/inserting-values-to-dynamic-range-in-apps-script\/"},"modified":"2025-09-15T17:57:37","modified_gmt":"2025-09-15T08:57:37","slug":"inserting-values-to-dynamic-range-in-apps-script","status":"publish","type":"post","link":"https:\/\/www.autooffice.co.kr\/en\/blog\/2023\/08\/11\/inserting-values-to-dynamic-range-in-apps-script\/","title":{"rendered":"Inserting Values to Dynamic Range in Apps Script"},"content":{"rendered":"

This post is a corrected post after AI explains the code included in the book “Google Apps Script 101: Building Work Automation For Free”<\/a>. Added a strikethrough<\/s> when editing what AI has written, and added color<\/span> when edited by author<\/p>\n

Table of Contents<\/h2>\n
    \n
  1. Summary<\/a><\/li>\n
  2. Code<\/a><\/li>\n
  3. Code Explanation<\/a><\/li>\n
  4. Example<\/a><\/li>\n
  5. AI Prompt<\/a><\/li>\n<\/ol>\n

    Summary<\/h2>\n

    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.<\/p>\n

    Code<\/h2>\n
    function insertValuesToDynamicRange() {\n  const SS = SpreadsheetApp.getActiveSpreadsheet()\n  const T_SHEET2 = SS.getSheetByName('Sheet2')\n  const T_SHEET4 = SS.getSheetByName('Sheet4')\n\n  const values = T_SHEET2.getDataRange().getValues()  \n\n  let cell = T_SHEET4.getRange(1, 1, values.length, values[0].length)\n  cell.setValues(values)\n}<\/code><\/pre>\n
    Code Explanation<\/h2>\n
    The code defines a function named insertValuesToDynamicRange<\/code>. It retrieves the active spreadsheet and assigns it to the SS<\/code> variable. It then gets the sheets named ‘Sheet2’ and ‘Sheet4’ and assigns them to the T_SHEET2<\/code> and T_SHEET4<\/code> variables, respectively.<\/p>\n
    The getDataRange<\/code> method is used to retrieve the range of data in T_SHEET2<\/code>. The values in the range are retrieved using the getValues<\/code> method and stored in the values<\/code> variable.<\/p>\n
    A Range<\/code> object is created in T_SHEET4<\/code> using the getRange<\/code> method, specifying the starting row and column, as well as the number of rows and columns based on the size of the values<\/code> array.<\/p>\n
    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. <\/span><\/p>\n
    1st argument: starting row number of data range (starting from 1)
    \n2nd argument: starting column number of data range (starting from 1)
    \n3rd argument: number of rows of data range (row length of values array)
    \n4th argument: Number of columns in the data range (column length of the first element (row) of the values array)<\/span><\/p>\n
    Finally, the setValues<\/code> method is used to set the retrieved values in the target range.<\/p>\n
    Example<\/h2>\n
    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.<\/p>\n
    When we execute the insertValuesToDynamicRange<\/code> 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.<\/p>\n
    AI Prompt<\/h2>\n
    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.<\/p>\n","protected":false},"excerpt":{"rendered":"
    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.<\/p>\n","protected":false},"author":1,"featured_media":15563,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_kad_post_transparent":"","_kad_post_title":"","_kad_post_layout":"","_kad_post_sidebar_id":"","_kad_post_content_style":"","_kad_post_vertical_padding":"","_kad_post_feature":"","_kad_post_feature_position":"","_kad_post_header":false,"_kad_post_footer":false,"footnotes":""},"categories":[5,118],"tags":[512,69,524,7],"class_list":["post-15555","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-spreadsheet","category-apps-script","tag-ai-assisted","tag-script","tag-getrange","tag-spreadsheet"],"translation":{"provider":"WPGlobus","version":"3.0.2","language":"en","enabled_languages":["ko","en"],"languages":{"ko":{"title":true,"content":true,"excerpt":true},"en":{"title":true,"content":true,"excerpt":true}}},"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.autooffice.co.kr\/en\/wp-json\/wp\/v2\/posts\/15555","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.autooffice.co.kr\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.autooffice.co.kr\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.autooffice.co.kr\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.autooffice.co.kr\/en\/wp-json\/wp\/v2\/comments?post=15555"}],"version-history":[{"count":3,"href":"https:\/\/www.autooffice.co.kr\/en\/wp-json\/wp\/v2\/posts\/15555\/revisions"}],"predecessor-version":[{"id":17194,"href":"https:\/\/www.autooffice.co.kr\/en\/wp-json\/wp\/v2\/posts\/15555\/revisions\/17194"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.autooffice.co.kr\/en\/wp-json\/wp\/v2\/media\/15563"}],"wp:attachment":[{"href":"https:\/\/www.autooffice.co.kr\/en\/wp-json\/wp\/v2\/media?parent=15555"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.autooffice.co.kr\/en\/wp-json\/wp\/v2\/categories?post=15555"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.autooffice.co.kr\/en\/wp-json\/wp\/v2\/tags?post=15555"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}