Hello! I am trying to build an Assistant API into Google Sheets. I have used Google Apps Script to create a textbox/side bar that users can submit messages through. I would like to direct these messages to my Assistant API and have it respond within the sidebar. However, I am having difficulty connecting the Google Sheets to my assistant API via Google Script. I am very new to using OpenAI’s APIs as well as Google Script so any direction would be greatly appreciated. If anyone could suggest a a preliminary block of code that could be used to to connect with an Assistant API through Google Script, it would really put me on the right track. Thanks!
Hi, did you make any progress with this? I want to do the same thing but there doesn’t seem to be any tool out there currently that can do this. All of the tools I’ve found only let you use the standard GPT models.
Same problem with different approach. I am looking in a way to call custom assistant via function.
Tell me what you need and I will provide the function.
I am looking for an app script that will take the content of another cell, prompt it to the assistant api, and fill the cell with the response. No context or conversation features are needed, each prompt is independent of the other.
Ideally I would have a custom function in google sheets that achieves this.
If you could provide this I would be most grateful, if anything is unclear let me know.
Creating a custom function in Google Sheets that interacts with the OpenAI Assistant API (or any external API) directly from a cell formula isn’t directly possible due to Google Sheets’ limitations. Custom functions in Sheets can only perform operations on the data within the spreadsheet and cannot call external APIs or perform HTTP requests.
However, you can achieve this functionality using Google Apps Script to create a custom menu in Google Sheets that triggers a script to send the content of a selected cell to the OpenAI Assistant API and then updates another cell with the response. Here’s a basic example:
Step 1: Open Google Apps Script
- Open your Google Sheet.
- Click on “Extensions” > “Apps Script”.
Step 2: Replace the Code
Delete any code in the script editor and replace it with the following:
function onOpen() {
var ui = SpreadsheetApp.getUi();
// Adds a custom menu to Google Sheets.
ui.createMenu('OpenAI API')
.addItem('Get Response', 'getResponse')
.addToUi();
}
function getResponse() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var cell = sheet.getActiveCell();
var promptText = cell.getValue();
if (promptText) {
// Replace YOUR_API_KEY with your actual OpenAI API key
var apiKey = 'YOUR_API_KEY';
var apiURL = 'https://api.openai.com/v1/completions';
var payload = JSON.stringify({
"model": "text-davinci-003", // Or another model
"prompt": promptText,
"temperature": 0.7,
"max_tokens": 150,
"top_p": 1,
"frequency_penalty": 0,
"presence_penalty": 0
});
var options = {
"method": "post",
"contentType": "application/json",
"payload": payload,
"headers": {
"Authorization": "Bearer " + apiKey
},
"muteHttpExceptions": true
};
try {
var response = UrlFetchApp.fetch(apiURL, options);
var json = JSON.parse(response.getContentText());
var resultText = json.choices[0].text.trim();
// Update the next cell in the row with the response
cell.offset(0, 1).setValue(resultText);
} catch (e) {
Logger.log(e.toString());
}
} else {
SpreadsheetApp.getUi().alert('Please select a cell with content.');
}
}
Step 3: Add Your API Key
Replace 'YOUR_API_KEY' with your actual OpenAI API key.
Step 4: Save and Close the Script Editor
After pasting and modifying the code, save the script and close the editor.
Step 5: Reload Your Spreadsheet
Reload your Google Sheets to see the custom menu (“OpenAI API”) appear.
Using the Script
- In your Google Sheet, select a cell that contains the text you want to send to the OpenAI API.
- Click on the “OpenAI API” menu and select “Get Response”.
- The script will send the cell’s content to the OpenAI Assistant API and populate the next cell in the same row with the response.
Note:
- This script uses the
UrlFetchAppservice to make a POST request to the OpenAI API, which requires enabling billing on your Google Cloud Project associated with the Apps Script. - The script does not implement conversation or context features; each request is independent.
- Remember to adhere to OpenAI’s API usage policies and guidelines.
Is that what you were looking for? Let me know if that helped.
Mart
4 Likes
Hi there
Sounds like it’s working for me.
It’s not very respectful towards somebody taking time to help you.
Have a good one,
Mart
2 Likes
Hi Mart,
You’re right, it’s not very respectful. Sorry. I thought you were some sort of karma farming robot.
That works with the normal openai api models, but I’m trying to use the assistants API, which I think uses a slightly different format than the standard models. It uses threads and messages.
Thanks for helping and sorry again.
Harry
No prob!
For assistant API it goes like this:
- Create thread
curl https://api.openai.com/v1/threads \
-H "Content-Type: application/json" \
-H "Authorization: Bearer $OPENAI_API_KEY" \
-H "OpenAI-Beta: assistants=v1" \
-d ''
- Create message in thread
curl https://api.openai.com/v1/threads/thread_abc123/messages \
-H "Content-Type: application/json" \
-H "Authorization: Bearer $OPENAI_API_KEY" \
-H "OpenAI-Beta: assistants=v1" \
-d '{
"role": "user",
"content": "How does AI work? Explain it in simple terms."
}'
- Run thread using assistant
curl https://api.openai.com/v1/threads/thread_abc123/runs \
-H "Authorization: Bearer $OPENAI_API_KEY" \
-H "Content-Type: application/json" \
-H "OpenAI-Beta: assistants=v1" \
-d '{
"assistant_id": "asst_abc123"
}'
Voila. Hope it helps.
Mart
4 Likes
Thanks a million, using that code I finally got it working. It seems custom functions in google sheets time out after 30 seconds and there’s no way around that, but around half of the time the function finishes before then. Next step is to work around this, I might just do this bit in excel as I’m not sure if it’s possible in sheets.
Below is my full code.
/**
* Custom Google Sheets function to interact with OpenAI's Assistant API.
* It creates a thread, sends the user's message, and retrieves the response in one go.
*
* @param {string} userInput The input from the user, taken from a cell in Google Sheets.
* @return {string} The response from the OpenAI Assistant.
* @customfunction
*/
function askOpenAIAssistant(userInput) {
var OPENAI_API_KEY = 'sk-*********************************'; // Set your OpenAI API key here
var ASSISTANT_ID = 'asst_********************'; // Set your Assistant ID here
var headers = {
'Authorization': 'Bearer ' + OPENAI_API_KEY,
'Content-Type': 'application/json',
'OpenAI-Beta': 'assistants=v1'
};
// Create a thread
var threadResponse = UrlFetchApp.fetch('https://api.openai.com/v1/threads', {
'method': 'post',
'headers': headers,
'muteHttpExceptions': true
});
if (threadResponse.getResponseCode() !== 200) {
return "Failed to create thread. Response: " + threadResponse.getContentText();
}
var threadData = JSON.parse(threadResponse.getContentText());
var threadId = threadData.id; // Adjust based on actual response format
// Send a message to the thread
var messageResponse = UrlFetchApp.fetch(`https://api.openai.com/v1/threads/${threadId}/messages`, {
'method': 'post',
'headers': headers,
'payload': JSON.stringify({
'role': 'user',
'content': userInput
}),
'muteHttpExceptions': true
});
if (messageResponse.getResponseCode() !== 200) {
return "Failed to send message. Response: " + messageResponse.getContentText();
}
// Run the thread using the assistant
var runResponse = UrlFetchApp.fetch(`https://api.openai.com/v1/threads/${threadId}/runs`, {
'method': 'post',
'headers': headers,
'payload': JSON.stringify({
'assistant_id': ASSISTANT_ID
}),
'muteHttpExceptions': true
});
if (runResponse.getResponseCode() !== 200) {
return "Failed to run thread. Response: " + runResponse.getContentText();
}
var runData = JSON.parse(runResponse.getContentText());
var runId = runData.id;
var status = runData.status;
var startTime = new Date().getTime();
// Poll the run status until it is 'completed'
while (status === 'queued' || status === 'in_progress') {
Utilities.sleep(500); // Wait for half a second before checking again
var checkResponse = UrlFetchApp.fetch(`https://api.openai.com/v1/threads/${threadId}/runs/${runId}`, {
'method': 'get',
'headers': headers,
'muteHttpExceptions': true
});
var checkData = JSON.parse(checkResponse.getContentText());
status = checkData.status;
// Check for timeout to avoid exceeding the execution limit
var currentTime = new Date().getTime();
if (currentTime - startTime > 29000) { // 29 seconds limit to be safe
return "Timeout waiting for the run to complete.";
}
}
// Once the run is completed, fetch the final result
if (status === 'completed') {
var messagesResponse = UrlFetchApp.fetch(`https://api.openai.com/v1/threads/${threadId}/messages`, {
'method': 'get',
'headers': headers,
'muteHttpExceptions': true
});
if (messagesResponse.getResponseCode() === 200) {
var messagesData = JSON.parse(messagesResponse.getContentText());
// Iterate over messages to find the assistant's response
for (var i = messagesData.data.length - 1; i >= 0; i--) {
var message = messagesData.data[i];
if (message.role === 'assistant' && message.content && message.content.length > 0) {
// Assuming the first content item contains the text response
var contentItem = message.content.find(c => c.type === 'text');
if (contentItem && contentItem.text && contentItem.text.value) {
return contentItem.text.value; // Return the text value of the assistant's message
}
}
}
return "Assistant's final response not found.";
} else {
return "Failed to fetch messages. Response: " + messagesResponse.getContentText();
}
} else {
return "Run did not complete successfully. Status: " + status;
}
}
1 Like
Try that may it is possible to overcome the limitation.
var lock = LockService.getScriptLock();
lock.waitLock(30000); // lock 30 seconds
}
Class Lock | Apps Script | Google for Developers
Quotas for Google Services | Apps Script | Google for Developers
Seems like function execution quota are 30 sec / execution
May I ask the reason why you are using Assistant API?
I have a table that has about 30 different rows, each describing a specific feature that needs to be met. I have 2 documents, about 10-20 pages each, outlining all of the features in a product. I have uploaded these 2 documents to my assistant, and given it specific instructions so that it takes in a feature as a prompt, uses RAG to search for that feature in the 2 documents, and returns the name of the section in the document where the feature is met. Its response is in a particular format that has been specified.
Using google sheets, I will drop in the table with each feature description as it’s own row, then simply run my custom function on every single row, using the feature description cell as the function input and the response from the Assistant API as the output of the function.
I work in a biomedical company and we have a lot of paperwork to get through, and this is a common chore. This tool will save us a lot of time.
My understanding is the lock helps manage concurrent usage of a script, but I don’t think running the function on multiple lines at the same time has any impact on performance, so that’s not the bottleneck. I think the bottleneck is just OpenAI.
So if I understand correctly, you want to match similar information from 2 documents and get back a reference of where it is located in each respectively, right?
I have 2 solutions, I’m pretty sure it will work.
- Use gpt-3.5 in your assistant, it’s going to speed up. It’s a small tweak
- When creating a run within google sheet, you don’t need to wait for it. It’s running on the back-end of OpenAI. Instead complete the execution keeping the threadId, runId, AssistantId.
Then, create another Google Sheet function to poll for the result. Create a trigger in Google Sheet to call that function every minute to get the result until it’s complete.
Here is how:
It is possible to trigger a Google Sheets function every minute using Google Apps Script. You can set up a time-driven trigger that executes a specific function at a one-minute interval. Here’s how you can do it:
Step 1: Open Google Apps Script
- Open your Google Sheets document.
- Click on
Extensions>Apps Script.
Step 2: Write Your Function
In the Apps Script editor, write the function that you want to execute every minute. For example:
function myFunction() {
// Your code here
Logger.log('This function runs every minute.');
}
Step 3: Create a Time-driven Trigger
After writing your function, you can create a time-driven trigger directly from the Apps Script editor. Here are two ways to do it:
Option 1: Through Current Project’s Triggers
- Click on the clock icon on the left panel in the Apps Script editor to open “Triggers.”
- Click on
+ Add Triggerat the bottom right corner. - Choose the function you want to run from the “Choose which function to run” dropdown.
- Select “Time-driven” from the “Select event source” dropdown.
- Select “Minutes timer” from the “Select type of time based trigger” dropdown.
- Choose “Every minute” from the “Select minute interval” dropdown.
- Click “Save.”
Option 2: Programmatically
Alternatively, you can create a trigger programmatically using Apps Script:
function createTimeDrivenTriggers() {
// Trigger every minute
ScriptApp.newTrigger('myFunction')
.timeBased()
.everyMinutes(1)
.create();
}
After creating this function, run createTimeDrivenTriggers once from the Apps Script editor to set up the trigger. You don’t need to run this function again unless you want to create a new trigger.
Important Considerations
- Quota Limits: Google Apps Script has daily quota limits, especially for triggers. Ensure that running a function every minute does not exceed these quotas, especially for accounts with a lot of scripts or heavy usage.
- Execution Time: Make sure that the function you intend to run every minute can complete its execution well within a minute to avoid overlapping executions or hitting execution time limits.
Setting up a function to trigger every minute is a powerful feature but use it judiciously to avoid unnecessary quota usage or performance impacts on your Google Sheets.
I’m pretty sure it’s gonna work fine like that. Let me know if you need further details.
Mart
1 Like
Thank you both so much! This helps my team immensely. We are testing stuff on google sheets and the key was the assistant. From my understanding the Assistents are more specified and capable models.
OK what I’ve done is made 2 different functions, as seen in the code below.
- startPrompt - sends the user’s input to the assistant API, and returns the threadId and runId
- getResponse - uses the output from startPrompt() to get the the assistant API’s response and returns it.
This means you can send the prompt with the first function, wait a few seconds yourself, then get the response with the 2nd function, avoiding any timeouts.
Unfortunately, sometimes I just get no response at all from the assistant API and I’m not sure why, even after waiting over a minute. Only happens about 5% of the time.
var OPENAI_API_KEY = 'sk-**************************************';
var ASSISTANT_ID = 'asst_**************************';
var headers = {
'Authorization': 'Bearer ' + OPENAI_API_KEY,
'Content-Type': 'application/json',
'OpenAI-Beta': 'assistants=v1'
};
/**
* Starts an interaction with the OpenAI Assistant and returns the thread and run IDs.
*
* @param {string} userInput The input from the user, taken from a cell in Google Sheets.
* @return {string} A string with the threadId and runId for tracking.
* @customfunction
*/
function startPrompt(userInput) {
// Create a thread
var threadResponse = UrlFetchApp.fetch('https://api.openai.com/v1/threads', {
'method': 'post',
'headers': headers,
'muteHttpExceptions': true
});
if (threadResponse.getResponseCode() !== 200) {
return "Failed to create thread. Response: " + threadResponse.getContentText();
}
var threadData = JSON.parse(threadResponse.getContentText());
var threadId = threadData.id;
// Send a message to the thread
var messageResponse = UrlFetchApp.fetch(`https://api.openai.com/v1/threads/${threadId}/messages`, {
'method': 'post',
'headers': headers,
'payload': JSON.stringify({
'role': 'user',
'content': userInput
}),
'muteHttpExceptions': true
});
if (messageResponse.getResponseCode() !== 200) {
return "Failed to send message. Response: " + messageResponse.getContentText();
}
// Run the thread using the assistant
var runResponse = UrlFetchApp.fetch(`https://api.openai.com/v1/threads/${threadId}/runs`, {
'method': 'post',
'headers': headers,
'payload': JSON.stringify({
'assistant_id': ASSISTANT_ID
}),
'muteHttpExceptions': true
});
if (runResponse.getResponseCode() !== 200) {
return "Failed to run thread. Response: " + runResponse.getContentText();
}
var runData = JSON.parse(runResponse.getContentText());
var runId = runData.id;
// Return threadId and runId for later use
return "Successful," + threadId + "," + runId;;
}
/**
* Parses the output from startPrompt to check the status and retrieve the OpenAI Assistant's response.
*
* @param {string} input The concatenated threadId and runId from the first function.
* @return {string} The response from the OpenAI Assistant or a status update.
* @customfunction
*/
function getResponse(input) {
var ids = input.split(","); // Split the input string to extract threadId and runId
var threadId = ids[1];
var runId = ids[2];
var attempt = 0;
var maxAttempts = 25; // Set a max limit to prevent infinite loop
var delayBetweenAttempts = 1000; // Delay in milliseconds (e.g., 5000 ms = 5 seconds)
while (attempt < maxAttempts) {
var messagesResponse = UrlFetchApp.fetch(`https://api.openai.com/v1/threads/${threadId}/messages`, {
'method': 'get',
'headers': headers,
'muteHttpExceptions': true
});
if (messagesResponse.getResponseCode() !== 200) {
return "Failed to fetch messages. Response: " + messagesResponse.getContentText();
}
var messagesData = JSON.parse(messagesResponse.getContentText());
for (var i = messagesData.data.length - 1; i >= 0; i--) {
var message = messagesData.data[i];
if (message.role === 'assistant' && message.content && message.content.length > 0) {
var contentItem = message.content.find(c => c.type === 'text');
if (contentItem && contentItem.text && contentItem.text.value) {
Logger.log("Success");
Logger.log(JSON.stringify(messagesData));
return contentItem.text.value; // Return the text value of the assistant's message
}
}
}
// If the assistant's response is not found, wait before trying again
Utilities.sleep(delayBetweenAttempts);
attempt++;
Logger.log("Attempting to fetch response again. Attempt #" + attempt);
}
Logger.log("Assistant's final response not found after " + maxAttempts + " attempts.");
Logger.log(JSON.stringify(messagesData));
return "Assistant's final response not found after maximum attempts.";
}
1 Like
Maybe trying to set that false will help troubleshoot.
'muteHttpExceptions': true
Hello, can u please help me to bypass the 30-second limit in Google Sheets? Using any method, is it possible?
No way of doing it as far as I can tell, even with a pro account. Not sure if there is a similar limitation on excel so that could be an option.
Hey,
thanks mouimet for sharing your tremendous work!
I ran into a problem to make it work, although I think I stick to your explanation.
When running the script, i get back “Finished script” but the next cell is still empty.
The API key has all Permissions, the GCP connection also worked as far as i could tell and has billig details added.
I am not sure why its not giving me some output.