LinkedIn
Twitter
Multiple AI models are evolving. In the Google family, there are mainly "Gemini" and "Vertex" AI models.
@Stefan_QREW has already shared a beautiful tip on using Vertex AI.
This tip is about using Gemini API for leveraging Gemini AI model of Google. This tip also uses Google Apps Script (GAS) as an integration platform between Google AppSheet and Gemini API.
A bit of background and overview
Show More
As some of the community members must have already used Gemini AI directly embedded in Google Workspace components such as Google Docs, Google Sheets , Google Slides and so on.
Gemini for Google Workspace | Gen AI tools for business
Then why integrate with AppSheet?
1. With Gemini AI integration with AppSheet, we can enable the AI to analyse the app table data rather than an open-ended data.
2. The app user can call Gemini through an AppSheet app rather than accessing the backend Google sheets etc.
3. With this tip, the AppSheet app user can analyse the AppSheet app data stored in various app tables of the app.
The overview block diagram of the code implementation is as follows:
How it works
Show More
Below are 2 GIF to see the app in action. To save time , I have purposely created a few questions with enums as input queries. Once the app table to analyze is selected and the question asked the Gemini API responds in around 3/4 seconds with anser in the column [Gemini_Response]
The implementation:
The Google Apps Script is here:
Show More
const properties = PropertiesService.getScriptProperties().getProperties();
const geminiApiKey = properties['GOOGLE_API_KEY'];
const geminiEndpoint = `https://generativelanguage.googleapis.com/v1beta/models/gemini-1.0-pro-latest:generateContent?key=${geminiApiKey}`;
// The function gets two inputs in the form of two text variables- the prompt ( the text input from the AppSheet column [Gemini Query] )
// and the AppSheet table data as a string data in the form of variable productTableData
//The AppSheet table to convertthe table data into a string is selcted by an enum field that send the table name to be analyzed to the Apps script
//and then to the Gemini AI through Gemini API.
function callGemini(prompt,productTableData, temperature=0) {
const payload = {
"contents": [
{
"parts": [
{
"text": prompt+productTableData
},
]
}
],
"generationConfig": {
"temperature": temperature,
},
};
// The code block below computes the total token count that the AI query will have as an input
const baseUrl = "https://generativelanguage.googleapis.com";
const model = "models/gemini-1.0-pro-latest";
const version = "v1beta";
const payload1 = { contents: [{ parts: [ { text: prompt+productTableData },] }] };
const totalTokens = JSON.parse(UrlFetchApp.fetch(`${baseUrl}/${version}/${model}:countTokens?key=${geminiApiKey}`, { payload: JSON.stringify(payload1), contentType: "application/json" }).getContentText());
Logger.log(totalTokens);
const options = {
'method' : 'post',
'contentType': 'application/json',
'payload': JSON.stringify(payload)
};
const response = UrlFetchApp.fetch(geminiEndpoint, options);
const data = JSON.parse(response);
const content = data["candidates"][0]["content"]["parts"][0]["text"];
return content;
}
//The function below calls the function callGemini() coded above
// The output returned by the Gemini is returned back to Appsheet app using the "Apps Script tasks return value" option.
// The input tablename received is passed to the function below from the AppSheet app using enum field called [Select_Table]
// The function then converts the selected AppSheet table data into a string to be sent to the Gemini API as inline data.
function testGemini(prompt1, tablename) {
var prompt=prompt1;
//Get the spreadsheet data range. First access the spreadsheet by File ID and sheet name
var sheet = SpreadsheetApp.openById("SPREADSHEET_FILE_ID").getSheetByName(tablename);
//Get the current last row of the sheet
var lastRow = sheet.getLastRow();
//Get the entire table range
var dataRange = sheet.getRange(1, 1, lastRow, 8).getValues();
// Covert all data from the table into a single string or a structured format that the API can understand
var productTableData= dataRange.map(row => row.join(", ")).join("; ");
Logger.log(productTableData);
const output1 = callGemini(prompt, productTableData, temperature=0);
console.log(prompt, output1);
return output1;
}
Acknowledgements and references for the GAS code
Show More
The apps script code is mainly based on the Google code lab mentioned below. Of course, I have modified it to suit the requirements.
https://codelabs.developers.google.com/codelabs/gemini-workspace#3
For getting the token count of the inline data ( prompt plus AppSheet table data ) I have referred and adapted the code from the following article by the Google Apps Script expert Tanaike
The bot configuration that calls the Google Apps Script is here
Show More
The bot event triggers on the condition
AND([_THISROW_BEFORE].[Gemini_Query]<>[_THISROW_AFTER].[Gemini_Query] ,
ISNOTBLANK([_THISROW_AFTER].[Gemini_Query])
)
When the user enters his query ( or prompt) in the [Gemini_Query] field the bot triggers.
The step Run_Gemini calls the GAS , passed two fields [Select_Table] (table to analyze) and [Gemini_Query] or the prompt to prompt1 and tablename fields in the GAS.
The step is also enabled to process return values from the GAS
The step "Get_Return_Value" sends the output of the GAS (which is nothing but the answer) from the Gemini API back to AppSheet app field [Gemini_Response]
How to get it going in the app:
Show More
1. Please see the section 4 Call the Gemini API from Apps Script of the Google Codelabs article referenced below for steps to create Gemini API key and save it in Google Apps Script project.
https://codelabs.developers.google.com/codelabs/gemini-workspace#3
2. In an AppSheet app, include the following fields in any table, It should be typically a Users table with one row per user for user data so that each user's question/ answers to Gemini can be stored independently.
3. Code the GAS. The sample code shared above.
4. Set up the bot to process the GAS. The bot configuration is shared above.
Well, that is it.
A few points must to note out of my learnings on the AI topic
Show More
1. The above script and tip is only illustrative of how one can integrate AI into AppSheet apps. Please ensure you have implemented the recommended additional configurations as mentioned in the following and various other Gemini API related documentation by Google on Gemini API. This understanding s especially necessary for more serious use of the app outside test apps.
2. Please use and implement the Gemini API in your projects only after reading all the relevant Gemini API terms documents
The terms of use of API
https://ai.google.dev/gemini-api/terms
3.. Gemini Pricing : Please note the following features on Gemini models for free tier and paid tier.
We have used Gemini 1.0 Pro in this tipโs GAS.
3. Tokens :My testing showed that a 10 record , 8 column table generates around 650 tokens when passed as a serial data. So you can calculate the tokens your query is expected to consume and accordingly select pricing model or remain within the free tier. Of course please test thoroughly on POCs before implementing for large scale models.
4. In general AI configuration is also a large subject, with proper prompt engineering , selecting proper data sets for querying , setting proper temperature etc, is of vital importance for an optimized use of the model and more accurate answers.
Hope this helps.
