Thanks for sharing Matt.
Right now it’s running at the text-davinci-003 model, I’ll definitely test out the new ChatGPT model as well and making changes on the default setting tab. Have you tested it out yet?
What is your experience in the new ChatGPT model?
I haven’t had a chance to play with ChatGPT via API, but I’ve done side-by-side comparisons with davinci-003 in API vs ChatGPT in browser and in our use cases it’s been night and day in response quality. I’ve been waiting for the day we can plugin ChatGPT instead.
I think for the sheets version you will probably want a new “Chat Completions” sheet and a new setting under Settings for the chat model. Reason is that it’s a slightly different API end point and a slightly different parameter format so the GPT3 and the chat models aren’t interchangeable. Then have a separate sheets function that accepts a range of cells rather than a single value, to correspond to the array that the API endpoint accepts.
Spitballing here, but that should work (I’ve never written scripts for sheets)
Hi everyone.
I have updated the Google Sheet + GPT3 to support the new ChatGPT API.
When running on the new ChatGPT API, you can reduce your cost by 10 times and get much safer responses, so I would highly recommend you to do the upgrade.
Also I have kept this google sheet in the same structure as the old one, so it should be be fairly easy if you are migrating from the old sheet to this new one. Good luck on your AI journey and please comment below and share your results. Cheers!
Great work Nelson!
Where can we find your updated version - would love to play around
Thanks for your kind words.
I updated the original link on the top.
But this is is newest version of the spreadsheet
1 Like
HI Nelson.
Thanks for making this sheet available for free here. Is it possible for you to make one that can send request to the GPT-4 Api ? Aslo how can I use this embeddings search to answer more complex questions that require information from more than I cell of embeddings to be able to answer the question ?
Hi @arjunsanyal9, how are you?
The Google Sheet should work with GPT4 if you change the model.
You can modify it on the setting tab, have you tried it?
See OpenAI API for reference.
I believe the embeddings search is already searching for all the rows in the data sheet.
Does that work for you? If not can tell me more about what you are trying to do?
Thanks.
Nelson
Hi Nelson,
Thanks for your reply. I meant that lets say on your sheet in the data tab there are few cells with info about John and i get embeddings for this in cell B . Now if my question is ( Can you tell me what is John’s age, where he lives and where he works ? ) Then the answer to this question will require information from all these 3 cells. So how can I retrieve information from more than 1 cell to answer this question?
cell A1 - John is 28 years old
Cell A2 - John lives in New Jersey
Cell A3 - John works for Microsoft
What you’re asking is precisely the nature of GPT Plugins. But it is possible to do this today if you shape the solution in a real-time fashion.
- Imagine an embeddings-based chatbot that uses vectors that point to answers (in a database).
- Imagine those answers also contain “pointers” to dynamic values related to that vector.
- Such “pointers” are actually API references to the latest values for these dynamic values.
- The ChatBot app uses embeddings to locate the best answer; it executes the API references, bundles all the data into a sensible prompt, and sends it to completion.
It’s not rocket science. It’s a simple matter of architecture and programming.
I’ve done it here using Coda and CustomGPT, and here using Mem, Airtable, and Pinecone.
And it’s even deeper. You’re touching on the DevOps side of employing AGI in actual business cases. But not DevOps in the traditional sense; this is training curation DevOps. 
The advent of usable AI models by domain experts has made obvious the need for operationally efficient patterns to help everyday domain experts and business people organize, curate, and transform their information into something AI models can digest. From Google sheets to Airtable, email messages, and PDFs - all of it will vie for inclusion into the future training sets we craft to put these new AGI tools to good use.
Consider the analytics you need to know if your corpus curation process is improving the experience. This is exactly the approach I used with Coda (instead of Google Sheets) to craft something useful for my team.
Your suggestion of a Chat Completions sheet and versioning attributes is critical. My Coda-based process factors all these variables into the workflow.
But the workflow also needs to support a way to test quickly and with an equal measure of refinement. Model curators should be able to quickly assess and change the corpus and then try to ensure there’s no regression.
Google Sheets is a good place to start, but my experience has shown it is not likely to carry us to the finish line. You need an environment that support content development with deep tentacles into other data sources. It needs to be as intelligent as the AI solutions you are trying to build. This has caused me to see a clear indication that GPT itself, is needed in many aspects of the corpus creation process.
Example, when I discover a poorly performing query for an FAQ system, I need to assimilate perhaps two other vectors that are performing well and which each contain parts of the correct answer. Using Coda’s relationship capabilities and OpenAI completions, I can automate the assimilation of a new answer that addresses the poorly-performing vector. This is made possible by using keyword extractions that align the other answers and completions that paint the new answer. With a few final edits in place, I now have a new vector and all the data needed to know that this new thing needs to be tested.
1 Like
Hi @arjunsanyal9
I see what you mean, for simplicity I only include one value initially, but you can customize it to fit your use case. Note this function here only pick the highest value, you can modify the code to use multiple values in the search results.
Do you want to modify this code on your sheet and try it out?
1 Like
Thanks again for your reply. Sorry I’m not a coder so can you tell me which parameters I can change in the code to be able to retrieve more than 1 value?
Hi @nelson, I discover this thread while I was myself building a script in google sheet to connect from gSheet to OpenAI.
The basic objective was to:
- rewrite a column of description
- generate keywords
- assign a business function related to the description.
Since I don’t know anything about coding, my “trainee” was chatGpt 4. However, after hours of prompting, it does not deliver the proper results yet, even if we are very close.
I don’t know if this code can be useful to the community; in case of it is, find below the .gs and .html script (or access to all via the gSheet file attached. Note the added on menu “AyS Script”).
-------------------------- [.gs script]
// Load OpenAI API library
function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu(‘
AyS Scripts’)
.addItem(‘Analyze Rows’, ‘showDialog’)
.addToUi();
}
function showDialog() {
var htmlOutput = HtmlService.createHtmlOutputFromFile(‘GptDialogDisplay’)
.setWidth(400)
.setHeight(300);
SpreadsheetApp.getUi().showModalDialog(htmlOutput, ‘Analyze Rows’);
}
function getColumnIndexByLetter(sheet, letter) {
var column = sheet.getRange(letter + “1”).getColumn();
return column;
}
function callOpenAiApi(promptTexts, openAiApiKey) {
var api_url = “https://api.openai.com/v1/engines/davinci/completions”;
var prompt_text = promptTexts.join(“\n\n”);
var data = {
“prompt”: prompt_text,
“max_tokens”: 1024,
“temperature”: 0.5,
“n”: promptTexts.length
};
var options = {
“method”: “post”,
“headers”: {
“Authorization”: "Bearer " + openAiApiKey,
“Content-Type”: “application/json”
},
“payload”: JSON.stringify(data)
};
return UrlFetchApp.fetch(api_url, options);
}
function prepareAnalysis(startRow, endRow) {
// Calculate the number of rows selected
var numRowsSelected = endRow - startRow + 1;
// Limit rows processed to a maximum of 20
var maxRows = 20;
if (numRowsSelected > maxRows) {
endRow = startRow + maxRows - 1;
numRowsSelected = maxRows;
}
// Update your API key in Project properties
var scriptProperties = PropertiesService.getScriptProperties();
var openAiApiKey = scriptProperties.getProperty(‘openai_api_key’);
return { numRowsSelected, openAiApiKey };
}
function fetchBatchResults(toolDescriptions, promptTemplate, numRowsSelected, openAiApiKey) {
var batchSize = 10;
var numBatches = Math.ceil(numRowsSelected / batchSize);
var allResults = ;
for (var batchIndex = 0; batchIndex < numBatches; batchIndex++) {
var startIndex = batchIndex * batchSize;
var endIndex = Math.min(startIndex + batchSize - 1, numRowsSelected - 1);
var batch = toolDescriptions.slice(startIndex, endIndex + 1);
var promptTexts = batch.map(function(description, index) {
return `AI Tool Description ${index + 1}: ${description[0]}\n`;
});
var prompt = promptTemplate.replace("{prompt_texts}", promptTexts.join(''));
var content = JSON.parse(callOpenAiApi([prompt], openAiApiKey).getContentText());
if (content.choices && content.choices.length > 0) {
var resultText = content.choices[0].text.trim();
var extractedDataArray = resultText.split("\n\n");
allResults.push(...extractedDataArray);
}
}
return allResults;
}
function processResults(sheet, allResults, startRow, htmlField1, htmlField2, htmlField3, rewrittenDescriptionColumnIndex, keywordsColumnIndex, criteria1ColumnIndex) {
allResults.forEach(function(extractedData, index) {
var rowIndex = startRow + index;
// Extract information
var extractedLines = extractedData.split("\n");
if (extractedLines.length >= 3) { // Add this check to ensure there are at least 3 lines
var rewrittenDescription = extractedLines[0].replace(new RegExp(`column ${htmlField1}: `), "");
var keywords = extractedLines[1].replace(new RegExp(`column ${htmlField2}: `), "").split(", ").join(", ");
var criteria1 = extractedLines[2].replace(new RegExp(`column ${htmlField3}: `), "");
// Set information in sheet with new column indexes
sheet.getRange(rowIndex, rewrittenDescriptionColumnIndex).setValue(rewrittenDescription);
sheet.getRange(rowIndex, keywordsColumnIndex).setValue(keywords);
sheet.getRange(rowIndex, criteria1ColumnIndex).setValue(criteria1);
} else {
Logger.log('Not enough information extracted for row: ' + rowIndex);
}
});
}
-------------------------------- [.html script]
Start row:
End row:
<!-- Indicate the destination columns -->
<label for="${htmlField1}">Rewritten Description column letter?:</label>
<input type="text" id="rewrittenDescriptionColumn" name="rewrittenDescriptionColumn" required><br><br>
<label for="${htmlField2}">Keywords column letter?</label>
<input type="text" id="keywordsColumn" name="keywordsColumn" required><br><br>
<label for="${htmlField3}">Criteria 1 column letter?</label>
<input type="text" id="criteria1Column" name="criteria1Column" required><br><br>
<div>
<button id="analyze" onclick="analyze()">Analyze Rows</button>
<button onclick="google.script.host.close()">Cancel</button>
</div>
<script>
function analyze() {
var startRow = document.getElementById('startRow').value;
var endRow = document.getElementById('endRow').value;
var rewrittenDescriptionColumn = document.getElementById('rewrittenDescriptionColumn').value;
var keywordsColumn = document.getElementById('keywordsColumn').value;
var criteria1Column = document.getElementById('criteria1Column').value;
if (startRow && endRow && startRow <= endRow && isValidColumn(keywordsColumn) && isValidColumn(criteria1Column) && isValidColumn(rewrittenDescriptionColumn)) {
google.script.run.analyzeRows(parseInt(startRow), parseInt(endRow), rewrittenDescriptionColumn, keywordsColumn, criteria1Column);
google.script.host.close();
} else {
alert('Please enter valid start-end rows or column letters');
}
}
function isValidColumn(column) {
return /^[A-Za-z]+$/.test(column);
}
</script>
1 Like
Hi @AyS0908
Thanks for sharing, do you mind send me a version of this in google spreadsheet.
I’ll like to take a tour into it.
Much appreciated.
1 Like
Sure, let me take a look if I can provide some example.
1 Like
FYI, this sheet has been modified to support GPT4 as well.
Make sure you change the model on the setting tab.
1 Like
Hello Nelson, thank you for sharing your spreadsheet, I’m trying to use the model: gpt-4-0314 and the model: gpt-4, but with both I get the following error:
Exception: Request failed for https://api.openai.com returned code 404. Truncated server response: { "error": { "message": "The model: gpt-4 does not exist", "type": "invalid_request_error", "param": null, "c... (use muteHttpExceptions option to examine full response) (line 98)
I believe GPT-4 is not available for all the accounts.
Have you request access yet?
Summary created by AI.
The users in this thread are discussing the combination of Google spreadsheets and GPT-3. nelson introduces his idea of combining a prompt and response from GPT-3 through a Google spreadsheet, providing a link and clear instructions on how to use it. He suggests users update their API key before using it and to not mistakenly share this key. He showcases the function of the spreadsheet with carefully annotated images and makes sure users understand how to link cells together in a workflow, modify values to get a GPT response, and use semantic search to call GPT.
The link to the older version of the spreadsheet with compatibility for the older models was also shared. Other users such as PaulBellow, lmccallum, and raymonddavey react positively and actively engage in the discussion.
The conversation also segues into the potential limitations of ChatGPT due to the lack of an API for it, as raymonddavey mentions. Despite these limitations, raymonddavey proposes potential workarounds, such as using placeholders in training and swapping them out with data from other services.
nelson provides further insights on how to approach the problem raised by peter.nemec, proposing integration of API feeds with the GPT API. This integration allows for customization and opportunities for pulling in real-time stock information into the Google spreadsheet. He demonstrates this interaction with images and shares another link to a new version that addresses issues with burning tokens.
Problems with the script are discussed by johnfaig, who reports his disagreement with the default API key, which remains active despite his API key being valid.
Finally, bill.french addresses the need for efficient patterns for domain experts to curate and transform information from various sources for AGI use. He shares his own workflows and experiences, emphasizing the importance of workflow support to quickly test and refine iterations without regression. He ultimately suggests that Google Sheets may be a starting point but further intelligent environments might be needed for more complex and integrated AI creation processes.
Summarized with AI on Nov 30 2023
AI used: gpt-4-32k