{"id":16198,"date":"2024-11-23T10:45:59","date_gmt":"2024-11-23T01:45:59","guid":{"rendered":"https:\/\/84office.jp\/?p=16198"},"modified":"2024-11-23T10:46:00","modified_gmt":"2024-11-23T01:46:00","slug":"chatgpt-spreadsheet","status":"publish","type":"post","link":"https:\/\/84office.jp\/en\/chatgpt-spreadsheet\/","title":{"rendered":"Link ChatGPT with Google Sheets: Boost Efficiency Using Google Apps Script and ChatGPT API"},"content":{"rendered":"<div class=\"codoc-evacuations\" style=\"display:none;\" data-shortcode=\"\"><\/div>\n<p>If you want to integrate ChatGPT and spreadsheets to make your work more efficient, but don&#8217;t know how to do it, this article will show you the basic way to integrate ChatGPT and Google Sheets.<\/p>\n\n\n\n<p class=\"u-mb-ctrl u-mb-40\">By utilizing Google Apps Script and the ChatGPT API, you can batch process questions and tasks on a spreadsheet and get real-time answers from ChatGPT.<\/p>\n\n\n<div class=\"swell-block-postLink u-mb-ctrl u-mb-60\"><\/div>\n\n\n<h2 class=\"wp-block-heading u-mb-ctrl u-mb-30\">Linking ChatGPT to Spreadsheets<\/h2>\n\n\n\n<ul class=\"wp-block-list has-border -border02 u-mb-ctrl u-mb-40 has-swl-pale-03-background-color has-background\">\n<li>Exchanging data via API<\/li>\n\n\n\n<li>Using Google Apps Script<\/li>\n\n\n\n<li>Fees are charged for API use.<\/li>\n<\/ul>\n\n\n\n<p>The integration between ChatGPT and Google Sheets is done using an API (Application Programming Interface).<\/p>\n\n\n\n<p>An API is an interface that allows two different systems to exchange data.<\/p>\n\n\n\n<p>Through Google Apps Script, a prompt is sent from the spreadsheet to ChatGPT and the response obtained through the API is displayed in the google sheets.<\/p>\n\n\n\n<p>The ChatGPT API has usage and cost limitations.\uff08<a href=\"https:\/\/openai.com\/chatgpt\/pricing\/\">OpenAI Rates Page<\/a>\uff09<\/p>\n\n\n\n<p class=\"u-mb-ctrl u-mb-60\">Be cautious, as usage fees may apply if the frequency of API requests or the amount of data increases.<\/p>\n\n\n\n<h2 class=\"wp-block-heading u-mb-ctrl u-mb-30\">Integration Steps<\/h2>\n\n\n\n<p>Here are the steps to integrate Google Sheets with ChatGPT.<\/p>\n\n\n\n<h3 class=\"wp-block-heading u-mb-ctrl u-mb-30 has-swl-pale-02-background-color has-background\">Obtaining the API Key<\/h3>\n\n\n\n<div class=\"swell-block-step u-mb-ctrl u-mb-60\" data-num-style=\"circle\">\n<div class=\"swell-block-step__item\"><div class=\"swell-block-step__number u-bg-main\"><span class=\"__label\">STEP<\/span><\/div><div class=\"swell-block-step__title u-fz-l\">Visit the OpenAI website<\/div><div class=\"swell-block-step__body\">\n<figure class=\"wp-block-image size-large is-resized u-mb-ctrl u-mb-40\"><img decoding=\"async\" width=\"1024\" height=\"361\" src=\"https:\/\/84office.jp\/wp-content\/uploads\/2024\/10\/cac903d5f889b75f49350333d5059374-e1728056808873-1024x361.png\" alt=\"\" class=\"wp-image-14821\" style=\"width:788px;height:auto\" srcset=\"https:\/\/84office.jp\/wp-content\/uploads\/2024\/10\/cac903d5f889b75f49350333d5059374-e1728056808873-1024x361.png 1024w, https:\/\/84office.jp\/wp-content\/uploads\/2024\/10\/cac903d5f889b75f49350333d5059374-e1728056808873-300x106.png 300w, https:\/\/84office.jp\/wp-content\/uploads\/2024\/10\/cac903d5f889b75f49350333d5059374-e1728056808873-768x271.png 768w, https:\/\/84office.jp\/wp-content\/uploads\/2024\/10\/cac903d5f889b75f49350333d5059374-e1728056808873.png 1283w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p>Access the <a href=\"https:\/\/platform.openai.com\/docs\/overview\" target=\"_blank\" rel=\"noreferrer noopener\">OpenAI platform<\/a>.<\/p>\n\n\n\n<p>If you already have an account, click &#8220;Log In&#8221; to sign in.<\/p>\n\n\n\n<p class=\"u-mb-ctrl u-mb-30\">If you don&#8217;t have an account yet, click &#8220;Sign Up&#8221; at the top right of the screen to register.<\/p>\n<\/div><\/div>\n\n\n\n<div class=\"swell-block-step__item\"><div class=\"swell-block-step__number u-bg-main\"><span class=\"__label\">STEP<\/span><\/div><div class=\"swell-block-step__title u-fz-l\"><strong>Open the Dashboard<\/strong><\/div><div class=\"swell-block-step__body\">\n<figure class=\"wp-block-image size-large u-mb-ctrl u-mb-40\"><img decoding=\"async\" width=\"1024\" height=\"382\" src=\"https:\/\/84office.jp\/wp-content\/uploads\/2024\/10\/941fa8040e650bdb03f473578426768e-e1728056909227-1024x382.png\" alt=\"\" class=\"wp-image-14822\" srcset=\"https:\/\/84office.jp\/wp-content\/uploads\/2024\/10\/941fa8040e650bdb03f473578426768e-e1728056909227-1024x382.png 1024w, https:\/\/84office.jp\/wp-content\/uploads\/2024\/10\/941fa8040e650bdb03f473578426768e-e1728056909227-300x112.png 300w, https:\/\/84office.jp\/wp-content\/uploads\/2024\/10\/941fa8040e650bdb03f473578426768e-e1728056909227-768x287.png 768w, https:\/\/84office.jp\/wp-content\/uploads\/2024\/10\/941fa8040e650bdb03f473578426768e-e1728056909227.png 1203w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p>After logging in, navigate to the OpenAI dashboard.<\/p>\n\n\n\n<p class=\"u-mb-ctrl u-mb-30\">Open the &#8220;Dashboard&#8221; at the top right of the screen.<\/p>\n<\/div><\/div>\n\n\n\n<div class=\"swell-block-step__item\"><div class=\"swell-block-step__number u-bg-main\"><span class=\"__label\">STEP<\/span><\/div><div class=\"swell-block-step__title u-fz-l\">Open &#8220;API keys&#8221;<\/div><div class=\"swell-block-step__body\"><div class=\"wp-block-image u-mb-ctrl u-mb-40\">\n<figure class=\"alignleft size-full is-resized\"><img decoding=\"async\" width=\"413\" height=\"466\" src=\"https:\/\/84office.jp\/wp-content\/uploads\/2024\/10\/173ea90bc2dc15d53557a943254d386d-1.png\" alt=\"\" class=\"wp-image-14830\" style=\"width:427px;height:auto\" srcset=\"https:\/\/84office.jp\/wp-content\/uploads\/2024\/10\/173ea90bc2dc15d53557a943254d386d-1.png 413w, https:\/\/84office.jp\/wp-content\/uploads\/2024\/10\/173ea90bc2dc15d53557a943254d386d-1-266x300.png 266w\" sizes=\"(max-width: 413px) 100vw, 413px\" \/><\/figure>\n<\/div>\n\n\n<p class=\"u-mb-ctrl u-mb-30\">Open \u201cAPI keys\u201d on the left side of the screen.<\/p>\n<\/div><\/div>\n\n\n\n<div class=\"swell-block-step__item\"><div class=\"swell-block-step__number u-bg-main\"><span class=\"__label\">STEP<\/span><\/div><div class=\"swell-block-step__title u-fz-l\"><strong>Open &#8220;Create new secret key.&#8221;<\/strong><\/div><div class=\"swell-block-step__body\">\n<figure class=\"wp-block-image size-large is-resized u-mb-ctrl u-mb-40\"><img decoding=\"async\" width=\"1024\" height=\"417\" src=\"https:\/\/84office.jp\/wp-content\/uploads\/2024\/10\/e3cd6cb729316fbda3f72dda55c7213c-1-1024x417.png\" alt=\"\" class=\"wp-image-14831\" style=\"width:788px;height:auto\" srcset=\"https:\/\/84office.jp\/wp-content\/uploads\/2024\/10\/e3cd6cb729316fbda3f72dda55c7213c-1-1024x417.png 1024w, https:\/\/84office.jp\/wp-content\/uploads\/2024\/10\/e3cd6cb729316fbda3f72dda55c7213c-1-300x122.png 300w, https:\/\/84office.jp\/wp-content\/uploads\/2024\/10\/e3cd6cb729316fbda3f72dda55c7213c-1-768x313.png 768w, https:\/\/84office.jp\/wp-content\/uploads\/2024\/10\/e3cd6cb729316fbda3f72dda55c7213c-1.png 1071w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p class=\"u-mb-ctrl u-mb-30\">Open \u201c<strong>Create new secret key<\/strong>\u201d in the center of the screen<\/p>\n<\/div><\/div>\n\n\n\n<div class=\"swell-block-step__item\"><div class=\"swell-block-step__number u-bg-main\"><span class=\"__label\">STEP<\/span><\/div><div class=\"swell-block-step__title u-fz-l\">Enter the name of the API key<\/div><div class=\"swell-block-step__body\"><div class=\"wp-block-image u-mb-ctrl u-mb-40\">\n<figure class=\"alignleft size-full\"><img decoding=\"async\" width=\"499\" height=\"452\" src=\"https:\/\/84office.jp\/wp-content\/uploads\/2024\/10\/image-8.png\" alt=\"\" class=\"wp-image-14826\" srcset=\"https:\/\/84office.jp\/wp-content\/uploads\/2024\/10\/image-8.png 499w, https:\/\/84office.jp\/wp-content\/uploads\/2024\/10\/image-8-300x272.png 300w\" sizes=\"(max-width: 499px) 100vw, 499px\" \/><\/figure>\n<\/div>\n\n\n<p>Enter a name for the API key.<\/p>\n\n\n\n<p class=\"u-mb-ctrl u-mb-30\">Then press \u201c<strong>Create secret key<\/strong>\u201d.<\/p>\n<\/div><\/div>\n\n\n\n<div class=\"swell-block-step__item\"><div class=\"swell-block-step__number u-bg-main\"><span class=\"__label\">STEP<\/span><\/div><div class=\"swell-block-step__title u-fz-l\">Copy and save the API key<\/div><div class=\"swell-block-step__body\"><div class=\"wp-block-image u-mb-ctrl u-mb-30\">\n<figure class=\"alignleft size-full\"><img decoding=\"async\" width=\"504\" height=\"315\" src=\"https:\/\/84office.jp\/wp-content\/uploads\/2024\/10\/15b03199265a968eaa05bcdddcf35f8c.png\" alt=\"\" class=\"wp-image-14827\" srcset=\"https:\/\/84office.jp\/wp-content\/uploads\/2024\/10\/15b03199265a968eaa05bcdddcf35f8c.png 504w, https:\/\/84office.jp\/wp-content\/uploads\/2024\/10\/15b03199265a968eaa05bcdddcf35f8c-300x188.png 300w\" sizes=\"(max-width: 504px) 100vw, 504px\" \/><\/figure>\n<\/div>\n\n\n<p>The API key created is displayed.<\/p>\n\n\n\n<p class=\"u-mb-ctrl u-mb-40\">This key appears only once, so copy it and save it in a safe place.<\/p>\n\n\n\n<div class=\"swell-block-capbox cap_box is-style-onborder_ttl2 u-mb-ctrl u-mb-40\" data-colset=\"col2\"><div class=\"cap_box_ttl\"><span><span class=\"swl-fz u-fz-l\"><span style=\"--the-icon-svg: url(data:image\/svg+xml;base64,PHN2ZyBoZWlnaHQ9IjFlbSIgd2lkdGg9IjFlbSIgeG1sbnM9Imh0dHA6Ly93d3cudzMub3JnLzIwMDAvc3ZnIiBhcmlhLWhpZGRlbj0idHJ1ZSIgdmlld0JveD0iMCAwIDQ4IDQ4Ij48cGF0aCBkPSJNNDMuNSA0My45aC0zOWMtMS40IDAtMi43LS43LTMuNS0yLS43LTEuMy0uNy0yLjcgMC00TDIwLjUgNC4xYy43LTEuMyAyLTIgMy41LTJzMi43LjcgMy41IDJMNDcgMzcuOWMuNyAxLjMuNyAyLjcgMCA0LS43IDEuMy0yIDItMy41IDJ6TTI0IDYuMSA0LjUgMzkuOWgzOS4xTDI0IDYuMXoiPjwvcGF0aD48Y2lyY2xlIGN4PSIyNCIgY3k9IjM1IiByPSIzIj48L2NpcmNsZT48cGF0aCBkPSJNMjQgMzBjLTEuNCAwLTIuNS0xLjEtMi41LTIuNXYtMTBjMC0xLjQgMS4xLTIuNSAyLjUtMi41czIuNSAxLjEgMi41IDIuNXYxMGMwIDEuNC0xLjEgMi41LTIuNSAyLjV6Ij48L3BhdGg+PC9zdmc+)\" data-icon=\"LsAlertOutline\" data-id=\"0\" aria-hidden=\"true\" class=\"swl-inline-icon\">\u2003<\/span> Managing API Keys<\/span><\/span><\/div><div class=\"cap_box_content\">\n<ul class=\"wp-block-list\">\n<li>If you lose your API key, you cannot reacquire the same one.<br>However, you can create a new key.<br><\/li>\n\n\n\n<li>Be careful not to share or disclose API keys.<br>If someone uses that API key illegally, it can be costly.<\/li>\n<\/ul>\n<\/div><\/div>\n\n\n\n<p class=\"u-mb-ctrl u-mb-40 is-style-icon_pen\">Usage Limitations and Fees<br><a href=\"https:\/\/openai.com\/chatgpt\/pricing\/\" target=\"_blank\" rel=\"noreferrer noopener\">Visit OpenAI&#8217;s rates page<\/a> to learn more about usage and costs.<\/p>\n\n\n\n<p>Now you have your API key and are ready to use it.<\/p>\n<\/div><\/div>\n<\/div>\n\n\n\n<h3 class=\"wp-block-heading u-mb-ctrl u-mb-30 has-swl-pale-02-background-color has-background\">Create and run scripts<\/h3>\n\n\n\n<p id=\"block-9cfa1c3b-78d7-497c-a269-30ce14795307\">Next, use Google Apps Script to create a script to link the Google Sheets and ChatGPT.<\/p>\n\n\n\n<p id=\"block-9cfa1c3b-78d7-497c-a269-30ce14795307\">Here is how to save and use ChatGPT API keys in script properties.<\/p>\n\n\n\n<p class=\"u-mb-ctrl u-mb-40\" id=\"block-9cfa1c3b-78d7-497c-a269-30ce14795307\">Script properties allow you to manage scripts while maintaining security by not writing API keys directly in the code.<\/p>\n\n\n\n<div class=\"swell-block-step u-mb-ctrl u-mb-40\" data-num-style=\"circle\">\n<div class=\"swell-block-step__item\"><div class=\"swell-block-step__number u-bg-main\"><span class=\"__label\">STEP<\/span><\/div><div class=\"swell-block-step__title u-fz-l\"><strong>Open the Apps Script Editor<\/strong><\/div><div class=\"swell-block-step__body\"><div class=\"wp-block-image u-mb-ctrl u-mb-40\">\n<figure class=\"alignleft size-full is-resized\"><img decoding=\"async\" width=\"828\" height=\"638\" src=\"https:\/\/84office.jp\/wp-content\/uploads\/2024\/11\/49eb48b95d4c4504148a0386b7cb3e2b.jpg\" alt=\"\" class=\"wp-image-15967\" style=\"width:483px;height:auto\" srcset=\"https:\/\/84office.jp\/wp-content\/uploads\/2024\/11\/49eb48b95d4c4504148a0386b7cb3e2b.jpg 828w, https:\/\/84office.jp\/wp-content\/uploads\/2024\/11\/49eb48b95d4c4504148a0386b7cb3e2b-300x231.jpg 300w, https:\/\/84office.jp\/wp-content\/uploads\/2024\/11\/49eb48b95d4c4504148a0386b7cb3e2b-768x592.jpg 768w\" sizes=\"(max-width: 828px) 100vw, 828px\" \/><\/figure>\n<\/div>\n\n\n<p>Open Google Sheets, then click on \u201c<strong>Extensions<\/strong>\u201d in the menu and select \u201c<strong>Apps Script<\/strong>\u201d to open the editor.<\/p>\n<\/div><\/div>\n\n\n\n<div class=\"swell-block-step__item\"><div class=\"swell-block-step__number u-bg-main\"><span class=\"__label\">STEP<\/span><\/div><div class=\"swell-block-step__title u-fz-l\">Store API keys in script properties<\/div><div class=\"swell-block-step__body\"><div class=\"wp-block-image u-mb-ctrl u-mb-40\">\n<figure class=\"alignleft size-full is-resized\"><img decoding=\"async\" width=\"862\" height=\"628\" src=\"https:\/\/84office.jp\/wp-content\/uploads\/2024\/11\/ccffce69b2b4ac34609f8fd36aa8a2b9-1.png\" alt=\"\" class=\"wp-image-16202\" style=\"width:557px;height:auto\" srcset=\"https:\/\/84office.jp\/wp-content\/uploads\/2024\/11\/ccffce69b2b4ac34609f8fd36aa8a2b9-1.png 862w, https:\/\/84office.jp\/wp-content\/uploads\/2024\/11\/ccffce69b2b4ac34609f8fd36aa8a2b9-1-300x219.png 300w, https:\/\/84office.jp\/wp-content\/uploads\/2024\/11\/ccffce69b2b4ac34609f8fd36aa8a2b9-1-768x560.png 768w\" sizes=\"(max-width: 862px) 100vw, 862px\" \/><\/figure>\n<\/div>\n\n\n<p class=\"u-mb-ctrl u-mb-50\">Once the editor is open, click on \u201c<strong>Project Settings<\/strong>\u201d from the left side menu.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large u-mb-ctrl u-mb-40\"><img decoding=\"async\" width=\"1024\" height=\"526\" src=\"https:\/\/84office.jp\/wp-content\/uploads\/2024\/11\/bcf0ec7722960445dd6cbf122f0c30ca-1024x526.png\" alt=\"\" class=\"wp-image-16205\" srcset=\"https:\/\/84office.jp\/wp-content\/uploads\/2024\/11\/bcf0ec7722960445dd6cbf122f0c30ca-1024x526.png 1024w, https:\/\/84office.jp\/wp-content\/uploads\/2024\/11\/bcf0ec7722960445dd6cbf122f0c30ca-300x154.png 300w, https:\/\/84office.jp\/wp-content\/uploads\/2024\/11\/bcf0ec7722960445dd6cbf122f0c30ca-768x395.png 768w, https:\/\/84office.jp\/wp-content\/uploads\/2024\/11\/bcf0ec7722960445dd6cbf122f0c30ca-1536x789.png 1536w, https:\/\/84office.jp\/wp-content\/uploads\/2024\/11\/bcf0ec7722960445dd6cbf122f0c30ca.png 1621w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p class=\"u-mb-ctrl u-mb-50\">Then select \u201c<strong>Add Script Property<\/strong>\u201d.<\/p>\n\n\n<div class=\"wp-block-image u-mb-ctrl u-mb-40\">\n<figure class=\"alignleft size-large\"><img decoding=\"async\" width=\"1024\" height=\"359\" src=\"https:\/\/84office.jp\/wp-content\/uploads\/2024\/11\/2326d2f11af4c97615da8ecd3086df68-1024x359.png\" alt=\"\" class=\"wp-image-16206\" srcset=\"https:\/\/84office.jp\/wp-content\/uploads\/2024\/11\/2326d2f11af4c97615da8ecd3086df68-1024x359.png 1024w, https:\/\/84office.jp\/wp-content\/uploads\/2024\/11\/2326d2f11af4c97615da8ecd3086df68-300x105.png 300w, https:\/\/84office.jp\/wp-content\/uploads\/2024\/11\/2326d2f11af4c97615da8ecd3086df68-768x269.png 768w, https:\/\/84office.jp\/wp-content\/uploads\/2024\/11\/2326d2f11af4c97615da8ecd3086df68-1536x538.png 1536w, https:\/\/84office.jp\/wp-content\/uploads\/2024\/11\/2326d2f11af4c97615da8ecd3086df68.png 1593w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n<\/div>\n\n\n<p><strong>Enter the API key in the property field and save.<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Property: Any name you like (e.g.,<strong><code>OPENAI_API_KEY<\/code><\/strong>)<\/li>\n\n\n\n<li class=\"u-mb-ctrl u-mb-30\">Value: API key obtained (e.g.,<code><strong>sk-xxxxxx<\/strong><\/code>)<\/li>\n<\/ul>\n<\/div><\/div>\n\n\n\n<div class=\"swell-block-step__item\"><div class=\"swell-block-step__number u-bg-main\"><span class=\"__label\">STEP<\/span><\/div><div class=\"swell-block-step__title u-fz-l\"><strong>Create the Script<\/strong><\/div><div class=\"swell-block-step__body\">\n<div class=\"swell-block-capbox cap_box is-style-onborder_ttl u-mb-ctrl u-mb-30\" data-colset=\"col2\"><div class=\"cap_box_ttl\"><span>Contents of this Script<\/span><\/div><div class=\"cap_box_content\">\n<p class=\"u-mb-ctrl u-mb-30\">Use the prompts<strong> entered in column A<\/strong> of the spreadsheet to display ChatGPT <strong>responses in column B<\/strong><\/p>\n<\/div><\/div>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro u-mb-ctrl u-mb-40\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span role=\"button\" tabindex=\"0\" data-code=\"function getChatGPTResponse() {\n  \/\/ Retrieve the API key from the script properties\n  var scriptProperties = PropertiesService.getScriptProperties();\n  var apiKey = scriptProperties.getProperty('OPENAI_API_KEY');\n  \n  \/\/ Get the active sheet of the spreadsheet\n  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();\n  var lastRow = sheet.getLastRow(); \/\/ Get the last row\n  \n  \/\/ Process rows from the second row to the last row in column A\n  for (var i = 2; i &lt;= lastRow; i++) { \/\/ Start from the second row\n    var prompt = sheet.getRange(i, 1).getValue(); \/\/ Get the prompt from column A\n    \n    \/\/ Send a request to the ChatGPT API if the prompt is not empty\n    if (prompt) {\n      var url = 'https:\/\/api.openai.com\/v1\/chat\/completions';\n      var options = {\n        'method': 'post',\n        'headers': {\n          'Authorization': 'Bearer ' + apiKey,\n          'Content-Type': 'application\/json'\n        },\n        'payload': JSON.stringify({\n          'model': 'gpt-3.5-turbo', \/\/ Specify the model to use\n          'messages': [{&quot;role&quot;: &quot;system&quot;, &quot;content&quot;: &quot;You are a helpful assistant.&quot;},\n                       {&quot;role&quot;: &quot;user&quot;, &quot;content&quot;: prompt}],\n          'max_tokens': 1000\n        })\n      };\n      \n      \/\/ Send a request to the ChatGPT API and retrieve the result\n      var response = UrlFetchApp.fetch(url, options);\n      var json = response.getContentText();\n      var data = JSON.parse(json);\n      \n      \/\/ Display the ChatGPT response in column B (second column) of the same row\n      sheet.getRange(i, 2).setValue(data.choices[0].message.content.trim());\n    }\n  }\n}\n\" style=\"color:#F8F8F2;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki monokai\" style=\"background-color: #272822\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #66D9EF; font-style: italic\">function<\/span><span style=\"color: #F8F8F2\"> <\/span><span style=\"color: #A6E22E\">getChatGPTResponse<\/span><span style=\"color: #F8F8F2\">() {<\/span><\/span>\n<span class=\"line\"><span style=\"color: #F8F8F2\">  <\/span><span style=\"color: #88846F\">\/\/ Retrieve the API key from the script properties<\/span><\/span>\n<span class=\"line\"><span style=\"color: #F8F8F2\">  <\/span><span style=\"color: #66D9EF; font-style: italic\">var<\/span><span style=\"color: #F8F8F2\"> scriptProperties <\/span><span style=\"color: #F92672\">=<\/span><span style=\"color: #F8F8F2\"> PropertiesService.<\/span><span style=\"color: #A6E22E\">getScriptProperties<\/span><span style=\"color: #F8F8F2\">();<\/span><\/span>\n<span class=\"line\"><span style=\"color: #F8F8F2\">  <\/span><span style=\"color: #66D9EF; font-style: italic\">var<\/span><span style=\"color: #F8F8F2\"> apiKey <\/span><span style=\"color: #F92672\">=<\/span><span style=\"color: #F8F8F2\"> scriptProperties.<\/span><span style=\"color: #A6E22E\">getProperty<\/span><span style=\"color: #F8F8F2\">(<\/span><span style=\"color: #E6DB74\">&#39;OPENAI_API_KEY&#39;<\/span><span style=\"color: #F8F8F2\">);<\/span><\/span>\n<span class=\"line\"><span style=\"color: #F8F8F2\">  <\/span><\/span>\n<span class=\"line\"><span style=\"color: #F8F8F2\">  <\/span><span style=\"color: #88846F\">\/\/ Get the active sheet of the spreadsheet<\/span><\/span>\n<span class=\"line\"><span style=\"color: #F8F8F2\">  <\/span><span style=\"color: #66D9EF; font-style: italic\">var<\/span><span style=\"color: #F8F8F2\"> sheet <\/span><span style=\"color: #F92672\">=<\/span><span style=\"color: #F8F8F2\"> SpreadsheetApp.<\/span><span style=\"color: #A6E22E\">getActiveSpreadsheet<\/span><span style=\"color: #F8F8F2\">().<\/span><span style=\"color: #A6E22E\">getActiveSheet<\/span><span style=\"color: #F8F8F2\">();<\/span><\/span>\n<span class=\"line\"><span style=\"color: #F8F8F2\">  <\/span><span style=\"color: #66D9EF; font-style: italic\">var<\/span><span style=\"color: #F8F8F2\"> lastRow <\/span><span style=\"color: #F92672\">=<\/span><span style=\"color: #F8F8F2\"> sheet.<\/span><span style=\"color: #A6E22E\">getLastRow<\/span><span style=\"color: #F8F8F2\">(); <\/span><span style=\"color: #88846F\">\/\/ Get the last row<\/span><\/span>\n<span class=\"line\"><span style=\"color: #F8F8F2\">  <\/span><\/span>\n<span class=\"line\"><span style=\"color: #F8F8F2\">  <\/span><span style=\"color: #88846F\">\/\/ Process rows from the second row to the last row in column A<\/span><\/span>\n<span class=\"line\"><span style=\"color: #F8F8F2\">  <\/span><span style=\"color: #F92672\">for<\/span><span style=\"color: #F8F8F2\"> (<\/span><span style=\"color: #66D9EF; font-style: italic\">var<\/span><span style=\"color: #F8F8F2\"> i <\/span><span style=\"color: #F92672\">=<\/span><span style=\"color: #F8F8F2\"> <\/span><span style=\"color: #AE81FF\">2<\/span><span style=\"color: #F8F8F2\">; i <\/span><span style=\"color: #F92672\">&lt;=<\/span><span style=\"color: #F8F8F2\"> lastRow; i<\/span><span style=\"color: #F92672\">++<\/span><span style=\"color: #F8F8F2\">) { <\/span><span style=\"color: #88846F\">\/\/ Start from the second row<\/span><\/span>\n<span class=\"line\"><span style=\"color: #F8F8F2\">    <\/span><span style=\"color: #66D9EF; font-style: italic\">var<\/span><span style=\"color: #F8F8F2\"> prompt <\/span><span style=\"color: #F92672\">=<\/span><span style=\"color: #F8F8F2\"> sheet.<\/span><span style=\"color: #A6E22E\">getRange<\/span><span style=\"color: #F8F8F2\">(i, <\/span><span style=\"color: #AE81FF\">1<\/span><span style=\"color: #F8F8F2\">).<\/span><span style=\"color: #A6E22E\">getValue<\/span><span style=\"color: #F8F8F2\">(); <\/span><span style=\"color: #88846F\">\/\/ Get the prompt from column A<\/span><\/span>\n<span class=\"line\"><span style=\"color: #F8F8F2\">    <\/span><\/span>\n<span class=\"line\"><span style=\"color: #F8F8F2\">    <\/span><span style=\"color: #88846F\">\/\/ Send a request to the ChatGPT API if the prompt is not empty<\/span><\/span>\n<span class=\"line\"><span style=\"color: #F8F8F2\">    <\/span><span style=\"color: #F92672\">if<\/span><span style=\"color: #F8F8F2\"> (prompt) {<\/span><\/span>\n<span class=\"line\"><span style=\"color: #F8F8F2\">      <\/span><span style=\"color: #66D9EF; font-style: italic\">var<\/span><span style=\"color: #F8F8F2\"> url <\/span><span style=\"color: #F92672\">=<\/span><span style=\"color: #F8F8F2\"> <\/span><span style=\"color: #E6DB74\">&#39;https:\/\/api.openai.com\/v1\/chat\/completions&#39;<\/span><span style=\"color: #F8F8F2\">;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #F8F8F2\">      <\/span><span style=\"color: #66D9EF; font-style: italic\">var<\/span><span style=\"color: #F8F8F2\"> options <\/span><span style=\"color: #F92672\">=<\/span><span style=\"color: #F8F8F2\"> {<\/span><\/span>\n<span class=\"line\"><span style=\"color: #F8F8F2\">        <\/span><span style=\"color: #E6DB74\">&#39;method&#39;<\/span><span style=\"color: #F8F8F2\">: <\/span><span style=\"color: #E6DB74\">&#39;post&#39;<\/span><span style=\"color: #F8F8F2\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #F8F8F2\">        <\/span><span style=\"color: #E6DB74\">&#39;headers&#39;<\/span><span style=\"color: #F8F8F2\">: {<\/span><\/span>\n<span class=\"line\"><span style=\"color: #F8F8F2\">          <\/span><span style=\"color: #E6DB74\">&#39;Authorization&#39;<\/span><span style=\"color: #F8F8F2\">: <\/span><span style=\"color: #E6DB74\">&#39;Bearer &#39;<\/span><span style=\"color: #F8F8F2\"> <\/span><span style=\"color: #F92672\">+<\/span><span style=\"color: #F8F8F2\"> apiKey,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #F8F8F2\">          <\/span><span style=\"color: #E6DB74\">&#39;Content-Type&#39;<\/span><span style=\"color: #F8F8F2\">: <\/span><span style=\"color: #E6DB74\">&#39;application\/json&#39;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #F8F8F2\">        },<\/span><\/span>\n<span class=\"line\"><span style=\"color: #F8F8F2\">        <\/span><span style=\"color: #E6DB74\">&#39;payload&#39;<\/span><span style=\"color: #F8F8F2\">: JSON.<\/span><span style=\"color: #A6E22E\">stringify<\/span><span style=\"color: #F8F8F2\">({<\/span><\/span>\n<span class=\"line\"><span style=\"color: #F8F8F2\">          <\/span><span style=\"color: #E6DB74\">&#39;model&#39;<\/span><span style=\"color: #F8F8F2\">: <\/span><span style=\"color: #E6DB74\">&#39;gpt-3.5-turbo&#39;<\/span><span style=\"color: #F8F8F2\">, <\/span><span style=\"color: #88846F\">\/\/ Specify the model to use<\/span><\/span>\n<span class=\"line\"><span style=\"color: #F8F8F2\">          <\/span><span style=\"color: #E6DB74\">&#39;messages&#39;<\/span><span style=\"color: #F8F8F2\">: [{<\/span><span style=\"color: #E6DB74\">&quot;role&quot;<\/span><span style=\"color: #F8F8F2\">: <\/span><span style=\"color: #E6DB74\">&quot;system&quot;<\/span><span style=\"color: #F8F8F2\">, <\/span><span style=\"color: #E6DB74\">&quot;content&quot;<\/span><span style=\"color: #F8F8F2\">: <\/span><span style=\"color: #E6DB74\">&quot;You are a helpful assistant.&quot;<\/span><span style=\"color: #F8F8F2\">},<\/span><\/span>\n<span class=\"line\"><span style=\"color: #F8F8F2\">                       {<\/span><span style=\"color: #E6DB74\">&quot;role&quot;<\/span><span style=\"color: #F8F8F2\">: <\/span><span style=\"color: #E6DB74\">&quot;user&quot;<\/span><span style=\"color: #F8F8F2\">, <\/span><span style=\"color: #E6DB74\">&quot;content&quot;<\/span><span style=\"color: #F8F8F2\">: prompt}],<\/span><\/span>\n<span class=\"line\"><span style=\"color: #F8F8F2\">          <\/span><span style=\"color: #E6DB74\">&#39;max_tokens&#39;<\/span><span style=\"color: #F8F8F2\">: <\/span><span style=\"color: #AE81FF\">1000<\/span><\/span>\n<span class=\"line\"><span style=\"color: #F8F8F2\">        })<\/span><\/span>\n<span class=\"line\"><span style=\"color: #F8F8F2\">      };<\/span><\/span>\n<span class=\"line\"><span style=\"color: #F8F8F2\">      <\/span><\/span>\n<span class=\"line\"><span style=\"color: #F8F8F2\">      <\/span><span style=\"color: #88846F\">\/\/ Send a request to the ChatGPT API and retrieve the result<\/span><\/span>\n<span class=\"line\"><span style=\"color: #F8F8F2\">      <\/span><span style=\"color: #66D9EF; font-style: italic\">var<\/span><span style=\"color: #F8F8F2\"> response <\/span><span style=\"color: #F92672\">=<\/span><span style=\"color: #F8F8F2\"> UrlFetchApp.<\/span><span style=\"color: #A6E22E\">fetch<\/span><span style=\"color: #F8F8F2\">(url, options);<\/span><\/span>\n<span class=\"line\"><span style=\"color: #F8F8F2\">      <\/span><span style=\"color: #66D9EF; font-style: italic\">var<\/span><span style=\"color: #F8F8F2\"> json <\/span><span style=\"color: #F92672\">=<\/span><span style=\"color: #F8F8F2\"> response.<\/span><span style=\"color: #A6E22E\">getContentText<\/span><span style=\"color: #F8F8F2\">();<\/span><\/span>\n<span class=\"line\"><span style=\"color: #F8F8F2\">      <\/span><span style=\"color: #66D9EF; font-style: italic\">var<\/span><span style=\"color: #F8F8F2\"> data <\/span><span style=\"color: #F92672\">=<\/span><span style=\"color: #F8F8F2\"> JSON.<\/span><span style=\"color: #A6E22E\">parse<\/span><span style=\"color: #F8F8F2\">(json);<\/span><\/span>\n<span class=\"line\"><span style=\"color: #F8F8F2\">      <\/span><\/span>\n<span class=\"line\"><span style=\"color: #F8F8F2\">      <\/span><span style=\"color: #88846F\">\/\/ Display the ChatGPT response in column B (second column) of the same row<\/span><\/span>\n<span class=\"line\"><span style=\"color: #F8F8F2\">      sheet.<\/span><span style=\"color: #A6E22E\">getRange<\/span><span style=\"color: #F8F8F2\">(i, <\/span><span style=\"color: #AE81FF\">2<\/span><span style=\"color: #F8F8F2\">).<\/span><span style=\"color: #A6E22E\">setValue<\/span><span style=\"color: #F8F8F2\">(data.choices[<\/span><span style=\"color: #AE81FF\">0<\/span><span style=\"color: #F8F8F2\">].message.content.<\/span><span style=\"color: #A6E22E\">trim<\/span><span style=\"color: #F8F8F2\">());<\/span><\/span>\n<span class=\"line\"><span style=\"color: #F8F8F2\">    }<\/span><\/span>\n<span class=\"line\"><span style=\"color: #F8F8F2\">  }<\/span><\/span>\n<span class=\"line\"><span style=\"color: #F8F8F2\">}<\/span><\/span>\n<span class=\"line\"><\/span><\/code><\/pre><\/div>\n\n\n\n<p>Once the API key is saved in the properties, the next step is to create the script.<\/p>\n\n\n\n<p class=\"u-mb-ctrl u-mb-40\">Copy and paste the code above into your editor.<\/p>\n\n\n\n<div class=\"swell-block-accordion\">\n<details class=\"swell-block-accordion__item\" data-swl-acc=\"wrapper\"><summary class=\"swell-block-accordion__title\" data-swl-acc=\"header\"><span class=\"swell-block-accordion__label\">Script Description<\/span><span class=\"swell-block-accordion__icon c-switchIconBtn\" data-swl-acc=\"icon\" aria-hidden=\"true\" data-opened=\"false\"><i class=\"__icon--closed icon-caret-down\"><\/i><i class=\"__icon--opened icon-caret-up\"><\/i><\/span><\/summary><div class=\"swell-block-accordion__body\" data-swl-acc=\"body\">\n<dl class=\"swell-block-dl\">\n<dt class=\"swell-block-dl__dt\"><strong>Get API key from script properties<\/strong><\/dt>\n\n\n\n<dd class=\"swell-block-dl__dd\">\n<div class=\"wp-block-kevinbatdorf-code-block-pro\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span role=\"button\" tabindex=\"0\" data-code=\"function getChatGPTResponse() {\n  \/\/ Retrieve the API key from the script properties\n  var scriptProperties = PropertiesService.getScriptProperties();\n  var apiKey = scriptProperties.getProperty('OPENAI_API_KEY');\n  \" style=\"color:#F8F8F2;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki monokai\" style=\"background-color: #272822\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #66D9EF; font-style: italic\">function<\/span><span style=\"color: #F8F8F2\"> <\/span><span style=\"color: #A6E22E\">getChatGPTResponse<\/span><span style=\"color: #F8F8F2\">() {<\/span><\/span>\n<span class=\"line\"><span style=\"color: #F8F8F2\">  <\/span><span style=\"color: #88846F\">\/\/ Retrieve the API key from the script properties<\/span><\/span>\n<span class=\"line\"><span style=\"color: #F8F8F2\">  <\/span><span style=\"color: #66D9EF; font-style: italic\">var<\/span><span style=\"color: #F8F8F2\"> scriptProperties <\/span><span style=\"color: #F92672\">=<\/span><span style=\"color: #F8F8F2\"> PropertiesService.<\/span><span style=\"color: #A6E22E\">getScriptProperties<\/span><span style=\"color: #F8F8F2\">();<\/span><\/span>\n<span class=\"line\"><span style=\"color: #F8F8F2\">  <\/span><span style=\"color: #66D9EF; font-style: italic\">var<\/span><span style=\"color: #F8F8F2\"> apiKey <\/span><span style=\"color: #F92672\">=<\/span><span style=\"color: #F8F8F2\"> scriptProperties.<\/span><span style=\"color: #A6E22E\">getProperty<\/span><span style=\"color: #F8F8F2\">(<\/span><span style=\"color: #E6DB74\">&#39;OPENAI_API_KEY&#39;<\/span><span style=\"color: #F8F8F2\">);<\/span><\/span>\n<span class=\"line\"><span style=\"color: #F8F8F2\">  <\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<ul class=\"wp-block-list\">\n<li>The API key to access the ChatGPT API is stored in a Google Apps Script script property, which is then retrieved.<\/li>\n\n\n\n<li><code>OPENAI_API_KEY<\/code> is a pre-defined property name.<\/li>\n<\/ul>\n<\/dd>\n\n\n\n<dt class=\"swell-block-dl__dt\">Get active sheet and last row of spreadsheet<\/dt>\n\n\n\n<dd class=\"swell-block-dl__dd\">\n<div class=\"wp-block-kevinbatdorf-code-block-pro\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span role=\"button\" tabindex=\"0\" data-code=\"  \/\/ Get the active sheet of the spreadsheet\n  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();\n  var lastRow = sheet.getLastRow(); \/\/ Get the last row\n  \" style=\"color:#F8F8F2;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki monokai\" style=\"background-color: #272822\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #F8F8F2\">  <\/span><span style=\"color: #88846F\">\/\/ Get the active sheet of the spreadsheet<\/span><\/span>\n<span class=\"line\"><span style=\"color: #F8F8F2\">  <\/span><span style=\"color: #66D9EF; font-style: italic\">var<\/span><span style=\"color: #F8F8F2\"> sheet <\/span><span style=\"color: #F92672\">=<\/span><span style=\"color: #F8F8F2\"> SpreadsheetApp.<\/span><span style=\"color: #A6E22E\">getActiveSpreadsheet<\/span><span style=\"color: #F8F8F2\">().<\/span><span style=\"color: #A6E22E\">getActiveSheet<\/span><span style=\"color: #F8F8F2\">();<\/span><\/span>\n<span class=\"line\"><span style=\"color: #F8F8F2\">  <\/span><span style=\"color: #66D9EF; font-style: italic\">var<\/span><span style=\"color: #F8F8F2\"> lastRow <\/span><span style=\"color: #F92672\">=<\/span><span style=\"color: #F8F8F2\"> sheet.<\/span><span style=\"color: #A6E22E\">getLastRow<\/span><span style=\"color: #F8F8F2\">(); <\/span><span style=\"color: #88846F\">\/\/ Get the last row<\/span><\/span>\n<span class=\"line\"><span style=\"color: #F8F8F2\">  <\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Get Active Spreadsheet:<\/strong> Get the active sheet of the currently open spreadsheet.<\/li>\n\n\n\n<li><strong>Get Last Row<\/strong>: Gets the last row in the sheet where data is entered. This determines how far along to process.<\/li>\n<\/ul>\n<\/dd>\n\n\n\n<dt class=\"swell-block-dl__dt\">Loop processing \/ Get prompt of column A<\/dt>\n\n\n\n<dd class=\"swell-block-dl__dd\">\n<div class=\"wp-block-kevinbatdorf-code-block-pro\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span role=\"button\" tabindex=\"0\" data-code=\"  \/\/ Process rows from the second row to the last row in column A\n  for (var i = 2; i &lt;= lastRow; i++) { \/\/ Start from the second row\n    var prompt = sheet.getRange(i, 1).getValue(); \/\/ Get the prompt from column A\n    \" style=\"color:#F8F8F2;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki monokai\" style=\"background-color: #272822\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #F8F8F2\">  <\/span><span style=\"color: #88846F\">\/\/ Process rows from the second row to the last row in column A<\/span><\/span>\n<span class=\"line\"><span style=\"color: #F8F8F2\">  <\/span><span style=\"color: #F92672\">for<\/span><span style=\"color: #F8F8F2\"> (<\/span><span style=\"color: #66D9EF; font-style: italic\">var<\/span><span style=\"color: #F8F8F2\"> i <\/span><span style=\"color: #F92672\">=<\/span><span style=\"color: #F8F8F2\"> <\/span><span style=\"color: #AE81FF\">2<\/span><span style=\"color: #F8F8F2\">; i <\/span><span style=\"color: #F92672\">&lt;=<\/span><span style=\"color: #F8F8F2\"> lastRow; i<\/span><span style=\"color: #F92672\">++<\/span><span style=\"color: #F8F8F2\">) { <\/span><span style=\"color: #88846F\">\/\/ Start from the second row<\/span><\/span>\n<span class=\"line\"><span style=\"color: #F8F8F2\">    <\/span><span style=\"color: #66D9EF; font-style: italic\">var<\/span><span style=\"color: #F8F8F2\"> prompt <\/span><span style=\"color: #F92672\">=<\/span><span style=\"color: #F8F8F2\"> sheet.<\/span><span style=\"color: #A6E22E\">getRange<\/span><span style=\"color: #F8F8F2\">(i, <\/span><span style=\"color: #AE81FF\">1<\/span><span style=\"color: #F8F8F2\">).<\/span><span style=\"color: #A6E22E\">getValue<\/span><span style=\"color: #F8F8F2\">(); <\/span><span style=\"color: #88846F\">\/\/ Get the prompt from column A<\/span><\/span>\n<span class=\"line\"><span style=\"color: #F8F8F2\">    <\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Loop processing:<\/strong> a <code>for<\/code> loop is used to process the second to last row in sequence, starting with row 2 as <code>i = 2<\/code>.<\/li>\n\n\n\n<li><strong>Get the prompt for column A:<\/strong> <code>sheet.getRange(i, 1)<\/code> gets the value of column A in row i and treats that value as a <code>prompt<\/code>. This <code>prompt<\/code> is the text sent to ChatGPT.<\/li>\n<\/ul>\n<\/dd>\n\n\n\n<dt class=\"swell-block-dl__dt\">Skip empty prompts \/ API request settings<\/dt>\n\n\n\n<dd class=\"swell-block-dl__dd\">\n<div class=\"wp-block-kevinbatdorf-code-block-pro\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span role=\"button\" tabindex=\"0\" data-code=\"    \/\/ Send a request to the ChatGPT API if the prompt is not empty\n    if (prompt) {\n      var url = 'https:\/\/api.openai.com\/v1\/chat\/completions';\n      var options = {\n        'method': 'post',\n        'headers': {\n          'Authorization': 'Bearer ' + apiKey,\n          'Content-Type': 'application\/json'\n        },\n        'payload': JSON.stringify({\n          'model': 'gpt-3.5-turbo', \/\/ Specify the model to use\n          'messages': [{&quot;role&quot;: &quot;system&quot;, &quot;content&quot;: &quot;You are a helpful assistant.&quot;},\n                       {&quot;role&quot;: &quot;user&quot;, &quot;content&quot;: prompt}],\n          'max_tokens': 1000\n        })\n      };\n      \" style=\"color:#F8F8F2;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki monokai\" style=\"background-color: #272822\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #F8F8F2\">    <\/span><span style=\"color: #88846F\">\/\/ Send a request to the ChatGPT API if the prompt is not empty<\/span><\/span>\n<span class=\"line\"><span style=\"color: #F8F8F2\">    <\/span><span style=\"color: #F92672\">if<\/span><span style=\"color: #F8F8F2\"> (prompt) {<\/span><\/span>\n<span class=\"line\"><span style=\"color: #F8F8F2\">      <\/span><span style=\"color: #66D9EF; font-style: italic\">var<\/span><span style=\"color: #F8F8F2\"> url <\/span><span style=\"color: #F92672\">=<\/span><span style=\"color: #F8F8F2\"> <\/span><span style=\"color: #E6DB74\">&#39;https:\/\/api.openai.com\/v1\/chat\/completions&#39;<\/span><span style=\"color: #F8F8F2\">;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #F8F8F2\">      <\/span><span style=\"color: #66D9EF; font-style: italic\">var<\/span><span style=\"color: #F8F8F2\"> options <\/span><span style=\"color: #F92672\">=<\/span><span style=\"color: #F8F8F2\"> {<\/span><\/span>\n<span class=\"line\"><span style=\"color: #F8F8F2\">        <\/span><span style=\"color: #E6DB74\">&#39;method&#39;<\/span><span style=\"color: #F8F8F2\">: <\/span><span style=\"color: #E6DB74\">&#39;post&#39;<\/span><span style=\"color: #F8F8F2\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #F8F8F2\">        <\/span><span style=\"color: #E6DB74\">&#39;headers&#39;<\/span><span style=\"color: #F8F8F2\">: {<\/span><\/span>\n<span class=\"line\"><span style=\"color: #F8F8F2\">          <\/span><span style=\"color: #E6DB74\">&#39;Authorization&#39;<\/span><span style=\"color: #F8F8F2\">: <\/span><span style=\"color: #E6DB74\">&#39;Bearer &#39;<\/span><span style=\"color: #F8F8F2\"> <\/span><span style=\"color: #F92672\">+<\/span><span style=\"color: #F8F8F2\"> apiKey,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #F8F8F2\">          <\/span><span style=\"color: #E6DB74\">&#39;Content-Type&#39;<\/span><span style=\"color: #F8F8F2\">: <\/span><span style=\"color: #E6DB74\">&#39;application\/json&#39;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #F8F8F2\">        },<\/span><\/span>\n<span class=\"line\"><span style=\"color: #F8F8F2\">        <\/span><span style=\"color: #E6DB74\">&#39;payload&#39;<\/span><span style=\"color: #F8F8F2\">: JSON.<\/span><span style=\"color: #A6E22E\">stringify<\/span><span style=\"color: #F8F8F2\">({<\/span><\/span>\n<span class=\"line\"><span style=\"color: #F8F8F2\">          <\/span><span style=\"color: #E6DB74\">&#39;model&#39;<\/span><span style=\"color: #F8F8F2\">: <\/span><span style=\"color: #E6DB74\">&#39;gpt-3.5-turbo&#39;<\/span><span style=\"color: #F8F8F2\">, <\/span><span style=\"color: #88846F\">\/\/ Specify the model to use<\/span><\/span>\n<span class=\"line\"><span style=\"color: #F8F8F2\">          <\/span><span style=\"color: #E6DB74\">&#39;messages&#39;<\/span><span style=\"color: #F8F8F2\">: [{<\/span><span style=\"color: #E6DB74\">&quot;role&quot;<\/span><span style=\"color: #F8F8F2\">: <\/span><span style=\"color: #E6DB74\">&quot;system&quot;<\/span><span style=\"color: #F8F8F2\">, <\/span><span style=\"color: #E6DB74\">&quot;content&quot;<\/span><span style=\"color: #F8F8F2\">: <\/span><span style=\"color: #E6DB74\">&quot;You are a helpful assistant.&quot;<\/span><span style=\"color: #F8F8F2\">},<\/span><\/span>\n<span class=\"line\"><span style=\"color: #F8F8F2\">                       {<\/span><span style=\"color: #E6DB74\">&quot;role&quot;<\/span><span style=\"color: #F8F8F2\">: <\/span><span style=\"color: #E6DB74\">&quot;user&quot;<\/span><span style=\"color: #F8F8F2\">, <\/span><span style=\"color: #E6DB74\">&quot;content&quot;<\/span><span style=\"color: #F8F8F2\">: prompt}],<\/span><\/span>\n<span class=\"line\"><span style=\"color: #F8F8F2\">          <\/span><span style=\"color: #E6DB74\">&#39;max_tokens&#39;<\/span><span style=\"color: #F8F8F2\">: <\/span><span style=\"color: #AE81FF\">1000<\/span><\/span>\n<span class=\"line\"><span style=\"color: #F8F8F2\">        })<\/span><\/span>\n<span class=\"line\"><span style=\"color: #F8F8F2\">      };<\/span><\/span>\n<span class=\"line\"><span style=\"color: #F8F8F2\">      <\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Skip empty prompt:<\/strong> <code>if (prompt)<\/code>, continue processing only if column A contains a value. If there are blank cells, they are ignored.<\/li>\n\n\n\n<li><strong>API request settings:<\/strong> <code>url<\/code>: Specify the API endpoint for OpenAI. Here, <a href=\"https:\/\/api.openai.com\/v1\/chat\/completions\"><code>https:\/\/api.openai.com\/v1\/chat\/completions<\/code><\/a> is used to send a chat-style request.<\/li>\n\n\n\n<li><strong><code>headers<\/code>:<\/strong> Send the API key as an <code>Authorization<\/code> header.<\/li>\n\n\n\n<li><code>payload<\/code><strong>:<\/strong> sets the data to be sent to the ChatGPT API. <code>messages<\/code> includes instructions from the system and user input (<code>prompt<\/code>).<\/li>\n\n\n\n<li><code>max_tokens<\/code> specifies the maximum number of tokens (length of reply) returned by the API.<\/li>\n<\/ul>\n<\/dd>\n\n\n\n<dt class=\"swell-block-dl__dt\">Sending requests \/ processing responses<\/dt>\n\n\n\n<dd class=\"swell-block-dl__dd\">\n<div class=\"wp-block-kevinbatdorf-code-block-pro\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span role=\"button\" tabindex=\"0\" data-code=\"      \/\/ Send a request to the ChatGPT API and retrieve the result\n      var response = UrlFetchApp.fetch(url, options);\n      var json = response.getContentText();\n      var data = JSON.parse(json);\n      \" style=\"color:#F8F8F2;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki monokai\" style=\"background-color: #272822\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #F8F8F2\">      <\/span><span style=\"color: #88846F\">\/\/ Send a request to the ChatGPT API and retrieve the result<\/span><\/span>\n<span class=\"line\"><span style=\"color: #F8F8F2\">      <\/span><span style=\"color: #66D9EF; font-style: italic\">var<\/span><span style=\"color: #F8F8F2\"> response <\/span><span style=\"color: #F92672\">=<\/span><span style=\"color: #F8F8F2\"> UrlFetchApp.<\/span><span style=\"color: #A6E22E\">fetch<\/span><span style=\"color: #F8F8F2\">(url, options);<\/span><\/span>\n<span class=\"line\"><span style=\"color: #F8F8F2\">      <\/span><span style=\"color: #66D9EF; font-style: italic\">var<\/span><span style=\"color: #F8F8F2\"> json <\/span><span style=\"color: #F92672\">=<\/span><span style=\"color: #F8F8F2\"> response.<\/span><span style=\"color: #A6E22E\">getContentText<\/span><span style=\"color: #F8F8F2\">();<\/span><\/span>\n<span class=\"line\"><span style=\"color: #F8F8F2\">      <\/span><span style=\"color: #66D9EF; font-style: italic\">var<\/span><span style=\"color: #F8F8F2\"> data <\/span><span style=\"color: #F92672\">=<\/span><span style=\"color: #F8F8F2\"> JSON.<\/span><span style=\"color: #A6E22E\">parse<\/span><span style=\"color: #F8F8F2\">(json);<\/span><\/span>\n<span class=\"line\"><span style=\"color: #F8F8F2\">      <\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Send request:<\/strong> send API request with <code>UrlFetchApp.fetch(url, options)<\/code> and get response from ChatGPT.<\/li>\n\n\n\n<li><strong>Response processing: <\/strong>The acquired response is converted to JSON format and stored in data.<\/li>\n<\/ul>\n<\/dd>\n\n\n\n<dt class=\"swell-block-dl__dt\">Output answers to column B \/ Formatting of answers<\/dt>\n\n\n\n<dd class=\"swell-block-dl__dd\">\n<div class=\"wp-block-kevinbatdorf-code-block-pro\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span role=\"button\" tabindex=\"0\" data-code=\"      \/\/ Display the ChatGPT response in column B (second column) of the same row\n      sheet.getRange(i, 2).setValue(data.choices[0].message.content.trim());\n    }\n  }\n}\" style=\"color:#F8F8F2;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki monokai\" style=\"background-color: #272822\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #F8F8F2\">      <\/span><span style=\"color: #88846F\">\/\/ Display the ChatGPT response in column B (second column) of the same row<\/span><\/span>\n<span class=\"line\"><span style=\"color: #F8F8F2\">      sheet.<\/span><span style=\"color: #A6E22E\">getRange<\/span><span style=\"color: #F8F8F2\">(i, <\/span><span style=\"color: #AE81FF\">2<\/span><span style=\"color: #F8F8F2\">).<\/span><span style=\"color: #A6E22E\">setValue<\/span><span style=\"color: #F8F8F2\">(data.choices[<\/span><span style=\"color: #AE81FF\">0<\/span><span style=\"color: #F8F8F2\">].message.content.<\/span><span style=\"color: #A6E22E\">trim<\/span><span style=\"color: #F8F8F2\">());<\/span><\/span>\n<span class=\"line\"><span style=\"color: #F8F8F2\">    }<\/span><\/span>\n<span class=\"line\"><span style=\"color: #F8F8F2\">  }<\/span><\/span>\n<span class=\"line\"><span style=\"color: #F8F8F2\">}<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Output responses to column B:<\/strong> Use <code>sheet.getRange(i, 2).setValue()<\/code>to output the ChatGPT responses to column B of the same row.<\/li>\n\n\n\n<li><strong>Answer formatting:<\/strong> use <code>trim()<\/code> to remove excess whitespace and display clean text.<\/li>\n<\/ul>\n<\/dd>\n<\/dl>\n\n\n\n\n<\/div><\/details>\n<\/div>\n<\/div><\/div>\n\n\n\n<div class=\"swell-block-step__item\"><div class=\"swell-block-step__number u-bg-main\"><span class=\"__label\">STEP<\/span><\/div><div class=\"swell-block-step__title u-fz-l\"><strong>Save the Script<\/strong><\/div><div class=\"swell-block-step__body\"><div class=\"wp-block-image u-mb-ctrl u-mb-40\">\n<figure class=\"alignleft size-large\"><img decoding=\"async\" width=\"1024\" height=\"442\" src=\"https:\/\/84office.jp\/wp-content\/uploads\/2024\/11\/a4d02d2969e8b2333ff066dd6ee76972-1024x442.png\" alt=\"\" class=\"wp-image-16209\" srcset=\"https:\/\/84office.jp\/wp-content\/uploads\/2024\/11\/a4d02d2969e8b2333ff066dd6ee76972-1024x442.png 1024w, https:\/\/84office.jp\/wp-content\/uploads\/2024\/11\/a4d02d2969e8b2333ff066dd6ee76972-300x130.png 300w, https:\/\/84office.jp\/wp-content\/uploads\/2024\/11\/a4d02d2969e8b2333ff066dd6ee76972-768x332.png 768w, https:\/\/84office.jp\/wp-content\/uploads\/2024\/11\/a4d02d2969e8b2333ff066dd6ee76972-1536x664.png 1536w, https:\/\/84office.jp\/wp-content\/uploads\/2024\/11\/a4d02d2969e8b2333ff066dd6ee76972.png 1627w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n<\/div>\n\n\n<p class=\"u-mb-ctrl u-mb-40\">After writing the code, name it and save it.<\/p>\n<\/div><\/div>\n\n\n\n<div class=\"swell-block-step__item\"><div class=\"swell-block-step__number u-bg-main\"><span class=\"__label\">STEP<\/span><\/div><div class=\"swell-block-step__title u-fz-l\"><strong>Run the Script<\/strong><\/div><div class=\"swell-block-step__body\">\n<figure class=\"wp-block-image size-large u-mb-ctrl u-mb-40\"><img decoding=\"async\" width=\"1024\" height=\"442\" src=\"https:\/\/84office.jp\/wp-content\/uploads\/2024\/11\/a4d02d2969e8b2333ff066dd6ee76972-1-1024x442.png\" alt=\"\" class=\"wp-image-16210\" srcset=\"https:\/\/84office.jp\/wp-content\/uploads\/2024\/11\/a4d02d2969e8b2333ff066dd6ee76972-1-1024x442.png 1024w, https:\/\/84office.jp\/wp-content\/uploads\/2024\/11\/a4d02d2969e8b2333ff066dd6ee76972-1-300x130.png 300w, https:\/\/84office.jp\/wp-content\/uploads\/2024\/11\/a4d02d2969e8b2333ff066dd6ee76972-1-768x332.png 768w, https:\/\/84office.jp\/wp-content\/uploads\/2024\/11\/a4d02d2969e8b2333ff066dd6ee76972-1-1536x664.png 1536w, https:\/\/84office.jp\/wp-content\/uploads\/2024\/11\/a4d02d2969e8b2333ff066dd6ee76972-1.png 1627w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p class=\"u-mb-ctrl u-mb-40\">Enter the prompt in column A of the spreadsheet and run the script.<\/p>\n\n\n\n<div class=\"wp-block-group is-style-big_icon_memo u-mb-ctrl u-mb-60\"><div class=\"wp-block-group__inner-container is-layout-constrained wp-block-group-is-layout-constrained\">\n<p>If you&#8217;re running the script for the first time, you need to authorize it.<\/p>\n\n\n\n<p><strong>Therefore, press &#8220;<strong>Review Permissions.<\/strong>&#8220;<\/strong><\/p>\n\n\n<div class=\"wp-block-image u-mb-ctrl u-mb-50\">\n<figure class=\"alignleft size-full\"><img decoding=\"async\" width=\"882\" height=\"345\" src=\"https:\/\/84office.jp\/wp-content\/uploads\/2024\/11\/image-79.png\" alt=\"\" class=\"wp-image-15938\" srcset=\"https:\/\/84office.jp\/wp-content\/uploads\/2024\/11\/image-79.png 882w, https:\/\/84office.jp\/wp-content\/uploads\/2024\/11\/image-79-300x117.png 300w, https:\/\/84office.jp\/wp-content\/uploads\/2024\/11\/image-79-768x300.png 768w\" sizes=\"(max-width: 882px) 100vw, 882px\" \/><\/figure>\n<\/div>\n\n\n<div class=\"swell-block-accordion\">\n<details class=\"swell-block-accordion__item\" data-swl-acc=\"wrapper\"><summary class=\"swell-block-accordion__title\" data-swl-acc=\"header\"><span class=\"swell-block-accordion__label\">Detailed Authorization Steps<\/span><span class=\"swell-block-accordion__icon c-switchIconBtn\" data-swl-acc=\"icon\" aria-hidden=\"true\" data-opened=\"false\"><i class=\"__icon--closed icon-caret-down\"><\/i><i class=\"__icon--opened icon-caret-up\"><\/i><\/span><\/summary><div class=\"swell-block-accordion__body\" data-swl-acc=\"body\">\n<p><strong>Press &#8220;Advanced.&#8221;<\/strong><\/p>\n\n\n\n<figure class=\"wp-block-image size-full u-mb-ctrl u-mb-50\"><img decoding=\"async\" width=\"1272\" height=\"688\" src=\"https:\/\/84office.jp\/wp-content\/uploads\/2024\/11\/188993dfac23ed4b35c326d75de2796d.jpg\" alt=\"\" class=\"wp-image-15939\" srcset=\"https:\/\/84office.jp\/wp-content\/uploads\/2024\/11\/188993dfac23ed4b35c326d75de2796d.jpg 1272w, https:\/\/84office.jp\/wp-content\/uploads\/2024\/11\/188993dfac23ed4b35c326d75de2796d-300x162.jpg 300w, https:\/\/84office.jp\/wp-content\/uploads\/2024\/11\/188993dfac23ed4b35c326d75de2796d-1024x554.jpg 1024w, https:\/\/84office.jp\/wp-content\/uploads\/2024\/11\/188993dfac23ed4b35c326d75de2796d-768x415.jpg 768w\" sizes=\"(max-width: 1272px) 100vw, 1272px\" \/><\/figure>\n\n\n\n\n\n\n\n<p><strong>Press &#8220;Go to Untitled project (Unsafe).&#8221;<\/strong><\/p>\n\n\n<div class=\"wp-block-image u-mb-ctrl u-mb-50\">\n<figure class=\"alignleft size-full\"><img decoding=\"async\" width=\"1305\" height=\"893\" src=\"https:\/\/84office.jp\/wp-content\/uploads\/2024\/11\/b7775751f92380510b389302eaafe1b9.jpg\" alt=\"\" class=\"wp-image-15940\" srcset=\"https:\/\/84office.jp\/wp-content\/uploads\/2024\/11\/b7775751f92380510b389302eaafe1b9.jpg 1305w, https:\/\/84office.jp\/wp-content\/uploads\/2024\/11\/b7775751f92380510b389302eaafe1b9-300x205.jpg 300w, https:\/\/84office.jp\/wp-content\/uploads\/2024\/11\/b7775751f92380510b389302eaafe1b9-1024x701.jpg 1024w, https:\/\/84office.jp\/wp-content\/uploads\/2024\/11\/b7775751f92380510b389302eaafe1b9-768x526.jpg 768w\" sizes=\"(max-width: 1305px) 100vw, 1305px\" \/><\/figure>\n<\/div>\n\n\n<p><strong>After that, press &#8220;Allow.&#8221;<\/strong><\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"alignleft size-full is-resized\"><img decoding=\"async\" width=\"955\" height=\"543\" src=\"https:\/\/84office.jp\/wp-content\/uploads\/2024\/11\/7ebb34b4d2c42fa532ad1c35c1ec1049.jpg\" alt=\"\" class=\"wp-image-15941\" style=\"width:566px;height:auto\" srcset=\"https:\/\/84office.jp\/wp-content\/uploads\/2024\/11\/7ebb34b4d2c42fa532ad1c35c1ec1049.jpg 955w, https:\/\/84office.jp\/wp-content\/uploads\/2024\/11\/7ebb34b4d2c42fa532ad1c35c1ec1049-300x171.jpg 300w, https:\/\/84office.jp\/wp-content\/uploads\/2024\/11\/7ebb34b4d2c42fa532ad1c35c1ec1049-768x437.jpg 768w\" sizes=\"(max-width: 955px) 100vw, 955px\" \/><\/figure>\n<\/div>\n\n\n\n<\/div><\/details>\n<\/div>\n<\/div><\/div>\n<\/div><\/div>\n\n\n\n<div class=\"swell-block-step__item\"><div class=\"swell-block-step__number u-bg-main\"><span class=\"__label\">STEP<\/span><\/div><div class=\"swell-block-step__title u-fz-l\">Output ChatGPT response<\/div><div class=\"swell-block-step__body\"><div class=\"wp-block-image u-mb-ctrl u-mb-50\">\n<figure class=\"alignleft size-full\"><img decoding=\"async\" width=\"647\" height=\"164\" src=\"https:\/\/84office.jp\/wp-content\/uploads\/2024\/11\/image-129.png\" alt=\"\" class=\"wp-image-16211\" srcset=\"https:\/\/84office.jp\/wp-content\/uploads\/2024\/11\/image-129.png 647w, https:\/\/84office.jp\/wp-content\/uploads\/2024\/11\/image-129-300x76.png 300w\" sizes=\"(max-width: 647px) 100vw, 647px\" \/><\/figure>\n<\/div>\n\n\n<p>The ChatGPT responses appear in column B.<\/p>\n<\/div><\/div>\n<\/div>\n\n\n\n<p id=\"block-3ecb6e71-6909-4406-bd72-0fc2ca3187f1\">This completes the method of linking ChatGPT and spreadsheets using Google Apps Script.<\/p>\n\n\n\n<h2 class=\"wp-block-heading u-mb-ctrl u-mb-30\">Benefits of linking ChatGPT and Google Sheets<\/h2>\n\n\n\n<p class=\"has-border -border01 u-mb-ctrl u-mb-60 has-swl-pale-03-background-color has-background\"><strong>You can ask ChatGPT questions in bulk.<\/strong><br>Process a large number of prompts at once and automate responses to reduce work time.<br><br><strong>No more copying and pasting from ChatGPT to Google Sheets.<\/strong><br>ChatGPT responses are displayed on a Google sheets, eliminating the need for tedious copy\/paste work.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Notes on using ChatGPT API<\/h2>\n\n\n\n<p>The following are some of the caveats when using the API.<\/p>\n\n\n\n<ul class=\"wp-block-list u-mb-ctrl u-mb-60 has-swl-pale-04-background-color has-background\">\n<li><strong>Data security: <\/strong>Care must be taken to ensure that data transmitted does not contain sensitive information, and measures must be taken to protect privacy.<\/li>\n\n\n\n<li><strong>Cost management: <\/strong>Since the ChatGPT API has a usage fee, it is necessary to check the API usage and costs.<br>\uff08<a href=\"https:\/\/openai.com\/pricing\">OpenAI Rates Page<\/a>\uff09<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading u-mb-ctrl u-mb-30\">Conclusion<\/h2>\n\n\n\n<p>By linking ChatGPT and spreadsheets via API, you can automate data processing to streamline your work.<\/p>\n\n\n\n<p>You can eliminate the need to manually copy-paste questions from ChatGPT to a spreadsheet or enter questions individually, and get answers to multiple prompts on a spreadsheet at once.<\/p>\n\n\n\n<p>By modifying GAS scripts, ChatGPT can also be used to automate the categorization and analysis of data in spreadsheets.<\/p>\n\n\n\n<p class=\"u-mb-ctrl u-mb-50\">Please take advantage of this service when you want to improve your business productivity.<\/p>\n\n\n<div class=\"swell-block-postLink u-mb-ctrl u-mb-40\"><\/div>\n\n<div class=\"p-postListWrap\"><ul class=\"p-postList -type-card -pc-col3 -sp-col1\"><li class=\"p-postList__item\">\n\t<a href=\"https:\/\/84office.jp\/en\/google-calendar-free-time-template\/\" class=\"p-postList__link\">\n\t\t<div class=\"p-postList__thumb c-postThumb\">\n\t<figure class=\"c-postThumb__figure\">\n\t\t<img decoding=\"async\" width=\"1024\" height=\"538\"  src=\"https:\/\/84office.jp\/wp-content\/uploads\/2025\/07\/ce7cc8b37e59afe6317fb52e1264cdbb-1024x538.jpg\" alt=\"\" class=\"c-postThumb__img u-obf-cover\" srcset=\"https:\/\/84office.jp\/wp-content\/uploads\/2025\/07\/ce7cc8b37e59afe6317fb52e1264cdbb-1024x538.jpg 1024w, https:\/\/84office.jp\/wp-content\/uploads\/2025\/07\/ce7cc8b37e59afe6317fb52e1264cdbb-300x158.jpg 300w, https:\/\/84office.jp\/wp-content\/uploads\/2025\/07\/ce7cc8b37e59afe6317fb52e1264cdbb-768x403.jpg 768w, https:\/\/84office.jp\/wp-content\/uploads\/2025\/07\/ce7cc8b37e59afe6317fb52e1264cdbb.jpg 1200w\" sizes=\"auto, (min-width: 960px) 400px, 100vw\" loading=\"lazy\" >\t<\/figure>\n\t\t\t<span class=\"c-postThumb__cat icon-folder\" data-cat-id=\"12\">Google Sheets<\/span>\n\t<\/div>\n\t\t\t\t\t<div class=\"p-postList__body\">\n\t\t\t\t<h2 class=\"p-postList__title\">Google Calendar Free Time Finder Template\uff5cGoogle Sheets \u00d7 Apps Script<\/h2>\t\t\t\t\t\t\t\t<div class=\"p-postList__meta\">\n\t\t\t\t\t<div class=\"p-postList__times c-postTimes u-thin\">\n\t<time class=\"c-postTimes__posted icon-posted\" datetime=\"2025-07-25\" aria-label=\"\u516c\u958b\u65e5\">2025\u5e747\u670825\u65e5<\/time><\/div>\n\t\t\t\t<\/div>\n\t\t\t<\/div>\n\t\t\t<\/a>\n<\/li>\n<li class=\"p-postList__item\">\n\t<a href=\"https:\/\/84office.jp\/en\/google-calendar-free-time-extraction\/\" class=\"p-postList__link\">\n\t\t<div class=\"p-postList__thumb c-postThumb\">\n\t<figure class=\"c-postThumb__figure\">\n\t\t<img decoding=\"async\" width=\"1024\" height=\"538\"  src=\"https:\/\/84office.jp\/wp-content\/uploads\/2025\/07\/44f75ff8829b781f2ffdba1b6396054f-1024x538.jpg\" alt=\"\" class=\"c-postThumb__img u-obf-cover\" srcset=\"https:\/\/84office.jp\/wp-content\/uploads\/2025\/07\/44f75ff8829b781f2ffdba1b6396054f-1024x538.jpg 1024w, https:\/\/84office.jp\/wp-content\/uploads\/2025\/07\/44f75ff8829b781f2ffdba1b6396054f-300x158.jpg 300w, https:\/\/84office.jp\/wp-content\/uploads\/2025\/07\/44f75ff8829b781f2ffdba1b6396054f-768x403.jpg 768w, https:\/\/84office.jp\/wp-content\/uploads\/2025\/07\/44f75ff8829b781f2ffdba1b6396054f.jpg 1200w\" sizes=\"auto, (min-width: 960px) 400px, 100vw\" loading=\"lazy\" >\t<\/figure>\n\t\t\t<span class=\"c-postThumb__cat icon-folder\" data-cat-id=\"12\">Google Sheets<\/span>\n\t<\/div>\n\t\t\t\t\t<div class=\"p-postList__body\">\n\t\t\t\t<h2 class=\"p-postList__title\">How to Find Common Free Time in Google Calendar: Manage Shared Schedules with Google Apps Script<\/h2>\t\t\t\t\t\t\t\t<div class=\"p-postList__meta\">\n\t\t\t\t\t<div class=\"p-postList__times c-postTimes u-thin\">\n\t<time class=\"c-postTimes__posted icon-posted\" datetime=\"2025-07-24\" aria-label=\"\u516c\u958b\u65e5\">2025\u5e747\u670824\u65e5<\/time><\/div>\n\t\t\t\t<\/div>\n\t\t\t<\/div>\n\t\t\t<\/a>\n<\/li>\n<li class=\"p-postList__item\">\n\t<a href=\"https:\/\/84office.jp\/en\/google-calendar-to-sheet-template\/\" class=\"p-postList__link\">\n\t\t<div class=\"p-postList__thumb c-postThumb\">\n\t<figure class=\"c-postThumb__figure\">\n\t\t<img decoding=\"async\" width=\"1024\" height=\"538\"  src=\"https:\/\/84office.jp\/wp-content\/uploads\/2025\/07\/ab88c0708887b99cf51ee963a109a8c1-1024x538.jpg\" alt=\"\" class=\"c-postThumb__img u-obf-cover\" srcset=\"https:\/\/84office.jp\/wp-content\/uploads\/2025\/07\/ab88c0708887b99cf51ee963a109a8c1-1024x538.jpg 1024w, https:\/\/84office.jp\/wp-content\/uploads\/2025\/07\/ab88c0708887b99cf51ee963a109a8c1-300x158.jpg 300w, https:\/\/84office.jp\/wp-content\/uploads\/2025\/07\/ab88c0708887b99cf51ee963a109a8c1-768x403.jpg 768w, https:\/\/84office.jp\/wp-content\/uploads\/2025\/07\/ab88c0708887b99cf51ee963a109a8c1.jpg 1200w\" sizes=\"auto, (min-width: 960px) 400px, 100vw\" loading=\"lazy\" >\t<\/figure>\n\t\t\t<span class=\"c-postThumb__cat icon-folder\" data-cat-id=\"12\">Google Sheets<\/span>\n\t<\/div>\n\t\t\t\t\t<div class=\"p-postList__body\">\n\t\t\t\t<h2 class=\"p-postList__title\">Ready-to-Use Google Calendar to Sheets Export Template| Google Sheets &amp; Apps Script<\/h2>\t\t\t\t\t\t\t\t<div class=\"p-postList__meta\">\n\t\t\t\t\t<div class=\"p-postList__times c-postTimes u-thin\">\n\t<time class=\"c-postTimes__posted icon-posted\" datetime=\"2025-07-23\" aria-label=\"\u516c\u958b\u65e5\">2025\u5e747\u670823\u65e5<\/time><\/div>\n\t\t\t\t<\/div>\n\t\t\t<\/div>\n\t\t\t<\/a>\n<\/li>\n<\/ul><\/div>\n\n\n<p class=\"is-style-emboss_box has-swl-pale-02-background-color has-background\">Our company offers support for improving work efficiency through the use of Google Apps Script. <br>If you need assistance with Google Apps Script customization or error resolution, please feel free to contact us. <br>We are fully committed to supporting your business improvements.<br><br><a href=\"https:\/\/84office.jp\/contact\/\">Contact us here<br><\/a><\/p>\n\n\n\n<div style=\"height:100px\" aria-hidden=\"true\" class=\"wp-block-spacer u-mb-ctrl u-mb-0\"><\/div>\n\n\n\n<script src=\"https:\/\/codoc.jp\/js\/cms.js\" data-css=\"rainbow\" data-usercode=\"pQLZofrBiA\" charset=\"UTF-8\" defer><\/script>\n <div id=\"codoc-entry-9lNr9PuMXQ\" class=\"codoc-entries\" data-without-body=\"1\" data-support-button-text=\"Support with a Tip\" data-support-message=\"We will keep offering tools and information to boost work efficiency. Your support helps us provide even better content.\"><\/div>\n","protected":false},"excerpt":{"rendered":"<p>If you want to integrate ChatGPT and spreadsheets to make your work more efficient, but don&#8217;t know how t [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":16208,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"swell_btn_cv_data":"","_locale":"en_US","_original_post":"https:\/\/84office.jp\/?p=14820","footnotes":""},"categories":[12],"tags":[],"class_list":["post-16198","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-12","en-US"],"_links":{"self":[{"href":"https:\/\/84office.jp\/wp-json\/wp\/v2\/posts\/16198","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/84office.jp\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/84office.jp\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/84office.jp\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/84office.jp\/wp-json\/wp\/v2\/comments?post=16198"}],"version-history":[{"count":1,"href":"https:\/\/84office.jp\/wp-json\/wp\/v2\/posts\/16198\/revisions"}],"predecessor-version":[{"id":16212,"href":"https:\/\/84office.jp\/wp-json\/wp\/v2\/posts\/16198\/revisions\/16212"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/84office.jp\/wp-json\/wp\/v2\/media\/16208"}],"wp:attachment":[{"href":"https:\/\/84office.jp\/wp-json\/wp\/v2\/media?parent=16198"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/84office.jp\/wp-json\/wp\/v2\/categories?post=16198"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/84office.jp\/wp-json\/wp\/v2\/tags?post=16198"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}