Download Stackdriver logs to a Google Sheet using Apps Script

[Alan Wells]

Stackdriver logs can be downloaded to a Google Sheet using Apps Script and the Cloud Logging REST API.

At GitHub:

https://github.com/Blueprinter/AppsScript-Download-StackDriver-Logs/blob/master/GS_Download_Logs

Code:

function getSD_Logs_(po){
 var data,entries,errMsg,errType,fnkName,httpRz,i,L,logs,nmbrOfEntriesToGet,options,param,parentParam,
 row,sh,ss,thisLog,time,url,usr;
 
 /*
 po.id = GCP project ID - https://console.cloud.google.com/home/dashboard
 po.shName - Name of the sheet tab to log the data to
 */
 
 /*
 Get StackDriver Logs and write them to a Google Sheet -
 
 */
 
 /*
 Your Apps Script project must be associated with a standard GCP project and the 
 Cloud Logging API must be enabled - 
 If you have not enabled the Cloud Logging API then you will get the error below - 
 
 */
 
 /*
 Request failed for https://logging.googleapis.com returned code 403. 
 "error": {
 "code": 403,
 "message": "Cloud Logging API has not been used in project 999 before or it is disabled. 
 Enable it by visiting https://console.developers.google.com/apis/api/logging.googleapis.com/overview?project=999 then retry. 
 If you enabled this API recently, wait a few minutes for the action to propagate to our systems and retry.",
 "status": "PERMISSION_DENIED",
 "details": [
 {
 "@type": "type.googleapis.com/google.rpc.Help",
 "links": [
 {
 "description": "Google developers console API activation",
 "url": "https://console.developers.google.com/apis/api/logging.googleapis.com/overview?project=999"
 }
 ]
 }
 ]
 }
}
 */
 
 param = 'projects/' + po.id;
 url = "https://logging.googleapis.com/v2/entries:list";
 nmbrOfEntriesToGet = 50;//Number of logs to get
 
 options = {};
 
 options.muteHttpExceptions = true;
 options.headers = {Authorization: 'Bearer ' + ScriptApp.getOAuthToken()};
 
 options.contentType = "application/json";
 options.method = 'post';
 
 options.payload = JSON.stringify({
 resourceNames: [param],
 orderBy: "timestamp desc",
 pageSize: 50,
 });

 httpRz = UrlFetchApp.fetch(url,options);
 //Logger.log(httpRz);
 
 if (httpRz.getResponseCode() !== 200) {
 
 return;
 }
 
 logs = httpRz.getContentText();
 //Logger.log('typeof logs: ' + typeof logs);
 
 logs = JSON.parse(logs);
 
 entries = logs.entries;
 
 //Logger.log('entries.length: ' + entries.length)
 //Logger.log('typeof entries: ' + typeof entries)
 
 ss = SpreadsheetApp.getActiveSpreadsheet();
 sh = ss.getSheetByName(po.shName);
 
 row = sh.getLastRow();
 //Logger.log('row: ' + row)
 L = entries.length;
 
 data = [];
 
 for (i=0;i<L;i++) {
   thisLog = entries[i];
   errType = thisLog.severity;
   fnkName = thisLog.resource.labels.function_name;
   errMsg = thisLog.jsonPayload.message;
   time = thisLog.receiveTimestamp;
   usr = thisLog.labels["script.googleapis.com/user_key"];
 
 data.push([errType + " " + time,fnkName,errMsg,usr]);
 }

 sh.getRange(row + 1, 1, data.length, data[0].length).setValues(data);
 
}


function testDownload() {
 
 getSD_Logs_({"id":"project-id-","shName":"StackDriver Logs"});
 
}



Manifest:

{
  "timeZone": "GMT",
  "dependencies": {
  },
  "exceptionLogging": "STACKDRIVER",
  "oauthScopes": ["https://www.googleapis.com/auth/cloud-platform","https://www.googleapis.com/auth/script.external_request",
                 "https://www.googleapis.com/auth/spreadsheets"]

}

[Ihor Stefurak]

This is awesome. Thanks for the plug!

[Ihor Stefurak]

Help with an issue.

1. I have a script in production that is an add-on. The project id is 3434XXXXXXXX I don't want to add this code to the script.

2. I want to use this code in a "dashboard" script. Is it possible? I tried but got an error.

[20-06-19 05:13:32:370 PDT] {

  "error": {

    "code": 403,

    "message": "Cloud Logging API has not been used in project 3597XXXXXXXX before or it is disabled. Enable it by visiting https://console.developers.google.com/apis/api/logging.googleapis.com/overview?project=3597XXXXXXXX then retry. If you enabled this API recently, wait a few minutes for the action to propagate to our systems and retry.",

    "status": "PERMISSION_DENIED",

    "details": [

      {

        "@type": "type.googleapis.com/google.rpc.Help",

        "links": [

          {

            "description": "Google developers console API activation",

            "url": "https://console.developers.google.com/apis/api/logging.googleapis.com/overview?project=3597XXXXXXXX"

          }

        ]

      }

    ]

[Alan Wells]

Yes, you can trigger the code from a sidebar, dialog box, web app, or any way that you can get an Apps Script function in a "gs" file to run.

That error is happening because you have not enabled the Google Cloud Logging API in the Google Cloud Platform.

That error provided a link.  All you need to do is copy the link, paste it into the browser address bar, and go to the page.

That will automatically associate a new GCP project with your Apps Script project.

From the Apps Script code editor, use the "Resources" menu and choose "Cloud Platform Project"

If you are not familiar with the "Cloud Platform" it can be confusing and has lots of services that you'll probably never use, and

never even know what they do or what they are for.

But if you're creating an add-on, then you may be familiar with the GSuite Marketplace SDK.

[Ihor Stefurak]

What would happen with my add-on if I associate this new script with it? Any possible issues?

[Ihor Stefurak]

So I created a new script, run test.

That error provided a link.  All you need to do is copy the link, paste it into the browser address bar, and go to the page.

I tried to go to the URL from the response https://console.developers.google.com/apis/api/logging.googleapis.com/overview?project=3597XXXXXXXX and got this

From the Apps Script code editor, use the "Resources" menu and choose "Cloud Platform Project"

Then I tried this and got another error. Everything is managed by me under 1 Gmail account.

[Alan Wells]

You can't associate the Cloud Logging script with your add-on.

The code that I provided requires some special scopes (permissions) that you would not want

to ask your users to approve.

You need to edit the appsscript.json file and add some scopes.

{
  "timeZone": "Your time zone will be here",

  "dependencies": {
  },
  "exceptionLogging": "STACKDRIVER",
  "oauthScopes": ["https://www.googleapis.com/auth/cloud-platform","https://www.googleapis.com/auth/script.external_request",
                 "https://www.googleapis.com/auth/spreadsheets"]

}

From the code editor, choose "View" and click "Show manifest file"

Edit your appsscript.json manifest file and save it.

Since that link did not work, you might need to "manually" create a new GCP project, get the GCP project ID,

and association it with the Apps Script project.  Note that the word "project" is used for both the Cloud Platform project,

and the Apps Script project.  They are not the same.  They are both projects, but you need to know which project

is being referred to.

[Alan Wells]

I just learned about a new option to filter Cloud Logging Logs by the log type.

The Logs can be filtered by:

'resource.type="app_script_function"'

This post is about how to download Apps Script logs from Stackdriver Logs.

https://stackoverflow.com/a/64530003/2946873