Frequently immersed in the world of Google Sheets, some might say I spend too much time there. With Gemini at my disposal for various tasks, seamlessly integrating it into my Sheets workflow just makes perfect sense.
Enhancing my productivity within Sheets through features like summarization and formula creation is a breeze with Gemini, now accessible in Gmail, Docs, Sheets, and more with the premium Google AI One plan.
Going deeper into the potential of custom integration, imagine utilizing Gemini to enrich multiple rows of data simultaneously. In this blog post, we'll guide you through the process.
Picture this scenario: leveraging the Online Retail dataset to extract colors from product descriptions and populate a new column. This addition could greatly assist customers in easily locating desired items using color filters.
Discover the seamless process of calling a Gemini API from within Google Sheets. Utilizing the powerful urlFetchApp function in Google Workspace, you can effortlessly make HTTP requests, fetch web content, and tailor headers, timeouts, redirects, and authentication settings to suit your needs.
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.
Now, let's delve into how BigQuery can act as a bridge between Apps Script and Gemini, offering a sturdy solution for handling extensive datasets or for users already immersed in the integration of BigQuery and Sheets.
The Google Cloud BigQuery Apps Script service equips users with BigQuery tools and functions, enabling seamless query execution directly within Sheets.
With this service, BigQuery can effortlessly field requests across entire columns of data, feeding them directly into the Gemini Pro model within Vertex AI. All it takes in BigQuery is to establish a model endpoint, specifying the preferred model for use.
Visualizing the workflow, this process unfolds seamlessly, ensuring smooth data flow and efficient AI model utilization.
Let's dive into a new integration approach by linking menu items to Apps Script functions instead of a custom function. You can still create functions as previously shown, but this time, we’ll try something different.
First, we'll look at the raw input data. For our example, we want to extract the color from a product description.
Description (C2):
WHITE HANGING HEART T-LIGHT HOLDER
Next, we 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 what an example response from this prompt might look like, placed in the cell directly to the right of the prompt cell.
Color (J2):
WHITE
To execute this, simply select "Query" from the menu with the prompt cells selected.
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. |
BigQuery's ML.GENERATE_TEXT function is the core of our solution. By leveraging Apps Script, a cloud-based JavaScript platform, we can execute a SQL query with our prompts and configuration parameters seamlessly.
const query = `SELECT * FROM ML.GENERATE_TEXT( MODEL \`${datasetId}.${modelId}\`, ` +
`(SELECT * FROM \`${projectId}.${datasetId}.${tableName}\`), ` +
`STRUCT(${getMaxOutputTokens()} AS max_output_tokens, ${getTemperature()} AS temperature));`
The dataset, table, and endpoint are configured in the setup() function. Here’s how to configure your project:
const query = `CREATE MODEL \`${projectId}.${datasetId}.${modelId}\`\n` +
`REMOTE WITH CONNECTION \`${projectId}.${location}.${connectionId}\`\n` +
`OPTIONS(ENDPOINT = "${modelName}")`
To get started:
Upon refreshing your sheet, a new menu will appear.
The first time you run the code, authorize Apps Script to access your Google Cloud project. You might also need to configure a service account to access Vertex AI. If it’s not configured, a dialog will guide you with the necessary URL and service account email address.