Integrating AI in Google Sheets with Google Apps Script: How to Pass Prompts to ChatGPT and Get a Response
Welcome!
Have you heard about ChatGPT? It’s the latest buzz in the world of AI and everyone’s eager to unleash its true powers. As I delved deeper, I wondered — is it possible to integrate ChatGPT with Google Sheets? Can we pass prompts from Google Sheets and get a response from ChatGPT?
After some research, I discovered that it’s indeed possible to integrate ChatGPT with Google Sheets using the Open AI API. In this blog, we’ll be exploring how to bring AI to your spreadsheets using the Open AI API and Google Apps Script.
So let’s get started.
Sample Google Sheet
For this blog I will be using a very simple Google Sheet that contains a prompt and a response column. Inside the prompt column I will pass a prompt and the response column will contain the response generated from ChatGPT.
If you want to work with the Google Sheet, click here.Once you make a copy of the Google Sheet you have to go ahead and change the API key in the Google Apps Script code.
Step1: Get API keys
To obtain an OpenAI API key, start by logging into your OpenAI account on their website. Once you’re logged in, navigate to the API Keys tab on your Dashboard page.
Click on the Create new Secret Key button. Finally, click on the “Generate API Key” button to generate your new key, and then copy it to your clipboard. Be sure to store it in a secure place as it will not be visible again.
With your API key in hand, you can now use it to authenticate your requests to the OpenAI API and integrate ChatGPT with Google Sheets. Simply add the key to your Google Apps Script code and use the appropriate functions to make requests to the API. You’re now ready to start using the power of AI to enhance your spreadsheets!
Step2: Write the Automation Script
While you are in the Google Sheet, let’s open up the Script Editor to write some Google Apps Script. To open the Script Editor, follow these steps:
- Click on Extensions and open the Script Editor.
2. This brings up the Script Editor as shown below.
We have reached the script editor lets code.
Now that we have the Google Sheet setup and the API key ready, lets go ahead and write our Google Apps Script to get ChatGPT into Google Sheets.
//Replace with your API key
var API_key = "your_api_key"We are going to start by declaring the API key that we just generated.
function GPT(prompt) {
var Model_ID = "text-davinci-002";
var maxTokens = 64;
var temperature = 0.5;
// Build the API payload
var payload = {
'model': Model_ID, // or your desired model
'prompt': prompt,
'temperature': temperature,
'max_tokens': maxTokens,
};
We are going to start out by declaring the function and passing a prompt variable into it as a parameter. This parameter will taken from the Google Sheet once the code is complete.
This part of the code sets the parameters for the Open AI API request. We start out by declaring the desired model that we want to use. To know more about the models the Open AI API has check out the link below.
After declaring the model, we declare the number of tokens to generate in the response and then we declare the temperature to control the randomness of the response provided based on the prompt. To understand these properties better, check out the link below.
After this we build the payload that include the parameters that we just declared and the prompt provided by the user in the Google Sheet. This payload is then passed to the Open AI API which in return will generate an output by ChatGPT.
This code is the backbone of the integration between ChatGPT and Google Sheets, this will allow users to generate responses for prompts directly from their spreadsheets using the power of AI.
var options = {
"method": "post",
"headers": {
"Content-Type": "application/json",
"Authorization": "Bearer " + API_KEY
},
"payload": JSON.stringify(payload)
};The options variable is used to define the parameters for the HTTP request that will be sent to the Open AI API. We start out by declaring the method parameter to post which indicates that request will be sending data to the API.
The headers parameter specifies the type of data that will be sent in the request, which in this case is “application/json” which is a commonly used format for sending structured data over the internet. Additionally, the “Authorization” header is set to include the API key that we generated earlier.
Finally, the “payload” parameter is used to specify the data will be sent in the request. In this case it is a JSON object that contains the model ID, prompt, temperature and the max tokens for the API request.
These options are now passed as an argument to the “UrlFetchApp.fetch” function which sends the request to the Open AI endpoint, and returns the response that contains the AI generated text.
// Make the API request
var response = UrlFetchApp.fetch("https://api.openai.com/v1/completions", options);In this case the API endpoint is “https://api.openai.com/v1/completions" which is the endpoint for the ChatGPT API provided by OpenAI.
The options variable that we defined is passed as an argument to the fetch function to provide additionally parameters for the requests. The fetch functions then sends the request to the API endpoint and waits for a response from the server. The response from the server will contain the AI generated text that will be stored in the response variable.
// Parse the response and return the generated text
var responseData = JSON.parse(response.getContentText());
return responseData.choices[0].text.trim();Now that we have sent a request to the Open AI API endpoint we get a response back. We are now going to parse this response.
The getContentText() function is used to extract the text content from the response object. Since the response in JSON format, we use the JSON.parse function to convert the JSON string into an object.
The parsed data is then stored in the responseData variable, which contains an array of choices that the AI model has generated for us based on the prompt.
Since we are only interested in the first choice of the array, we use responseData.choices[0] to access that choice. We then use the .text to extract the text from the choice and finally use .trim function to remove any leading or trailing whitespace from the generated text.
The text is then returned to the calling function, which can use it to display the response to the user in the Google Sheet.
Our code is complete and good to go.
Step3: Check the output
Its time check the output and see if the code is working according to what we expected. To do that go ahead and save your code and run the GPT function.
On running the code you should get an output like this in the Execution Log.
Now that we have everything ready, lets go and use the custom function for the prompts we have in our Google Sheet.
Here I have used the GPT custom function that we created and passed a prompt into it. On clicking enter, you will see that the it has returned an perfect response to the prompt we just passed.
I am going to apply the function to get a response for all the prompts we have.
Here you can see it has generated responses for all the prompts we passed.
Conclusion
In conclusion, integrating AI in Google Sheets using the Open AI API and Google Apps Script can significantly boost productivity by automating various tasks that would otherwise take a lot of time and effort. With the power of ChatGPT at our disposal, we can generate complex responses with just a simple prompt, making it an invaluable tool for anyone who uses spreadsheets regularly. I hope you have understood how to integrate ChatGPT inside of Google Sheets using Google Apps Script. You can get the code from the GitHub link given below.
Feel free to reach out if you have any issues/feedback at aryanirani123@gmail.com.
