Did you know? (You can log to a spreadsheet from client JavaScript!)

Google Apps Script has a Logger class, intended to help debug by enabling you to log the operation of your code, and examine run-time data. Viewing those logs is supported through the View > Logs menu option in the Google Apps Script editor.

Unfortunately, the built-in Logger doesn’t work in every situation. A requirement is that you must be running your script with an attached editor / debugger session, so that precludes logging trigger functions (when reacting to actual events), and many asynchronous operations such as UI interactions. If you want to compare results between sessions, you’ll be frustrated to find the output of the Logger is transient – the next time your script is executed, all previous logs are lost.

Today’s post is a quick tip for generating persistent logs from your whole application, including client-side JavaScript running in a user’s browser, such as an add-on sidebar or dialog, menu-driven functions, and autonomous functions such as triggers. Custom functions are not permitted to modify spreadsheet contents directly, so they are the one exception for this technique.

Extending the native Logger

I have mentioned Peter Herrmann’s BetterLog library on StackOverflow on many occasions.

The BetterLog Google Apps Script library extends the native Logger with one line of code and gives you additional features like logging to a spreadsheet and more.

Before I was introduced to this library, I had my own snippet that performed the simple task of recording logs to a spreadsheet – maybe you have one, too. Well, that function is long-retired, and now BetterLog is one of my go-to debugging tools.

Utility function to enable client logging

On the server side, two functions make up this utility.

The startBetterLog() function pretty much does what it says – it starts BetterLog as a log destination, overwriting the native Logger class. I call this function at the start of every server-side function that may be invoked asynchronously. If the execution instance already has BetterLog set up, then the function exits immediately, incurring very little overhead.

Two global variables are used for managing the state of BetterLog.

  • betterLogStarted is set true when BetterLog is started for the present execution instance. Using a global for this ensures that it is initialized false for each new execution instance; if we instead used a persistent store such as PropertyService, we would not reflect the state of individual instances properly.
  • logSheetId should be left undefined for spreadsheet-contained scripts, when we want logs to be directed to the container spreadsheet. If that’s not the situation, then provide the ID of a logging spreadsheet. (This variable could be replaced with a persistent store value if you wish.)

The second server-side function is specifically for support of client-side JavaScript; clientLog() is designed to invoke any BetterLog method by having the method name provided as the first argument, followed by any number of arguments to be passed to the target method. The intent is to call this function from simplified wrapper functions – we’ll get to that next.

For the client side, I’ve been happy with simple logging, so there’s just one pass-through wrapper function defined, Logger.log(). It can be called anywhere in our Client-side JavaScript exactly as we’d do in Google Apps Script, making it very convenient when you’re writing code on both sides of the divide.

If you find that you have need of any of the other methods in BetterLog, simply follow the pattern introduced with Logger.log().


Here’s an example of client-side JavaScript for an add-on sidebar.

Add on Template   Google Sheets

Things to note:

  • The HtmlUtilities.html file is assumed to have been loaded, so that is something you need to take care of. Within the default file arrangement that’s provided for web apps, for example, I add these lines to Index.html, which is loaded and evaluated as an HtmlTemplate:
    <!-- Use a templated HTML printing scriptlet to import common HtmlUtilities. -->
    <?!= include('HtmlUtilities'); ?>
  • Logger.log() can be called from client-side JavaScript without worrying about the availability of BetterLog’s spreadsheet, since that detail is taken care of on the server.
  • Further, all Logger.log() calls may be left in place in production code with no ill effects, beyond a performance tax incurred for round-trip messaging.
  • In the log spreadsheet, all logs from the client side have “CLIENT” prepended automatically, so they can be located easily. Refer to the above image for an example of that, circled in red.

Happy Logging!

There you go – now you can develop your application with centralized, time-stamped logs from all components.


2 thoughts on “Did you know? (You can log to a spreadsheet from client JavaScript!)

  1. hello – thanks for writing this up. do you know what the performance of BetterLog is as compared to the standard Logger ? Is this done async or not? Reason i ask is that from what i’ve seen writing to a google sheet can take half a second which can be problematic. thanks!


    • From a performance standpoint, BetterLog is costlier than Logger. There are two reasons for this; first, external libraries are slower than self-contained code in Google Apps Script, and second, the spreadsheet operations are being done synchronously (i.e. your code must wait while the log is written).

      You need to consider though, that BetterLog works in situations that Logger does not – in those cases, performance is less of a problem than the lack of transparency to your code’s operation.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s