{"id":15526,"date":"2023-08-05T09:00:00","date_gmt":"2023-08-05T00:00:00","guid":{"rendered":"https:\/\/www.autooffice.co.kr\/blog\/2023\/08\/05\/insert-values-into-array-column-in-google-sheets-using-apps-script\/"},"modified":"2025-09-15T18:46:04","modified_gmt":"2025-09-15T09:46:04","slug":"insert-values-into-array-column-in-google-sheets-using-apps-script","status":"publish","type":"post","link":"https:\/\/www.autooffice.co.kr\/en\/blog\/2023\/08\/05\/insert-values-into-array-column-in-google-sheets-using-apps-script\/","title":{"rendered":"Insert Values into Column in Google Sheets using 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 learn how to use Apps Script to insert array <\/span>values into an array<\/s> 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.<\/p>\n

    Code<\/h2>\n
    function insertValuesArrayCol() {\n    const SS = SpreadsheetApp.getActiveSpreadsheet()\n    const sheet = SS.getSheetByName('Sheet3')\n\n    const values = [[1], ['Google Sheets'], [true], [new Date()], ['=LEN(B2)'], ['=query(\\'Sheet2\\'!A1:E4,\"select *\")']]\n    const ranges = 'A1:A6'\n\n    let cell = sheet.getRange(ranges)\n    cell.setValues(values)\n}<\/code><\/pre>\n
    Code Explanation<\/h2>\n
    The code defines a function named insertValuesArrayCol<\/code>. It retrieves the active spreadsheet and the sheet named ‘Sheet3’ using the SpreadsheetApp<\/code> class.<\/p>\n
    An array of values is created and assigned to the values<\/code> variable. This array contains various data types, including numbers, strings, booleans, dates, and a formula referencing another cell. The ranges<\/code> variable specifies the range where the values will be inserted, in this case, column A and rows 1 to 6.<\/p>\n
    The getRange<\/code> method is used to get the range specified by the ranges<\/code> variable, and the setValues<\/code> method is used to insert the values into the range.<\/p>\n
    Example<\/h2>\n
    Suppose we have a Google Sheet with a column A labeled ‘Data’. By executing the insertValuesArrayCol<\/code> function, the following values will be inserted into column A:<\/p>\n
    1\nGoogle Sheets\nTRUE\n[date]\n=LEN(B2)\n=query('Sheet2'!A1:E4,\"select *\")<\/code><\/pre>\n
    AI Prompt<\/h2>\n
    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’.<\/p>\n
    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.<\/span><\/p>\n
    (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.)<\/span><\/p>\n","protected":false},"excerpt":{"rendered":"
    In this blog post, we will learn how to use Apps Script to insert values into an array 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.<\/p>\n","protected":false},"author":1,"featured_media":15541,"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,536,7],"class_list":["post-15526","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-spreadsheet","category-apps-script","tag-ai-assisted","tag-script","tag-setvalues","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\/15526","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=15526"}],"version-history":[{"count":3,"href":"https:\/\/www.autooffice.co.kr\/en\/wp-json\/wp\/v2\/posts\/15526\/revisions"}],"predecessor-version":[{"id":17236,"href":"https:\/\/www.autooffice.co.kr\/en\/wp-json\/wp\/v2\/posts\/15526\/revisions\/17236"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.autooffice.co.kr\/en\/wp-json\/wp\/v2\/media\/15541"}],"wp:attachment":[{"href":"https:\/\/www.autooffice.co.kr\/en\/wp-json\/wp\/v2\/media?parent=15526"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.autooffice.co.kr\/en\/wp-json\/wp\/v2\/categories?post=15526"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.autooffice.co.kr\/en\/wp-json\/wp\/v2\/tags?post=15526"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}