Log and analyse your Google Apps Script execution stats

put a neat little number against every task you have apps script automate using the processes.list method of their rest apis.

context

have you ever wondered how much time you have the google apps script spend over all the menial tasks that you might've scheduled for automation? ever wonder how many of those web apps, time triggers, "run" command from editors you may've initiated over the course of your time while using apps script?

this is simply yet another saga of stats that most may not need or want but might feel good to know just the same 😛

the last time was when i tried creating github-like contributions heatmap, but for meetings.

prior reading

in case you haven't already reviewed my walkthrough on how to use google apps script rest api from browser editor, i'd recommend going through that at first, as this post involves using a whole bunch of it while rallying through the initial setup (its not as easy as it may initially seem).

architecture

the script would do the following activities -

1. start fetching & logging executions right from the moment you trigger the fetchMetrics() function
2. spawn a new time-trigger to start collecting all the older metrics, should all of 'em not be captured in the first go
3. delete residual trigger (if any, used for fetching older executions) and spawn yet another time-trigger to periodically log executions every 10 minutes and avoid any duplicates along the process

icing on the cake

the architecture also accommodates for aligning every execution with a unique, 32-bit md5 hash (thanks to this gist from github) that acts as an identifier and avoids any chance of logging duplicate executions.

codebase

you can access the entire script on my github repository here.

an important construct to highlight with this script is that the manifest file too ought to be manually configured as there isn't an automated way where the apps script would detect the appropriate scopes - like it does for utilising any of its other inbuilt resources - & without which, the script would inadvertently fail -

{
  "timeZone": "Asia/Kolkata",
  "dependencies": {
  },
  "oauthScopes": [
    "https://www.googleapis.com/auth/script.external_request",
    "https://www.googleapis.com/auth/script.processes",
    "https://www.googleapis.com/auth/spreadsheets",
    "https://www.googleapis.com/auth/script.scriptapp"
  ],
  "exceptionLogging": "STACKDRIVER"
}

you guessed it - its the 2^nd one (script.processes)!

common errors

besides the ones that you'd encounter while having to use the apps script api, the first and foremost of 'em all would be for not already having enabled the rest api & the error would look something like this -

{
  "error": {
    "code": 403,
    "message": "Apps Script API has not been used in project ZZZZYYYYAAAA before or it is disabled. Enable it by visiting https://console.developers.google.com/apis/api/script.googleapis.com/overview?project=ZZZZYYYYAAAA 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/script.googleapis.com/overview?project=ZZZZYYYYAAAA"
          }
        ]
      }
    ]
  }
}

demo

once you have everything setup, you could simply connect the spreadsheet that contains all the executions along with its peripheral information and plug them to google data studio to see what visualisations you can then draw up. here's what i was able to do -

in case you're unsure of how to connect spreadsheets to google data studio, refer this documentation for a smooth transition towards the same.

learning(s)

1. from everything that i've been able to gather, looks like you could only go so far as 7 days to collect all your script execution data - i could be wrong here though
2. also, you don't really need an "apiKey" to trigger any of the apps script rest apis that have been used in my posts (that's pretty much only the processes method, for now)