Thanks for your help. It works now.
I made a copy of the Google Sheet from the link you just provided. I’m not sure if it was a previous version, but you need to do some serious editing to your sample formulas. For example, simply pressing [F2] to edit and pressing [ENTER] doesn’t cut it. I added a leading apostrophe to make it a text label and then removed it and that worked. In some cases, I’ve copied formulas around. It seems like Google Shhet might have some caching.
Good to know that you were able to get it up and running.
Cheers!
This sheet has been super helpful for our experiments. Just want to flag that it looks like it may need some updates to support the just recently updated ChatGPT. It seems the API endpoint is slightly different for those models.
FYI the docs for that are here, including how to use ChatGPT for the completion use case:
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
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.
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 generative AI 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 AI 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.
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?
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:
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]
<!-- 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>
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.
Sure, let me take a look if I can provide some example.
FYI, this sheet has been modified to support GPT4 as well.
Make sure you change the model on the setting tab.