Identify and understand errors in Apps Script with Stackdriver

Post date: Mar 16, 2017 3:33:11 PM

Last week I attended Next, the Google Cloud conference in San Francisco. With Paul McReynolds, the Apps Script Product Manager, we presented a new connector to Stackdriver logging. It's especially helpful to investigate exceptions in scripts deployed as a web app or as an add-on, like YAMM.

The session has been recorded and you can find the video on YouTube:

Apps Script monitoring and analytics with Stackdriver Logging and BigQuery (Google Cloud Next '17)

Here's below the session agenda. In addition to Stackdriver Logging, I've covered how I use Google Analytics to track the add-on usage and how to use BigQuery to aggregate data sent to Stackdriver.

Handling client side exceptions

The current implementation lets you monitor all exceptions happening on server side (in your .gs files) simply by checking a box in your Apps Script project UI. At the end of the session, somebody asked if it was also possible to track exceptions on client side. I knew that was possible with Stackdriver but I've never tested it. In fact, it's quite easy to set up! Stackdriver provides a library (the source code is even available on GitHub) to track errors on client side JS: https://cloud.google.com/error-reporting/docs/setup/client-side-js

You might notice that this logs exceptions directly in the Error Reporting part of Stackdriver - which was announced in April 2016, while server side exceptions appear in the Logging part of Stackdriver:

Sending data to Stackdriver logging makes sense as we can send standard logs in addition to exceptions, but it would be great to also get the info in the Error Reporting part, where exceptions are directly aggregated. You can quickly see the number of occurrences and receive email notifications when a new kind of exception appears.

Some useful queries

During the session, I mentioned that the biggest limitation in Stackdriver Logging was its poor metrics capabilities. You can view and filter logs but you can't aggregate them, meaning it's not possible to list the most common errors, the number of users impacted,...

Thankfully this is easily solved thanks to the ability to export logs in BigQuery (here's how to set it up). Here is a list of useful queries to do on BigQuery. You only have to edit the FROM clause to adapt it to your own table ids.

Note: As a new table is created for each day, we make use of the wildcard functions in the FROM clause.

"The term table wildcard function refers to a special type of function unique to BigQuery. These functions are used in the FROM clause to match a collection of table names using one of several types of filters. For example, the TABLE_DATE_RANGE function can be used to query only a specific set of daily tables."

List the number of errors & number of users impacted by day

SELECT

STRFTIME_UTC_USEC(timestamp, "%Y-%m-%d") AS date,

COUNT(*) AS nb_of_errors,

COUNT(UNIQUE(labels.script_googleapis_com_user_key)) AS nb_of_impacted_users

FROM

TABLE_DATE_RANGE([api-project-52669336:YAMM.apps_script_console_logs_], DATE_ADD(CURRENT_TIMESTAMP(), -7, 'DAY'), CURRENT_TIMESTAMP())

WHERE

severity == 'ERROR'

GROUP BY

date

ORDER BY

date DESC

List the most common errors (by nb of users impacted)

SELECT

textPayload AS error_message,

COUNT(textPayload) AS error_count,

COUNT(UNIQUE(labels.script_googleapis_com_user_key)) AS nb_of_impacted_users

FROM

TABLE_DATE_RANGE([api-project-52669336:YAMM.apps_script_console_logs_], DATE_ADD(CURRENT_TIMESTAMP(), -7, 'DAY'), CURRENT_TIMESTAMP())

WHERE

severity == 'ERROR'

GROUP BY

error_message

ORDER BY

nb_of_impacted_users DESC

LIMIT

100

List errors by function name with error count & nb of users impacted

SELECT

resource.labels.function_name AS function_name,

COUNT(resource.labels.function_name) AS error_count,

COUNT(UNIQUE(labels.script_googleapis_com_user_key)) AS nb_of_impacted_users

FROM

TABLE_DATE_RANGE([api-project-52669336:YAMM.apps_script_console_logs_], DATE_ADD(CURRENT_TIMESTAMP(), -7, 'DAY'), CURRENT_TIMESTAMP())

WHERE

severity == 'ERROR'

GROUP BY

function_name

ORDER BY

error_count DESC

The connection between Apps Script and Stackdriver Logging is not yet available to everybody. For now you need to be part of the App Maker Early Access Program to get this feature (here's the link to join the EAP). But you can already make use of Stackdriver on client side and even send server side exceptions via UrlFetch. Stackdriver Logging and Error Reporting are great tools to help monitor your app / add-on and I strongly recommend them!