Abstract
It is considered that when the current error message of Google Apps Script is expanded, it will be useful for a lot of users. This report introduces a sample script for expanding the error message of Google Apps Script using Gemini Pro API with Google Apps Script.
Introduction
The recent release of the LLM model Gemini as an API on Vertex AI and Google AI Studio opens a world of possibilities. Ref and Ref
I believe Gemini API significantly expands the potential of Google Apps Script and paves the way for diverse applications. In this report, I present a sample script for expanding the error message of Google Apps Script using Gemini Pro API with Google Apps Script, and I would also like to mention the potential of the expanded error message using Gemini Pro API.
In the current stage, when an error occurs with Google Apps Script, a simple error message is returned. For example, when the following simple sample script for the container-bound script of Google Spreadsheet is used,
function onEdit(e) {
const { range } = e;
console.log(range.getA1Notation());
}
When a cell is edited, you can see the cell coordinate as a1Notation in the log by automatically executing onEdit with the simple trigger. However, when you directly run the function onEdit with the script editor, an error of TypeError: Cannot destructure property 'range' of 'e' as it is undefined.. The reason for this error is due to that the event object e is not given by directly executing the function. But, when I saw Stackoverflow, I saw several questions related to this error. From this situation, I thought that when more information is added to this error message, it might be useful for a lot of users. So, I published this report. When the method of this report is used, the error message by adding more information is shown. I believe that it will assist a lot of users in developing applications with Google Apps Script.
Usage
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://makersuite.google.com/app/apikey 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.
As additional information, of course, in this case, when you use the Google Apps Script project linked with the Google Cloud Platform, when Generative Language API is enabled at the API console, you can also use the access token retrieved by ScriptApp.getOAuthToken() instead of the API key. In this sample, the API key is used.
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 { scriptId, errorMessage } = e.parameter;
if (!scriptId || !errorMessage) {
return ContentService.createTextOutput("No script ID or an error message.");
}
try {
const url1 = `https://script.googleapis.com/v1/projects/${scriptId}/content`;
const res1 = UrlFetchApp.fetch(url1, {
headers: { authorization: "Bearer " + ScriptApp.getOAuthToken() },
});
let { files } = JSON.parse(res1.getContentText());
const script = files
.filter(({ type }) => ["SERVER_JS", "HTML"].includes(type))
.map(({ source }) => source)
.join("\n");
const text = `Read the following script and the error message. Return the reason for the error within 100 words.\nScript is as follows.\n${script}\nError message is as follows.\n${errorMessage}`;
const url2 = `https://generativelanguage.googleapis.com/v1beta/models/gemini-pro:generateContent?key=${apiKey}`;
const payload = { contents: [{ parts: [{ text }] }] };
const options = {
payload: JSON.stringify(payload),
contentType: "application/json",
};
const res2 = UrlFetchApp.fetch(url2, options);
const obj = JSON.parse(res2.getContentText());
if (
obj.candidates &&
obj.candidates.length > 0 &&
obj.candidates[0].content.parts.length > 0
) {
return ContentService.createTextOutput(
obj.candidates[0].content.parts[0].text
);
}
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
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.
}
function onEdit(e) {
try {
const { range } = e;
console.log(range.getA1Notation());
} catch (err) {
console.error(expandErrorMessage_(err));
}
}
In this sample script, when you edit a cell, you can see the cell coordinate of the edited cell in the log by automatically executing onEdit with the simple trigger.
When you directly run onEdit with the script editor, an error occurs. However, at that time, you can see both the original error message and the expanded error message in the log by the method in this report. You can see a demonstration at the top GIF image.
The expanded error message at the top image is as follows.
{
"errorMessage": "TypeError: Cannot destructure property 'range' of 'e' as it is undefined.\n at onEdit (Code:4:13)\n at __GS_INTERNAL_top_function_call__.gs:1:8",
"expandedErrorMessage": "The error occurs because the event object ('e') passed to the 'onEdit' function doesn't have a 'range' property. The 'range' property is expected to contain information about the edited range in the spreadsheet. Without the 'range' property, the script is unable to proceed with its intended operations."
}
If you want to obtain more details error message, please modify const text = `Read the following script and the error message. Return the reason for the error within 100 words.\nScript is as follows.\n${script}\nError message is as follows.\n${errorMessage}`; of the function doGet.
By the way, in the demonstration of the top image, the function expandErrorMessage_ is put into a script file of expandErrorMessage_.gs.
Also, in the case of the simple trigger, UrlFetchApp cannot be used. So, if you want to retrieve the expanded error message with the OnEdit trigger, please use the installable OnEdit trigger instead of the simple trigger.
Summary
From this result, I believe that Gemini API has the potential to be used for expanding the error message of the script.
Note
- In the current stage, Gemini Pro API can be requested 60 times in 1 minute as the default. Ref Please be careful about this.