{"id":15524,"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-google-sheets-using-apps-script\/"},"modified":"2026-08-21T16:53:03","modified_gmt":"2026-08-21T07:53:03","slug":"inserting-values-into-google-sheets-using-apps-script","status":"publish","type":"post","link":"https:\/\/www.autooffice.co.kr\/en\/blog\/2023\/08\/05\/inserting-values-into-google-sheets-using-apps-script\/","title":{"rendered":"Inserting values repeatedly 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 insert values repeatedly<\/span> into a Google Sheets using Apps Script. We will explore a code snippet that inserts values into specific cells of a sheet and also provide an example demonstrating its usage.<\/p>\n

    Code<\/h2>\n
    function insertValues() {\n  const SS = SpreadsheetApp.getActiveSpreadsheet()\n  const sheet = SS.getSheetByName('Sheet1')\n\n  const values = [1, 'Google Sheets', true, new Date(), '=LEN(B2)', '=query(\\'Sheet2\\'!A1:E4,\"select *\")']\n  const ranges = ['B1', 'B2', 'B3', 'B4', 'B5', 'B6']\n\n  const valuesLabel = ['Number', 'String', 'Boolean', 'Current Date', 'Length of B2 Cell String', 'Query Function to Get Sheet2 A1:E4']\n  const rangesLabel = ['A1', 'A2', 'A3', 'A4', 'A5', 'A6']\n\n  for (i = 0; i < values.length; i++) {\n    let cellLabel = sheet.getRange(rangesLabel[i])\n    cellLabel.setValue(valuesLabel[i])\n\n    let cell = sheet.getRange(ranges[i])\n    cell.setValue(values[i])\n  }\n}<\/code><\/pre>\n
    Code Explanation<\/h2>\n
    The code defines a function named insertValues<\/code>. It retrieves the active spreadsheet and the sheet named ‘Sheet1’ to work with.<\/p>\n
    Two arrays, values<\/code> and ranges<\/code>, are declared to store the values to be inserted and the corresponding cell ranges. Another two arrays, valuesLabel<\/code> and rangesLabel<\/code>, store labels for each value and cell range.<\/p>\n
    A loop iterates through the arrays, setting the labels and values in their respective cells using the setValue<\/code> method of the Range<\/code> class.<\/p>\n
    Example<\/h2>\n
    When you run the insertValues<\/code> function, it will insert the following values into the specified cells:<\/p>\n
      \n
    • Number: 1 (cell B1)<\/li>\n
    • String: “Google Sheets” (cell B2)<\/li>\n
    • Boolean: true (cell B3)<\/li>\n
    • Current Date: current date (cell B4)<\/li>\n
    • Length of B2 Cell String: length of the string in cell B2 (cell B5)<\/li>\n
    • Query Function to Get Sheet2 A1:E4: results of the query function on Sheet2 (cell B6)<\/li>\n<\/ul>\n
      AI Prompt<\/h2>\n
      Write a function that inserts values into specific cells of a Google Sheets using Apps Script. The function should use the provided arrays to set labels and values in their respective cells.<\/s><\/p>\n
      When you have an array like the one below, write an Apps Script function that correctly inserts the labels and values using loops.<\/span><\/p>\n
      \n  const values = [1, '\uad6c\uae00 \uc2dc\ud2b8', true, new Date(), '=LEN(B2)', '=query(\\'\uc2dc\ud2b82\\'!A1:E4,\"select *\")']\n  const ranges = ['B1', 'B2', 'B3', 'B4', 'B5', 'B6']\n\n  const valuesLabel = ['\uc22b\uc790', '\ubb38\uc790\uc5f4', '\ubd88\ub9ac\uc5b8', '\ud604\uc7ac \ub0a0\uc9dc', 'B2\uc140 \ubb38\uc790\uc5f4\uc758 \uae38\uc774 \ud568\uc218', '\uc2dc\ud2b82\uc758 A1:E4 \uac00\uc838\uc624\uae30 \ucffc\ub9ac\ud568\uc218']\n  const rangesLabel = ['A1', 'A2', 'A3', 'A4', 'A5', 'A6']\n<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"
      In this blog post, we will learn how to insert values into a Google Sheets using Apps Script. We will explore a code snippet that inserts values into specific cells of a sheet and also provide an example demonstrating its usage.<\/p>\n","protected":false},"author":1,"featured_media":15532,"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,527,535,7],"class_list":["post-15524","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-apps-script","tag-ai-assisted","tag-apps-script","tag-for","tag-setvalue","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