Abstract
This report builds on prior work using Gemini 1.0 Pro to expand Google Apps Script error messages. It highlights how the script’s execution time limit created a bottleneck, but the introduction of Gemini 1.5 Flash eliminates this issue.
Introduction
After the release of the Gemini API, I previously reported on “Expanding Error Messages of Google Apps Script using Gemini Pro API with Google Apps Script”. Ref In that report, I utilized the Gemini 1.0 Pro model. While expanding error messages proved valuable for understanding script errors in detail, Google Apps Script currently has a maximum execution time of 6 minutes. Ref This meant that processing time for content generation by the Gemini API significantly impacted the total process time when dealing with large scripts, creating a bottleneck.
However, the introduction of Gemini 1.5 Flash eliminates this bottleneck. This report explores the advantages of using Gemini 1.5 Flash for expanding error messages in Google Apps Script.
Usage
In order to test this script, please do the following flow.
1. Create a standalone Google Apps Script project
Create a standalone Google Apps Script project. The filename is server. This Google Apps Script project is used as a server for using Google Apps Script API.
2. Open the “server” file and link “server” with the Google Cloud Platform
You can see how to link the Google Apps Script Project with the Google Cloud Platform Project at here.
3. Enable APIs
Please enable the following APIs at the API console.
- Google Apps Script API
4. Enable change of Google Apps Script project
Please access to https://script.google.com/home/usersettings using your browser and please change to “ON” for “Google Apps Script API” at “Settings”. When you have already done this, this process can be skipped.
5. Retrieve API key for using Gemini API
Please access https://ai.google.dev/gemini-api/docs/api-key and create your API key. At that time, please enable Generative Language API at the API console. This API key is used for this sample script.
This official document can be also seen. Ref.
6. Sample script for “server”
Please copy and paste the following script to the script editor of “server”, and please set your API key to apiKey.
function doGet(e) {
const apiKey = "###"; // Please set your API key.
const model = "models/gemini-1.5-flash-latest";
const version = "v1beta";
const { scriptId, errorMessage } = e.parameter;
if (!scriptId || !errorMessage) {
return ContentService.createTextOutput("No script ID or an error message.");
}
try {
// Retrieve scripts from a Google Apps Script project with Apps Script API.
const res1 = UrlFetchApp.fetch(
`https://script.googleapis.com/v1/projects/${scriptId}/content`,
{ headers: { authorization: "Bearer " + ScriptApp.getOAuthToken() } }
);
let { files } = JSON.parse(res1.getContentText());
const script = files.filter(({ type }) => ["SERVER_JS", "HTML"].includes(type)).map(({ name, source }) => `<filename:${name}>${source}</filename:${name}>`).join("\n");
// Upload scripts to Gemini with Gemini API.
const baseUrl = "https://generativelanguage.googleapis.com";
const metadata = { file: { displayName: `Script of "${scriptId}"` } };
const res = UrlFetchApp.fetch(
`${baseUrl}/upload/v1beta/files?uploadType=multipart&key=${apiKey}`,
{
method: "post",
payload: {
metadata: Utilities.newBlob(JSON.stringify(metadata), "application/json"),
file: Utilities.newBlob(script, MimeType.PLAIN_TEXT),
}
}
).getContentText();
const { file: { uri, mimeType } } = JSON.parse(res);
// Count tokens with Gemin API.
const text = [
`Read "script" of the uploaded file and the "errorMessage". Return the reason for the error within 100 words.`,
`<errorMessage>${errorMessage}</errorMessage>`,
].join("\n");
const payload = { contents: [{ parts: [{ text }, { text: "The below file is Google Apps Script." }, { fileData: { fileUri: uri, mimeType } }] }] };
const { totalTokens } = JSON.parse(UrlFetchApp.fetch(`${baseUrl}/${version}/${model}:countTokens?key=${apiKey}`, { payload: JSON.stringify(payload), contentType: "application/json" }).getContentText());
// Generate content with Gemin API.
const start = new Date().getTime();
const res2 = UrlFetchApp.fetch(
`${baseUrl}/${version}/${model}:generateContent?key=${apiKey}`,
{ payload: JSON.stringify(payload), contentType: "application/json" }
);
const diff = (new Date().getTime() - start) / 1000;
const obj = JSON.parse(res2.getContentText());
if (obj.candidates && obj.candidates.length > 0 && obj.candidates[0].content.parts.length > 0) {
const res = { res: obj.candidates[0].content.parts[0].text, diff, promptSize: text.length, totalTokens };
return ContentService.createTextOutput(JSON.stringify(res));
}
return ContentService.createTextOutput("No response.");
} catch ({ stack }) {
return ContentService.createTextOutput(stack);
}
}
7. Deploy Web Apps
Please deploy Web Apps on the “server” file.
The detailed information can be seen in the official document.
- On the script editor, at the top right of the script editor, please click “click Deploy” -> “New deployment”.
- Please click “Select type” -> “Web App”.
- Please input the information about the Web App in the fields under “Deployment configuration”.
- Please select “Me” for “Execute as”.
- Please select “Only myself” for “Who has access”.
- Please click “Deploy” button.
- Please run the function
getUr()of the above script. By this, you can see your Web Apps URL. It’s likehttps://script.google.com/macros/s/###/dev. Please copy this URL. This URL is used on the client side.
By this flow, the preparation is finished.
By using Google Apps Script API with Web Apps, all Google Apps Script projects in your Google Drive can be used. This approach is the same with this post.
8. Testing
By the above setting, you can configure Google Apps Script to display expanded error messages for all projects in your Google Drive. This can be incredibly helpful for debugging purposes, as expanded error messages often contain more specific information about the source of the error.
In order to test this script, please create a Google Spreadsheet and open the script editor of the created Spreadsheet. And, please copy and paste the following script to the script editor, please set your Web Apps URL to webAppsURL, and save the script.
function expandErrorMessage_(errorMessage) {
const webAppsURL = "https://script.google.com/macros/s/###/dev"; // Please set your Web Apps URL.
const res = UrlFetchApp.fetch(`${webAppsURL}?scriptId=${ScriptApp.getScriptId()}&errorMessage=${encodeURIComponent(errorMessage.stack)}`, { headers: { authorization: "Bearer " + ScriptApp.getOAuthToken() }, muteHttpExceptions: true });
if (res.getResponseCode() != 200) {
return { errorMessage: errorMessage.stack, expandedErrorMessage: null };
}
return { errorMessage: errorMessage.stack, expandedErrorMessage: res.getContentText() };
// DriveApp.getFiles(); // This comment line is used for automatically detecting a scope of "https://www.googleapis.com/auth/drive.readonly". This scope is used for requesting Web Apps. Please don't remove this comment line.
}
// This is a sample function for occuring an error.
function onEdit(e) {
try {
const { range } = e;
console.log(range.getA1Notation());
} catch (err) {
console.error(expandErrorMessage_(err));
}
}
When you directly run the function onEdit with the script editor, the following result is obtained.
The console output will contain both the original error message and the expanded error message retrieved from the server. Additionally, you might see values for diff, promptSize, and totalTokens. These values are used internally for processing time calculations (explained in a later section) and can be disregarded for now.
The following section will delve into the processing times for different Gemini models (1.0 Pro, 1.5 Pro, and 1.5 Flash) utilizing the diff and totalTokens. values.
Processing times for Gemini 1.0 Pro, 1.5 Pro, 1.5 Flash
Here, in order to compare the processing times for Gemini 1.0 Pro, 1.5 Pro, and 1.5 Flash, a chart is created from the values of diff and totalTokens.
This figure shows the change in processing times for Gemini 1.0 Pro (latest), 1.5 Pro (latest), and 1.5 Flash (latest) by changing the total tokens. The vertical axis (the processing time) and the horizontal axis (the total tokens) are the linear scale and the semi-logarithm scale, respectively. At Gemini 1.0 Pro, the maximum number of tokens is 30,000 by one API call. So, only the data of less than 30,000 tokens is shown.
From this figure, it is found that when the total tokens are increased, the processing times of all models are increased. However, you can see the differences in the trend of increase. It is found that the processing time of Gemini 1.5 Flash is the shortest of all. This result shows the predominance of using Gemini 1.5 Flash for expanding the error message of Google Apps Script.
Of course, this method can be used for other languages except for Google Apps Script. For example, when Python script is used, this method can be used without linking the Google Apps Script Project with the Google Cloud Platform Project.
Summary
In this report, a method for expanding the error messages in Google Apps Script is proposed. This improved error messaging can help developers identify and fix bugs more efficiently. Additionally, the report compares the processing times of different Gemini models (1.0 Pro, 1.5 Pro, and 1.5 Flash). The results indicate that Gemini 1.5 Flash is the fastest option, making it a good choice for tasks requiring high performance.
Note
- The top abstract image was created by giving this report to Gemini.