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.

Continue reading

Converting from UiApp + Chart Service to Html Service + Google Visualization API

In July 2012 Roee Eilat and Eric Koleda, respectively members of the Google Charts and Apps Script Teams, published a popular tutorial: “Building a Charts Dashboard”. Later, in December 2014, Google deprecated the UI Service that supports Charts – while strangely not deprecating the Charts Service itself. That’s left some confusion for developers.

For users of Google Apps Script, there are two alternatives for visualizing data:

  1. Continue using Charts + UiApp. While deprecated and not supported for auto-completion in the editor, UiApp still works. There has been no announcement that it will actually be shut down… yet.
  2. Follow Google’s recommendation, and use HtmlService instead. A feature request to have Charts supported by the HtmlService has been declined. So this seemingly-simple recommendation entails the additional requirement that you use Google’s other Charts offering, also known as the Google Visualization API, or GViz.

In today’s post, I will show you how to convert the old dashboard example from from UiApp + Chart Service to HtmlService + Google Visualization API. For those willing to geek out with me, there’s even an introduction to System Architecture.

Continue reading

Did you know? (Custom Functions in Google Apps Script)

In Google Apps Script, Custom Functions are special. They have limitations that other script functions don’t, but to compensate, they have been given awesome powers! Ok, that’s a bit strong – let’s say they have unique capabilities that aren’t immediately obvious.

You can learn most of what you need to know to write your own Custom Functions by reading Google’s documentation. But did you know

  • You can craft effective auto-completion help with comments in your Custom Functions?
  • Your Custom Functions can present customized error messages for users?
  • Custom Functions can support an arbitrary number of parameters?

Today’s blog might have some surprises for you, if you thought you knew everything about writing Custom Functions.

Continue reading