Update: This article was first published in 2021 when we were using GPT-3. At time of update we are now on GPT-4 Turbo. Everything you'll read here still works - you can just expect even better results for generative writing. Enjoy!
-
Bringing GTP-3 functionality into a Google Sheet will make you feel like Superman… well, if Superman was known for generating bespoke results faster than you can say, “generative pre-trained transformer three” that is. But you get the sentiment.
Simply write a prompt, add some example outputs and generate any output you desire, whether it’s copy for social media ads or python code that automates an SEO task. Honestly, whatever you can think of, it can be achieved.
Before we get into the how and the why, we recommend that you make a copy of our free Google Sheet template.
Right, all done? Let’s get into it.
How to get started (detailed instructions follow)
- Sign up to OpenAI and get a free API key
- Enter your API key into the Google Sheet tab titled “Settings”
- Define your prompts and desired outputs in the training sheet
- Use the formula =gpt(prompt, text)
Note: Select “save without formula” in the nav menu to remove the formula. This stops the formula – and thus the API – from running when you open and close the sheet.
Credits
The Google Sheet and this article was brought about in part due to some brilliant Google Sheets produced by the SEO expert Danny Richman. It was also inspired by Paul Katsen's example function demoed on Twitter in 2020.
Google Sheet template in action
GPT-3 has so many use cases from writing code and classifying text sentiment to powering chatbots and translating and summarising text. You name it, it can do it … and exceptionally well, too.
To give you an idea of how that may look – and to get you started – we have produced some examples below, which we’ve created using our free Google Sheet template.
Keyword classification
In the SEO world, we spend a lot of time performing keyword research and categorising keywords to identify opportunities for different themes.
This makes the keyword research more actionable – whether it’s to build a PPC campaign, optimise landing pages and so on – and it can help with prioritisation (search volume often gives us a steer on what keywords to prioritise). With GPT-3’s assistance, we can categorise keywords at scale.
Prompt: Extract the material listed in the keyword
Content generation
Another use case for GPT-3 is content generation. Generally, GPT-3 works well for smaller snippets of content, such as meta descriptions.
While the copy will probably need an edit, it will save you an incredible amount of time that would otherwise be spent on writing the descriptions.
At the moment, it will struggle to generate longer form content, especially if it isn’t backed by factual data (also known as a knowledge graph).
For example, if you create a prompt like “write about things to do in London” without providing GPT-3 with the actual things, it can go off course and, for example, write about a delightful fictitious beach in London that doesn’t even exist.
Prompt: Write a 100-word meta description with the keywords
Sentiment score
GPT-3 can be used for sentiment analysis and providing sentiment scores. One use case for this is in customer service, helping members of staff quickly identify and prioritise dealing with the most negative reviews.
Prompt: Classify the sentiment of the review between one and five (one being very unhappy and five being very happy)
How to use the Google Sheet
If you haven’t already, make a copy of the Google Sheet to get started. Once you’ve done that, open it up. You’ll see that it has three tabs:
- Settings
- Training
- Test
Settings
The Settings tab is where you input your OpenAI API. Do note that you will need to create an account with OpenAI and also get access to their API to be able to do this (you can start with a free trial and then pay as you go to access credits/tokens).
Training
The Training tab is where you add your text and create your prompt. Put your prompt in the top column of the table and add your desired output in the rows under it.
Below you can see the examples I have provided to produce the sentiment scores for reviews:
You typically want to provide at least three examples but no more than 10. Providing three examples should allow GTP-3 to “learn” the output you are after, while a maximum of 10 should be sufficient enough for the model to have a good grasp of the action you’re after.
If you’re still not getting the desired output with 10 examples, then it’s likely your prompt needs rewriting or you need to train a custom model.
Test
The Test tab allows you to use the prompt defined in the Training tab. Put the same prompt in the first column and apply the formula =gpt(prompt, text).
Lastly – and importantly – you need to replace the formulas within the cells with the actual values (Google and Excel will show you the value but when you click on the cell you’ll see the formula being used behind the scenes).
To remove these formulas, head to the nav menu and select ”save without formula” and then “save” as shown below.
How this Google Sheet works
Google Sheets does not have an out of the box =gpt() function. This sheet is something we have created using Google Apps Script.
This allows us to widen the functionality of Google Sheets by giving you access to the user interface (or more simply, the navigation), and allows you to create custom functions (among other things). If you navigate to “extensions” and then “apps script”, you can see the code behind our =gpt() formula.
The function we have created – named “gpt” – accepts a prompt and some text. Using this prompt, App Script does a look up in the Train tab to see examples of inputs and outputs.
It formats these examples alongside our prompt and makes a GET request to the OpenAI API (which basically allows our application to talk to OpenAI’s application).
For example, looking at the code block below, say we would like to extract the material listed in a keyword – we could format a prompt asking OpenAI to extract the material and give an example of what we would like it to return.
Note we have given it multiple examples to “learn” from and on the final line we have left the material blank to allow the API to return a response (hopefully “velvet”).
Extract the material from the keyword:
Keyword: grey leather sofa
Material: leather
Extract the material from the keyword:
Keyword: grey fabric sofa
Material: fabric
Extract the material from the keyword:
Keyword: grey velvet sofa
Material: velvet
Summary
This Google Sheet has huge potential to tackle a wide range of challenges. We have provided a few examples, but it could be used for nearly anything (it can, for instance, even do maths or write your App Script code for you).
With that being said, you may notice you don’t always get the output you’d expect. That is due to limited training data. In our Google Sheet, we have suggested only 3–10 examples.
To train a custom model we’d need a much bigger training set – and for that, Google Sheets is not the correct application.
Melt Digital has a solution for that. We use OpenAI to develop bespoke AI-powered models that are designed to tackle all sorts of business challenges – and identify new opportunities, too.
Get in contact with us for more information and a demo.
