Empowering Your Organisation with AI: Integrating Gemini Pro and PaLM2 into Google Sheets

A Comprehensive Guide to Using Gemini Pro and PaLM2 in Google Sheet on thousands of cells

Why Gemini Pro and PaLM2 in google sheet? Because it’s allow every one in the company to use genAI. Explore it’s capabilities and what they can do with it. This also enables you to make POCs very quickly before developing a more robust solution. Also spreadsheet is a format a the end that is usable for the organisation.

Why write another article on this subject?

Creating a Custom Function in Google Sheets to ask Gemini Pro

GenAI for Google Workspace: Exploring Gemini API Function Calling with Google Apps Script — Part 3

While there are existing articles on this topic, I want to extend a heartfelt thanks to all the authors who have written about it. Without their contribution this article would never have existed.

Simplifying API Key Management:

Managing API keys can be cumbersome in large organisations. Ideally, in Google Workspace, we want to use Google Cloud Platform’s IAM for easier management, avoiding the hassles of distributing and securing individual API keys.

Seamless Sheet Integration:

Typical integration methods, which often require adding custom buttons to each Google Sheet, are impractical for widespread use. A more efficient approach is a ‘plug-and-play’ solution, where simply adding a library to the sheet activates the functionality without additional setup.

Addressing API Limitations:

The Gemini and PaLM2 APIs limit the number of calls, conflicting with the Google Apps Script’s 30-second limit for custom functions. This means that the solutions can only be run on a few cells and not on large datasets. Highlighting the need for a more efficient solution.

Solutions for these problems

Avoiding API Keys and Custom Buttons

We circumvent the use of API keys by retrieving a user token that is used to call the API:

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

However, this code cannot be called by a custom function but only through an action initiated by the user or a trigger. To avoid creating a button on the sheet, we add this part to a custom menu that loads upon opening the sheet :

function onOpen() {
  PropertiesService.getDocumentProperties().setProperty(`start_genai_process_running`,"false");
  try {
    SpreadsheetApp.getUi().createMenu('GenAI')
        .addItem('Authenticate', 'auth')
        .addToUi();
  } catch (e) {
    console.log('Failed with error: %s', e.error);
  }
}

Custom menu in a spread sheet

Handling 429 Errors

After a few API calls, users often face these 429 errors, necessitating a rerun of the custom function to resume model interactions. To counter this, we can implement a retry mechanism that activates when encountering a 429 error. This system will attempt to resend the request, ensuring smoother and more consistent access to the API, even under heavy request loads.

const maxRetries = 15

for (let attempt = 1; attempt <= maxRetries; attempt++) {
  const response = UrlFetchApp.fetch(url, options);
  Logger.log(`Attempt: ${attempt}, Return Code: ${response.getResponseCode()}`)
  if (response.getResponseCode() == 200) {
    const json = JSON.parse(response.getContentText());
    return model === "gemini-pro" ? json[0].candidates[0].content.parts[0].text : json.predictions[0].content;
  } else if (response.getResponseCode() == 429) {
    if (attempt < maxRetries) {
      Logger.log(`Sleep before next attempt = ${baseDelay} ms`);
      Utilities.sleep(baseDelay);
    } else {
      return null;
    }
  } else {
    throw new Error(response);
  }
}

Now that we’re managing the 429s, we have a new problem. The execution time for custom functions in app script is limited to 30 seconds. So we no longer have a 429, but after a dozen or so cells have been executed, we have timeouts on the other cells.

How execute on thousands of cells

Now, with the retry system, we are not blocked by the API’s ‘429’ responses. However, two issues remain: the 30-second timeout for custom functions and the short lifespan of the token (few minutes) because the run of tens of thousands cells can takes hours.

To manage this, we will have a main process running continuously in the background which will be responsible for retrieving credentials on a regular basis and also for executing predictions cell by cell.

High level architecture of the process

When we use the custom function GEN_AI, it adds in a queue that the cell reference needs to be executed. The main process then loops to retrieve the cells where a prediction needs to be made and executes it.

In app script, executions are limited to 6 minutes. How can I get around this limit?

Restart process

To restart the process and avoid the limit of run of 6 minutes for a process in App scripts. We need to restart the process every 5 minutes approximatively. To do so after have done predictions for 5 minutes, the process create a trigger that will start a new process the next minutes, then stop. The new process will delete the trigger that create it to avoid another process to start.

To avoid having processes that run indefinitly if a process doen’t do a predition during 5 minutes it’s will not trigger another process and the generation will stop and show a warning to the next user

⚠️ Please start the process in menu GenAI -> Start GenAI if not already done

How install it :

Get the complete code : https://github.com/Remy-Larroye/gemini-palm-google-sheet. You can copy all the code in a single file in app script.

How install the solution

How to use it?

Now in your spreed sheet you can use the function GEN_AI which takes as arguments :

• prompt: The input prompt to send to Google VertexAI API. Typically a reference to a cell of the spreadsheet.
• project: The Google Cloud project ID on which you want to make requests (and where they will be billed). You must have the roles/aiplatform.user role on it.
• region: (Optional) The Google Cloud region where the GenAI project is hosted. Default is ‘us-central1’.
• temperature: (Optional) The temperature setting for the GenAI model, controlling the randomness of the output. Default is 0.1.
• model: (Optional) The specific GenAI model to use. Default is ‘gemini-pro’

Example on Gemini pro :

=GEN_AI(A2, "my-gcp-project-id", "us-central1", 0.1, "gemini-pro")

And with PaLM2 :

=GEN_AI(A3, "my-gcp-project-id", "us-central1", 0.1, "text-bison")

Troubleshooting

If you get a 401 error on your functions, check that :

• You’ve set a valide project_id in call of the GEN_AI function
• The VertexAI API (aiplatform.googleapis.com) is enabled on this project,
• You have the “roles/aiplatform.user” role on that project.

Conclusion

Image generate with DALL·E 3 then edited by Rémy Larroye

This solution offers a seamless integration of Gemini Pro and PaLM2 into Google Sheets, enabling organisations to harness the power of generative AI without the complexities of API management. It’s a testament to the evolving synergy between AI and everyday productivity tools, paving the way for more accessible and efficient data processing and analysis.

If you found this article helpful, please give it a clap, share it, and leave your comments below. For more content like this, don’t forget to subscribe. I publish new articles every 3 weeks on Medium!