Introducing ChatGPTApp, a new library for Google Apps Script

Last month, OpenAI announced Function calling, “a new way to more reliably connect GPT’s capabilities with external tools and APIs”. Super useful ❤️ and we decided to integrate that with Google Apps Script.

ChatGPTApp, a Google Apps Script library

The library is publicly accessible on GitHub, you can simply copy-past the code in your Google Apps Script project: https://github.com/scriptit-fr/ChatGPTApp.

Then it’s super simple to use:

1. Get an API key from openAI
2. Start a new chat with ChatGPTApp.newChat(), add a message / prompt and run()

A basic use of ChatGPTApp to send a prompt to openAI and retrieve the response.

Let openAI call your functions

The real beauty though, is when you start letting OpenAI / ChatGPT call other functions in your Google Apps Script project. You could for example create a very basic function to retrieve data from a Google Sheet and another function to send emails using GmailApp… and then ask OpenAI to retrieve a list of recipients and send them a personalized email... #mailMerge 😁

function sendCodingTipsByEmail() {
  ChatGPTApp.setOpenAIAPIKey(OPENAI_API_KEY);

  var getContactList = ChatGPTApp.newFunction()
    .setName("getContactList")
    .setDescription("Retrieve a list of contacts, including their name and email address");

  var sendMessageFunction = ChatGPTApp.newFunction()
    .setName("sendMessage")
    .setDescription("Send an email")
    .addParameter("recipientEmail", "string", "The email address of the recipient")
    .addParameter("subject", "string", "The email subject")
    .addParameter("htmlBody", "string", "The HTML email body");

  var resp = ChatGPTApp.newChat()
    .addMessage("Send a useful Google Apps Script coding tip to each of my contact.")
    .addFunction(getContactList)
    .addFunction(sendMessageFunction)
    .run();

  console.log(resp);
}

function getContactList() {
  return SpreadsheetApp.openByUrl(MY_CONTACTS_SS).getSheets()[0].getDataRange().getValues();
}

function sendMessage(recipientEmail, subject, htmlBody) {
  GmailApp.sendEmail(recipientEmail, subject, htmlBody, {
    htmlBody: htmlBody
  });
  return `Email sent to ${recipientEmail}`;
}

OpenAI will see that 2 functions are available, one to retrieve my contact database, the other to send emails. And thus it will decide by itself to call the first function, retrieve the result and then call 4 times the second function… because I have 4 contacts in my spreadsheet :)

The ChatGPTApp library automatically log useful events, like when openAI decides to call one of your functions.

… and then comes Browsing

A very useful feature launched with the “Plus” version of ChatGPT was the ability to browse the web to get more relevant responses. And we added that to ChatGPTApp!

Sadly Google does not provide a free and unlimited API to get web search results but it’s easy to create an API key. Once you have one, you can enable web browsing in the library.

OpenAI will generate a search query, send it to Google, retrieve the first results, open the url of the most relevant result, read the content of the web page and then provide a response.

Thanks to this library and a few line of additional Apps Script code, you can start drafting responses to emails of your choosing, retrieve info from the web to better answer questions, summarize Gmail conversations, check attendees availability in Calendar, and whatever you want that nicely mixes the power of generative AI with the simplicty of automating actions on Google Workspace with Google Apps Script.