Creating a Custom Function in Google Sheets to ask Gemini Pro

With the announcement of Gemini available to developers, I am so excited to try it out. I must say I am very impressed about the performance of Gemini. There are plenty of reviews out there comparing Gemini Pro with GPT-3.5 and GPT-4 and it seems that Gemini Pro won in almost every test. As a daily user of Generative AI, my main concern is actually the pricing. Below is a quick comparison (using the converstion of 1 token = 4 characters). We could see that Gemini is almost the same price as GPT-3.5 Turbo and less than 10% of GPT-4 or GPT-4 Turbo. And guess what, currently Google does offer free access to Gemini Pro as long as the Query Per Minute (QPM) is less than 60.

Pricing Comparison (December 2023)

Anyway, this article is not to promote any product, it is some simple example of how we could use genAI in our daily tasks. Today, I will try to use genAI in Google Sheets to help me to create a custom function to ask Gemini the questions from other cells. This kind of requirement is actually very common. I will use the example below to demonstrate.

• Cell A1 is the template question
• Column A is the variables for the question
• Function askGemini(inputText) is a Custom Function that will send the question to Gemini Pro to get the answer

Note that this article assumes that you have some simple understanding of how App Script in Google Sheets works. But you can also follow the step without really understanding what it does (not recommended).

Create a Google Cloud project

If you don’t already have a GCP Project, you can use this link to create one. It is free and for the first 3 months you will have $300 credit to use in almost everything in Google Cloud.

Once you have the project created, make sure you have the Vertex AI API enabled. The link above should explain on how this is done too.

Authenticate in Google Sheets

Currently, most of the API calls to GCP from Google Sheets must be done via REST API. In other to do this, our first step is to get the OAuth2 token to authenticate ourselves.

Open Apps Script by going to Extensions → Apps Script.

In the Project Settings enable the option to show appsscript.json file.

Open the appsscript.json file and add the 3 oauthScopes below

"oauthScopes": [
          "https://www.googleapis.com/auth/spreadsheets.currentonly",
          "https://www.googleapis.com/auth/script.external_request",
          "https://www.googleapis.com/auth/cloud-platform"
        ]

In the default Code.gs file, create a new function auth(). In this function, we will request for the token and store in the cache as the Custom Function does not allow user authentication. The cache should keep the token for 10 minutes (default) or maximum 6 hours.

function auth() {
  cache = CacheService.getUserCache();
  token = ScriptApp.getOAuthToken();  
  cache.put("token", token);
}

Create a button or custom menu and assign the function auth() into it. In order for our custom function that calling Gemini to work, this button need to pressed to get a token.

We now need to use the function UrlFetchApp() to call the REST API. Below is the example of how it is done. Refer to this documentation on the options for the API. Basically, some of the compulsory values are the role where we need to put value “user” and parts where we will put the prompt.

function askGemini(inputText) {
  cache = CacheService.getUserCache();
  token = cache.get("token");
  if (token == "") return "ERROR";
  Logger.log(`Token = ${token}`);
  url = `https://${REGION}-aiplatform.googleapis.com/v1/projects/${PROJECT_ID}/locations/${REGION}/publishers/google/models/gemini-pro:streamGenerateContent`  
  data = {
    contents: {
      role: "USER",
      parts: { "text": inputText }
    },  
    generation_config: {
      temperature: 0.9,
      topP: 1        
    }
  }
  const options = {
    method: "post",
    contentType: 'application/json',   
    headers: {
     Authorization: `Bearer ${token}`,
    },
    payload: JSON.stringify(data)
  };

  const response = UrlFetchApp.fetch(url, options);
  if (response.getResponseCode() == 200) {
    json = JSON.parse(response.getContentText());
    answer = json[0].candidates[0].content.parts[0].text;
    return answer;
  }
  return "ERROR";
}

Save the code and it should be able to be used immediately, there are no need to deploy the project.

One thing to note about this Custom Fucntion is that it may be re-validated and it will call the API again and again. There are many way to stop that action, such as put an IF() to check if the value is not empty, we do not call the function again.

Hope this is some useful information for those just want to try out genAI and make it useful for their daily work.