Leveraging GCP for Seamless Google Apps Script Log Export and Analysis with Gemini API
Abstract
Linking a Google Apps Script project to a GCP project enables you to export logs from the Class console to Logs Explorer for simplified analysis and debugging. By overcoming the limitations of in-script logging methods, this report outlines a method for exporting logs using the Cloud Logging API with Google Apps Script.
Introduction
While developing applications with Google Apps Script, the Class console is a valuable tool for debugging individual components. Ref However, a key limitation exists: by default, Google Apps Script projects on Google Drive are not linked to Google Cloud Platform (GCP) projects. In this unlinked scenario, logs from the Class console are only visible within the script editor, requiring manual copying for export.
Linking a Google Apps Script project to a GCP project unlocks the power of Logs Explorer. Ref Logs generated using the Class console become readily accessible in Logs Explorer, allowing for effortless export via the Cloud Logging API. Ref This exported log data proves invaluable for script refactoring and analysis.
While in-script options exist for exporting logs to files or Spreadsheets, these methods have limitations. Script execution errors like “Exceeded maximum execution time” can prevent successful in-script logging. Therefore, the ability to export logs after script execution, regardless of success or failure, offers a significant advantage.
This report delves into the process of exporting logs from functions within Google Apps Script, leveraging the capabilities of GCP. Such information will be useful for evaluating the script by Gemini API and for refactoring the script.
Process
In order to retrieve the logs from an execution of a function, the following process is run. In this process, it supposes that the logs of this function sample() of Google Apps Script are retrieved.
- Run a function (
sample()) of Google Apps Script. - In a separate call, after the function
sample()has finished, use the “Method: processes.list” of the Apps Script API to retrieve a list of all currently running processes. This list can be used to identify the start and end times of the specific execution ofsample()just after the execution. Ref - Using the retrieved start and end times from step 2, retrieve the logs of the executed function
sample()with the “Method: entries.list” of Cloud Logging API. Ref
By following this flow, you can obtain the logs specifically for the executed function sample().
As another approach for retrieving logs, logs can also be exported to a file or Spreadsheet while the function is running. However, this method has limitations: logs might not export correctly if the function runs for more than 6 minutes, and exporting logs after every line can significantly increase processing costs. Due to these issues, I opted for the previously described method.
Usage
1. Create Google Apps Script project
In order to use the following sample scripts, please create a new Google Apps Script project. Ref In this case, the standalone script is used. Of course, the container-bound script can also be used.
2. Linking Google Cloud Platform project to Google Apps Script project
Please link the Google Cloud Platform project to the Google Apps Script project. You can see how to do it at my repository. Ref
3. Enable APIs
Please enable Apps Script API and Cloud Logging API at API console.
4. Scripts
Main script
Please copy and paste the following script to the script editor and save the script.
This is the main script. This function getLogs_ retrieves the logs from the executed function using Apps Script API and Cloud Logging API. In this sample, by inputting the function name, the function getLogs_ retrieves the logs of the function of the inputted function name which was run last executed.
/**
* ### Description
* Retrieve the processing logs from a function using Apps Script API and Cloud Logging API.
*
* @param {Object} object Object for running this method.
* @param {String} object.functionName Function name of the function you want to retrieve processing logs.
* @param {Array} object.projectIds Project ID of the Google Cloud Platform Project. In the current stage, only one ID can be used.
*
* @return {Object}
*/
function getLogs_(object) {
const { functionName, projectIds } = object;
const projects = projectIds.map(p => `projects/${p}`);
const logNames = projects.map(p => `logName="${p}/logs/script.googleapis.com%2Fconsole_logs"`).join(" OR ");
const headers = { authorization: "Bearer " + ScriptApp.getOAuthToken() };
// Retrieve start and end times of the function.
const scriptId = ScriptApp.getScriptId();
const url1 = `https://script.googleapis.com/v1/processes?userProcessFilter.scriptId=${scriptId}&userProcessFilter.functionName=${functionName}&pageSize=1`;
const res1 = UrlFetchApp.fetch(url1, { headers });
const obj1 = JSON.parse(res1.getContentText());
if (obj1.processes.length == 0) return;
// Retrieve logs.
const { startTime, duration } = obj1.processes[0];
const endTime = new Date(new Date(startTime).getTime() + (Number(duration.replace("s", "")) * 1000)).toISOString();
const filterAr = [
`resource.type = "app_script_function"`,
`timestamp >= "${startTime}" AND timestamp <= "${endTime}"`,
`(${logNames})`,
];
const filter = filterAr.join(" AND ");
const values = [];
let pageToken = "";
const payload = { resourceNames: projects, filter, pageSize: 1000 };
do {
payload.pageToken = pageToken;
const url2 = "https://logging.googleapis.com/v2/entries:list";
const options = { method: "post", contentType: "application/json", headers, payload: JSON.stringify(payload) };
const res2 = UrlFetchApp.fetch(url2, options);
const obj2 = JSON.parse(res2.getContentText());
values.push(...obj2.entries.map(({ jsonPayload: { message }, timestamp, severity }) => ({ message, timestamp, severity })));
pageToken = obj2.nextPageToken;
} while (pageToken);
return values;
}
/**
* ### Description
* Parse logs from getLogs_. This is used for parsing the values from getLogs_.
*
* @param {Array} logs Logs from getLogs_.
* @return {Object}
*/
function parseLogs_(logs) {
return logs.reduce((o, e) => {
const { message } = e;
const [l, t] = message.split(":");
if (t) {
const time = Number(t.replace("ms", ""));
if (l.includes("_")) {
const [p, i] = l.split("_");
if (!o.logs[p]) {
o.logs[p] = [];
}
o.logs[p][i] = time;
} else {
o.logs[l] = o.logs[l] ? [...o.logs[l], time] : [time];
}
} else {
o.messages = [...o.messages, e];
}
return o;
}, { messages: [], logs: {} });
}Sampe scripts
Sample script 1
Please copy and paste the following script to the script editor and directly run sampleFunction1() with the script editor. If a dialog for authorizing the scopes, please authorize them.
function sampleFunction1() {
const labelPrefix = "label";
const label1 = `${labelPrefix}1`;
const label2 = `${labelPrefix}2`;
const label3 = `${labelPrefix}3`;
const label4 = `${labelPrefix}4`;
console.time(label1);
console.time(label2);
Utilities.sleep(1000);
console.timeEnd(label2);
console.time(label3);
for (let i = 0; i < 5; i++) {
const label4a = `${label4}_${i}`;
console.time(label4a);
Utilities.sleep(100 * (i + 1));
console.timeEnd(label4a);
}
console.timeEnd(label3);
console.timeEnd(label1);
}In order to measure the processing time of each part of the script, console.time and console.timeEnd are added. In this sample, as the format of the label, if the single processing time of a script, label1, label2,,, are used. If the multiple processing time of a script in a loop, label1_1, label1_2,,, are used.
When this script is run, you can see the following logs in “Execution log” of the script editor.
Notice Execution started
Debug label2: 1001ms
Debug label4_0: 101ms
Debug label4_1: 201ms
Debug label4_2: 301ms
Debug label4_3: 401ms
Debug label4_4: 501ms
Debug label3: 1511ms
Debug label1: 2515ms
Notice Execution completedAnd, please copy and paste the following script to the script editor and directly run it with the script editor.
function main() {
const functionName = "sampleFunction1";
const projectIds = ["###"]; // Please set your project ID created with GCP.
const logs = getLogs_({ functionName, projectIds });
console.log(logs);
}When this script is run, the following result is obtained.
[
{ "message": "label2: 1001ms", "timestamp": "###", "severity": "DEBUG" },
{ "message": "label4_0: 101ms", "timestamp": "###", "severity": "DEBUG" },
{ "message": "label4_1: 201ms", "timestamp": "###", "severity": "DEBUG" },
{ "message": "label4_2: 301ms", "timestamp": "###", "severity": "DEBUG" },
{ "message": "label4_3: 401ms", "timestamp": "###", "severity": "DEBUG" },
{ "message": "label4_4: 501ms", "timestamp": "###", "severity": "DEBUG" },
{ "message": "label3: 1511ms", "timestamp": "###", "severity": "DEBUG" },
{ "message": "label1: 2515ms", "timestamp": "###", "severity": "DEBUG" }
]When the following script is used,
function main() {
const functionName = "sampleFunction1";
const projectIds = ["###"]; // Please set your project ID created with GCP.
const logs = getLogs_({ functionName, projectIds });
const obj = parseLogs_(logs);
console.log(obj);
}the following result is obtained.
{
"messages": [],
"logs": {
"label2": [1001],
"label4": [101, 201, 301, 401, 501],
"label3": [1511],
"label1": [2515]
}
}You can analyze the process cost of the script from this result.
Sample script 2
Please copy and paste the following script to the script editor and directly run sampleFunction2() with the script editor. If a dialog for authorizing the scopes, please authorize them.
function sampleFunction2() {
const labelPrefix = "label";
const label1 = `${labelPrefix}1`;
const label2 = `${labelPrefix}2`;
const label3 = `${labelPrefix}3`;
const label4 = `${labelPrefix}4`;
console.time(label1);
console.time(label2);
Utilities.sleep(1000);
console.timeEnd(label2);
console.time(label3);
for (let i = 0; i < 5; i++) {
const label4a = `${label4}_${i}`;
console.time(label4a);
Utilities.sleep(100000 * (i + 1));
console.timeEnd(label4a);
}
console.timeEnd(label3);
console.timeEnd(label1);
}Notice Execution started
Debug label2: 1001ms
Debug label4_0: 100002ms
Debug label4_1: 200001ms
Error Exceeded maximum execution timeAnd, please copy and paste the following script to the script editor and directly run it with the script editor.
function main() {
const functionName = "sampleFunction2";
const projectIds = ["###"]; // Please set your project ID created with GCP.
const logs = getLogs_({ functionName, projectIds });
console.log(logs);
}When this script is run, the following result is obtained.
[
{ "message": "label2: 1001ms", "timestamp": "###", "severity": "DEBUG" },
{ "message": "label4_0: 100002ms", "timestamp": "###", "severity": "DEBUG" },
{ "message": "label4_1: 200001ms", "timestamp": "###", "severity": "DEBUG" },
{
"message": "Exceeded maximum execution time",
"timestamp": "###",
"severity": "ERROR"
}
]It was found that the error message due to over-execution time could also be obtained.
When the following script is used,
function main() {
const functionName = "sampleFunction2";
const projectIds = ["###"]; // Please set your project ID created with GCP.
const logs = getLogs_({ functionName, projectIds });
const obj = parseLogs_(logs);
console.log(obj);
}the following result is obtained.
{
"messages": [
{
"message": "Exceeded maximum execution time",
"timestamp": "###",
"severity": "ERROR"
}
],
"logs": {
"label2": [1001],
"label4": [100002, 200001]
}
}In this result, only “label2” and “label4” have the values. From this result, it is found that the script is stopped before the loop is finished.
Sample script 3
This script sample generates a more detailed evaluation of the script using logs retrieved by the getLogs_ function and the Gemini API. By providing the script’s logs, we can expect a more comprehensive evaluation.
The script utilizes the following functions: getLogs_, sampleFunction1, and sampleFunction2 shown in the above sections. Additionally, it leverages the GeminiWithFiles library from Google Apps Script to generate the evaluation.
For testing, please install the GeminiWithFiles library from the following repository: Ref
/**
* ### Description
* Generate evaluation of the script using Gemini.
*
* @param {Object} object Object for running this method.
* @param {String} object.apiKey API key for using Gemini API.
* @param {String} object.functionName Function name of script.
* @param {Object} object.logs Logs when the script is run.
*
* @return {String} Generated evaluation of the script using Gemini.
*/
function generateEvaluation_(object) {
const { apiKey, functionName, logs } = object;
const scriptFilename = `${functionName}.gs`;
const logFilename = `log for ${functionName}.txt`;
const scriptBlob = Utilities.newBlob(this[functionName].toString(), MimeType.PLAIN_TEXT, scriptFilename);
const logBlob = Utilities.newBlob(JSON.stringify(logs), MimeType.PLAIN_TEXT, logFilename);
const jsonSchema = {
description: "Processing log. This log shows the processing time of each part in the script.",
type: "array",
items: {
type: "object",
properties: {
message: { type: "string", description: "Message from except for console.time and console.timeEnd." },
timestamp: { type: "string", description: "Time stamp of the message." },
severity: { type: "string", description: "Type of message." },
}
}
};
const q = [
`You are required to evaluate the Google Apps Script.`,
`In order to evaluate the script, 2 text files are uploaded as follows.`,
`1. Google Apps Script. Filename is ${scriptFilename}.`,
`2. Log data for the Google Apps Script. Filename is ${logFilename}.`,
`The script of ${scriptFilename} has "console.time' and 'console.timeEnd'.`,
`Those are used for measuring the processing time of each part.`,
`The log of ${logFilename} is an object. The JSON schema of this object is as follows.`,
`<JSONSchema>${JSON.stringify(jsonSchema)}</JSONSchema>`,
`Please evaluate this Google Apps Script using the log data.`,
`Also, please clearly describe the problems points and the improvement points.`,
`Export the result by summarizing the generated description within 100 words without a markdown.`,
].join("\n");
const g = GeminiWithFiles.geminiWithFiles({ apiKey });
const fileList = g.setBlobs([scriptBlob, logBlob]).uploadFiles();
return g.withUploadedFilesByGenerateContent(fileList).generateContent({ q });
}When you want to generate the evaluation of the function sampleFunction1, you can use the following script.
function sample3() {
const functionName = "sampleFunction1";
const projectIds = ["forappsscript"];
const apiKey = "###"; // Please set your API key for using Gemini API.
const logs = getLogs_({ functionName, projectIds });
console.log(logs);
const res = generateEvaluation_({ apiKey, functionName, logs });
console.log(res);
}When this script is run, the following result is obtained.
The Google Apps Script measures the processing time of different parts using console.time and console.timeEnd. The log data shows the execution time of different labels, indicating that label2 took 1001ms, label3 took 1511ms, and label1 took 2515ms. The script measures the time of each iteration in the loop using label4_i, but these measurements are not useful for identifying performance issues since each iteration has different sleep durations. The script unnecessarily measures the time of label4a within the loop, which is not a meaningful measurement for overall performance. The script could be improved by focusing on measuring the time of relevant sections and removing redundant measurements.When the evaluation is generated from sampleFunction2, please modify const functionName = "sampleFunction1"; to const functionName = "sampleFunction2";. By this, the following result is obtained.
The Google Apps Script has a performance issue. The script takes a significant amount of time to execute due to the long sleep durations and the nested loop. Specifically, the sleep duration in the loop increases with each iteration, leading to an extended execution time. The script also exceeds the maximum execution time. The script can be improved by reducing the sleep duration, restructuring the loop, or using asynchronous functions.Here, when the line of Export the result by summarizing the generated description within 100 words without a markdown. is removed from generateEvaluation_, the detailed evaluation will be generated.
Summary
This report outlines a method for exporting logs from Google Apps Script projects to GCP for analysis using the Cloud Logging API. This approach overcomes limitations of in-script logging methods by enabling retrieval of logs after script execution, regardless of success or failure.
Key steps involve:
- Linking a Google Apps Script project to a GCP project.
- Employing the Apps Script API to retrieve a list of currently running processes.
- Leveraging the Cloud Logging API to retrieve logs for the specific function execution based on start and end times.
Sample scripts are provided to demonstrate retrieving logs and parsing them to analyze script process costs. Additionally, a script is included to generate an evaluation of the script using the Gemini API and the retrieved logs.
