Hi @Stephan_Vienna
Sorry for late answer, I suggest you share more details so I can help you better. Include your code, the more specific and detailed, the better I can help.
Mart 
Hi @Stephan_Vienna ,
Last week I was playing with inicial script from @mouimet , it looks like the endpoit to completion changed or this model is no longer available.
To overcome this I had to change:
- apiURL (to match my chosen model)
from
var apiURL = 'https://api.openai.com/v1/completions';
to
var apiURL = 'https://api.openai.com/v1/chat/completions';
- the payload, to use gpt3.5 model
from
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
});
to, for testing I’ve removed all parameters to keep it simple
var payload = JSON.stringify({
"model": "gpt-3.5-turbo", // Or another model
"messages": [
{"role": "system", "content": "You are a helpful assistant."},
{"role": "user", "content": `${promptText}`}
]
});
- response
from
var resultText = json.choices[0].text.trim();
to
var resultText = json.choices[0].message.content;
Done 
Here you can see my POC of this + some other features:
https://www.linkedin.com/posts/modzelewski-lukasz_openaiintegration-googlesheets-productivity-activity-7175185582128545792-YNTJ
Cheers,
Łukasz
2 Likes
Hi,
I’ve been trying to figure this out without a technical background. I don’t know if that’s the limitation, or if the tech isn’t there yet… maybe someone in this thread would be kind enough to let me know? I read over this thread and all over and I’m not sure if anyone has made what I want to happen, happen.
What I want is to have a chat interface with Google Sheets-- and the ability to add/change data in the sheet.
I can kind of get it to work with a Zap in my own GPT, but it’s clunky and limited and prone to mistakes. I want to be able to say to my sheet: Add a new assignment (column) named xyz in this category (think: gradebook for a teacher). It keeps adding columns to the end, not in the ‘category.’ Even when I explicitly tell it add new column after column H, it sticks it at the end of existing data. And when I tell it to add or change grades (add numbers under ‘assignment 1’)-- it can’t do it. At least with a zap I can’t get it to go.
I can prompt chatGPT to build and edit a sheet exactly how I want it-- but I need it to be Google Sheets, so I can edit and update it over time without downloading/uploading a csv every time I want to update the sheet. That’s just silly.
So is this something that can be done with the right technical skill or is this capability not yet available? Even Gemini chat in a Google Sheet won’t edit the sheet.
Thank you immensely.
A
1 Like
Thanks for this and everyone who contributed, it will be very useful. I modified the script slightly to allow selecting a range of cells instead of having to do one at a time. Also incorporating Lukasz updated code;
function onOpen() {
var ui = SpreadsheetApp.getUi();
// Adds a custom menu to Google Sheets.
ui.createMenu('OpenAI API')
.addItem('Get Response', 'getResponse')
.addToUi();
}
function getResponse() {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
const range = sheet.getActiveRange();
const numRows = range.getNumRows();
const numCols = range.getNumColumns();
for (let i = 1; i <= numRows; i++) {
for (let j = 1; j <= numCols; j++) {
const cell = range.getCell(i, j);
var promptText = cell.getValue();
if (promptText) {
// Replace YOUR_API_KEY with your actual OpenAI API key
const apiKey = '■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■onayRDQ8';
const apiURL = 'https://api.openai.com/v1/chat/completions';
const payload = JSON.stringify({
"model": "gpt-3.5-turbo-0125", // Or another model
"frequency_penalty": 0,
"presence_penalty": 0,
"messages": [
{"role": "system", "content": "You are a helpful assistant."},
{"role": "user", "content": `${promptText}`}
]
});
const options = {
"method": "post",
"contentType": "application/json",
"payload": payload,
"headers": {
"Authorization": "Bearer " + apiKey
},
"muteHttpExceptions": false
};
try {
const response = UrlFetchApp.fetch(apiURL, options);
const json = JSON.parse(response.getContentText());
const resultText = json.choices[0].message.content;
// 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.');
}
}
}
}
If it is possible, then for sure will be tricky
As far as I was playing with google app scripts: any new idea required creation of some UI elements or custom formulas … to execute functions. Each modification needed to be saved, then sheet needed to be refreshed to see the changes in scripts.
If you want to use chat in sheet, and expect chat to modify sheet, then chat would need to have full control over sheet and write & execute code. It would need to modify the scripts, which are already running… I’m not sure if that would be possible.
The other way would be defining needed functions in advance, then somehow execute those from the chat. But for this, I’m not sure if any AI is required (besides writing scripts) if you only need to execute function. Maybe some input field and button would be enough, or this could be an input for argument (generated by chat) to execution of some “ultra powerfull” function ^^
hint: watch out for costs, and consider batching: https://platform.openai.com/docs/guides/rate-limits/batching-requests
I had the same plan to go for the range, but decided to fucus on costs, check out my part 2 video:
Thank you! This works great for me and has saved a lot of time. Thank you to @lumo1987 and @mouimet too
Your project looks amazing on LinkedIn, I’d love to try it. Particularly the =ASKGPT function you created.
1 Like
@tomchurch Thanks for reaching out on LinkedIn! As promised, I have some exciting news… I’m teaming up with @mouimet to develop suit of small tools and features for Google Workspace. We both have some working solutions and ideas for new features
Before we go public with our MVP, we’ll be looking for 10-20 beta testers. We’ll announce this opportunity in a separate post, but if you’re interested, feel free to DM me already!
3 Likes
Hallo gents, this thread was very inspiring. I made the code working with a custom assistant.
I have a problem on the temperature that is used.
What is my use case: I made a simple assistant to classify data between categories.
Using the playground gives expected results. The temperature is set to 0.
When trying the same prompt from here I get very difficult results even plenty of hallucination (classifications that are missing in the knowledge).
I was looking for temperature setting but had limited success.
Is there anyone that can help?
Many thanks!
1 Like
set top_p to 0.001 and check if the results stick to the context.
Has anyone tried using it with Assistants V2?
can you share the code?
also you cant set temp to 0 it gose back to 2
u must set it to 0.00001
¿Noticias al respecto? Me encataria alguna solucion menos tortuosa en terminos de tiempo para google sheets 
1 Like