How to integrate Gemini and Sheets with BigQuery

I often find myself in Google Sheets. Some would say too often. Since I use Gemini for all kinds of things too, integrating Gemini into my Sheets workflow just makes sense.

I can boost my productivity in Sheets with capabilities like summarizing sheets and creating formulas. Gemini is now available in Gmail, Docs, Sheets and more with the Google AI One Premium plan. 

Building on top of these built-in features, let’s take a look at what’s possible with custom integration. For example, what if you wanted to use Gemini to generate or enrich multiple rows of data all at once?  In this blog post, we’ll walk through how to do just that.

Here’s what I’m visualizing. Using the Online Retail dataset, we can extract colors into a new column from product descriptions. This new column could help customers find what they’re looking for easily using a color filter.

Direct approach

Let’s explore how to invoke a Gemini API from Sheets. The key function in Google Workspace we’ll use is urlFetchApp, which can make HTTP requests or fetch web content. It can customize headers, setting timeouts, following redirects, and handling authentication

Using urlFetchApp, we can call the generateContent REST API, passing in the model and endpoint:

POST https://{service-endpoint}/v1/{model}:generateContent

All of the steps required are provided in this Google Sheets Custom Function. After you set up a service account and configure the Apps Scripts properties, you will have a custom function with two inputs, the range of cells to include in the prompt, and then the prompt.

=gemini(A1:A10,"Extract colors from the product description")

The function itself embeds these parameters into a prompt:

function gemini(range,prompt) {

  prompt = `For the table of data: ${range}, Answer the following: ${prompt}. Do not use formatting. Remove all markdown.`

  return getAiSummary(prompt);

}

Helper functions invoked by this function handle authentication and making the HTTP request.

If you’d like to see this in action, check out the Google Cloud Next 2024 session, Apps Script and Gemini: Build custom AI-powered Google Workspace solutions. There is also an AI Studio version of this script available.

Integration through BigQuery

Let’s now show how we can use BigQuery as a broker between Apps Script and Gemini. This is a robust approach suitable for large data sets, or where users are already integrating BigQuery and Sheets.

The Google Cloud BigQuery Apps Script service provides BigQuery objects and functions and the ability to execute queries directly in Sheets.

Using this service, BigQuery can make requests on entire columns of data directly to the Gemini Pro model in Vertex AI. All you have to do in BigQuery is create a model endpoint, specifying the model you’d like to use.

You can visualize the process like this:

Process overview

With this integration, we’ll create menu items linked to Apps Script functions, rather than a custom function. You can also create a function as we showed earlier, but we’ll explore a different style here.

We’ll start by looking at the raw input data. In our scenario, we want to extract the color from a product description:

Description (C2):

WHITE HANGING HEART T-LIGHT HOLDER

We’ll then create a prompt column that includes the instructions, concatenated with the input data.

ColorPrompt (I2):

"Extract the color(s) from the product description, all in capital letters... If there are multiple, return a valid comma separated list of colors (without the word and). Here is the product description: " & C2

Here’s an example response from this prompt, that we’ll place in the cell directly to the right of the prompt cell.

Color (J2):

WHITE

To make this happen, select “Query” from the menu with prompt cells selected:

How it works

The menu is created in the onOpen() trigger, and each of the menu items is linked to a function in Apps Script, a cloud-based JavaScript platform:

Menu Item	Functionality
Query	Query Gemini with the selected prompts, and place the responses in the column to the right.
Setup	Create the necessary artifacts in BigQuery to run the solution: dataset, temporary table, and model.
Configure > Temperature	Controls the degree of randomness in token selection; 0 is deterministic.
Configure > Max Output Tokens	The maximum number of tokens that can be generated in the response. A token might be smaller than a word and is approximately four characters.

The heart of the solution is BigQuery’s ML.GENERATE_TEXT function. From Apps Script, a cloud-based JavaScript platform, we can execute a SQL query with each of the prompts and configuration parameters:

const query = `SELECT * FROM ML.GENERATE_TEXT( MODEL \`${datasetId}.${modelId}\`, ` +

   `(SELECT * FROM \`${projectId}.${datasetId}.${tableName}\`), ` +

   `STRUCT(${getMaxOutputTokens()} AS max_output_tokens, ${getTemperature()} AS temperature));`

As the responses are returned in arbitrary order, we store them in a map, so that we can match them to the order of the original prompts. The prompts themselves are stored into a temporary table. We could directly include all values from the sheet into the query string, but using a table allows us to handle large datasets exceeding the maximum query length of 1MB. 

The dataset, table, and endpoint are configured in the setup() function. Within the project selected by the user, type:

const query = `CREATE MODEL \`${projectId}.${datasetId}.${modelId}\`\n` +

     `REMOTE WITH CONNECTION \`${projectId}.${location}.${connectionId}\`\n` +

     `OPTIONS(ENDPOINT = "${modelName}")`

Try it yourself

You can access the Apps Script code here. You can create a new sheet (sheet.new is a handy URL shortcut), and then add it via Extensions > Apps Script.  Then you’ll want to add the BigQuery API Service to your Apps Script project.

When you refresh your sheet, you’ll see the menu.

If you haven’t already, you’ll need to enable the BigQuery API. You’ll also need to create a BigQuery connection to Vertex AI. The provided code uses the connection ID genai-connection. 

The first time you run the code, you’ll be prompted to authorize Apps Script to access your Google Cloud project. You may also need to configure a service account to access Vertex AI. If it’s not configured, a dialog will provide the URL and the service account email address to make this a straightforward process.

Next steps

You’ve seen how querying Gemini directly from Sheets can make all kinds of new tasks possible. You can customize the integration code provided here for your own workflow.

For a deeper dive into using generative AI in BigQuery, check out the Bookshelf Analytics codelab. And if you’d like to learn more about Apps Script, the Fundamentals of Apps Script playlist will get you started.

By integrating Gemini models with Sheets, more users than ever can get AI insights and save time and effort on custom tasks.