You don't think of a Google Sheet as being the place where you'd want to have a conversation with ChatGPT. However, it can be useful to compare multiple chat personas side-by-side, pull in text snippets from other data sources, or just collaborate with your coworkers.
In this demo, we will build a live ChatGPT chat interface right inside a Google Sheet. The end result will looking something like this video – the ability to have a live chat in a specific style depending on the instructions it was given and how the user interacts with ChatGPT.
1. Initiate Chat
When representing the start of a chat, there may only be a single user message to provide. Consider the following example:
If you wish to have ChatGPT respond to the user message in cell A5, you would enter the following formula in cell B5:
This takes the single user message in A5 and sends it to ChatGPT, the response of which will then populate cell B5.
If you want ChatGPT to act with a specific personality or set of parameters, you can always provide that as the system_role parameter. In this example we've described how we want the assistant to act in cell A2 ("Act as a cheerful morning person").
We don't have any assistant message history yet, so we can leave the second parameter position empty:
We can see that the ChatGPT response has take on a very different personality now that we have told it to act cheerful:
2. Ongoing Chat
Initiating a chat is often quirte simple since there is really only one input, the user's initial message. Representing the rest of the conversation isn't that difficult, but does build upon the initial chat.
Continuing our chat start example, let's assume the user has responded with another message (in A6), and now we have to feed both the new message and our previous history in to ChatGPT so we can get a relevant response.
To do so, we will need to pass CHATGPT the range of users messages (A5:A6), the previous assistant message (B5), and our assistant role (again). We will put this formula in cell B6, where we want the next message in our sequence to go:
This results in a response that has used the previous chat history as input and responded with something contextually relevant:
If we want the B column to be able to automatically respond to user messages in the A column, we can tidy up our formula in B5 so it only invokes CHATGPT when there is a user message to respond to. Let's update B5 to this value, and then copy/paste it into the rest of the B column cells for a live chat interface within your sheet:
=IF(NOT(ISBLANK(A6)), CHATGPT(A$5:A6,B$5:B5,A$2), "")
Now, any user message added to the A column will automatically have a ChatGPT response in the B column.
You can see a fully implemented version of the ChatGPT Google Sheet here, which includes instructions for how to copy it to your account so you can engage in a chat yourself. This will require you to install SheetGPT, but we have a generous free tier which is sufficient for most non-professional uses.
You may find the following resources useful as well: