{"id":15525,"date":"2023-08-05T09:00:00","date_gmt":"2023-08-05T00:00:00","guid":{"rendered":"https:\/\/www.autooffice.co.kr\/blog\/2023\/08\/05\/inserting-values-into-array-row-in-google-sheets-using-apps-script\/"},"modified":"2026-08-21T16:53:03","modified_gmt":"2026-08-21T07:53:03","slug":"inserting-values-into-array-row-in-google-sheets-using-apps-script","status":"publish","type":"post","link":"https:\/\/www.autooffice.co.kr\/en\/blog\/2023\/08\/05\/inserting-values-into-array-row-in-google-sheets-using-apps-script\/","title":{"rendered":"Inserting Values into Array Row 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 explore how to insert values into an array row in Google Sheets using Apps Script. We will learn how to use the setValues<\/code> method to insert an array of values into a specified range in a sheet.<\/p>\n

    Code<\/h2>\n
    function insertValuesArrayRow() {\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:F1'\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 insertValuesArrayRow<\/code>. It first gets the active spreadsheet and then retrieves a specific sheet named ‘Sheet3’.<\/p>\n
    An array of values is created and assigned to the variable values<\/code>. This array contains multiple values of different data types.<\/p>\n
    Here, the array must be 2-dimensional.<\/span><\/p>\n
    The range A1:F1<\/code> is specified as the target range where the values will be inserted.<\/p>\n
    A range object is obtained using getRange<\/code> method, and then the setValues<\/code> method is used to insert the values into the specified range.<\/p>\n
    Example<\/h2>\n
    Let’s say you have a Google Sheet with the following structure:<\/p>\n\n\n\n\n
    A<\/th>\nB<\/th>\nC<\/th>\nD<\/th>\nE<\/th>\nF<\/th>\n<\/tr>\n
    <\/td>\n<\/td>\n<\/td>\n<\/td>\n<\/td>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    You can use the insertValuesArrayRow<\/code> function to insert an array of values into the first row of the sheet:<\/p>\n
    insertValuesArrayRow();<\/code><\/pre>\n
    After running the function, the first row of the sheet will be populated with the specified values:<\/p>\n\n\n\n\n
    A<\/th>\nB<\/th>\nC<\/th>\nD<\/th>\nE<\/th>\nF<\/th>\n<\/tr>\n
    1<\/td>\nGoogle Sheets<\/td>\ntrue<\/td>\nCurrent Date<\/td>\n=LEN(B2)<\/td>\n=query(‘Sheet2’!A1:E4,”select *”)<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    AI Prompt<\/h2>\n
    Write a function that inserts an array of values into the first row of a Google Sheet. Use the setValues<\/code> method and specify the range as A1:F1<\/code>. The array of values should contain a number, a string, a boolean, a date, a formula, and a query function.<\/p>\n","protected":false},"excerpt":{"rendered":"
    In this blog post, we will explore how to insert values into an array row in Google Sheets using Apps Script. We will learn how to use the setValues method to insert an array of values into a specified range in a sheet.<\/p>\n","protected":false},"author":1,"featured_media":15537,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_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":[118],"tags":[512,355,536,7],"class_list":["post-15525","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-apps-script","tag-ai-assisted","tag-apps-script","tag-setvalues","tag-spreadsheet"],"translation":{"provider":"WPGlobus","version":"3.0.5","language":"en","enabled_languages":["ko","en"],"languages":{"ko":{"title":true,"content":true,"excerpt":true},"en":{"title":true,"content":true,"excerpt":true}}},"aioseo_notices":[],"aioseo_head":"\n\t\t\n\t\n\t\n\t\n\t\n\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t