Adventures in Building an Interactive Apps Script Sidebar

Now that I’ve finished a rewrite of an application in HTMX, I wanted to see if HTMX could be used to enhance the use of Google Apps Script sidebar interfaces. I build these from time to time at work to help with spreadsheet interaction that goes beyond simple formulas. The idea is to allow for more dynamic interactions in the (very limited) sidebar available through Google Apps Script.

HTMX: This Won’t Work

I started with adding HTMX directly and had no luck. The main issue is that HTMX uses XMLHttpRequest to fetch data, and that leads to CORS issues. When HTMX starts a request, a preflight check is made because it includes the upload parameter, which is considered “not simple” by the browser. This preflight check has to pass for the request to proceed.

The preflight fails because of the redirect between the published Apps Script /exec endpoint and the googleusercontent URL with the actual code. HTMX also adds headers to the request, which also makes the request unsafe and the preflight check fails with a CORS error.

So, I tried getting around the CORS issue by issuing async fetch calls, but that just adds a layer of complexity rather than solving a problem. It also confirms the fact that browser engineers are much smarter than me because it still didn’t work.

All that said, fetching content directly from a sidebar won’t work, let alone posting data to the backend.

Hyperscript to the Rescue

Hyperscript is a companion to HTMX which offers similar functionality, just in a different context. It’s a lightweight scripting language with Python-ish syntax that you can include as a “_=*” attribute on HTML elements. You can add different handlers and listen for events just like with HTMX, so clean interactions are still possible.

Using Hyperscript, you can take advantage of the google.script.run() API to interact with your Apps Script code. This allows you to do some simple dynamic content replacement all the way up to accepting input from a user to query or update data in the sheet through the sidebar.

The Hyperscript cookbook has some examples, so let’s look at how to implement it in an Apps Script context for some real-world application. All of the code to try it yourself is in this GitHub repo. You can also make a copy of this sheet to get your own version.

Getting started

There are some utility functions we need to get the project started. I’m greatly indebted to Bruce McPherson and his extensive writing on Apps Script project structure. In your Code.gs file, we’ll create some global functions to allow us to work more effectively between the Apps Script code and the client.

I’ll also use Bruce McPherson’s Promise-based wrapper for working with Apps Script as a starting point. All of our requests will go through this method:

Templates

Now that the boilerplate is done, we need to start defining some worker classes and templates.

To keep things clean, I wrap each of my operations in an IIFE object which defines methods and the templates to return with each completion. This means there are more files to manage in the code editor, but each one encapsulates function cleanly and it’s easier to maintain.

Make the Apps Script handler:

And finally our base HTML template for the sidebar:

This template will:

  • Allow us to access the Runner middleware to marshal API calls.
  • Click a button to run a bit of code.
  • Put the result of the Apps Script code into the #target div.
  • Initialize any Hyperscript included in the template to allow for interactivity following the swap.

The key in this method is to think through what interaction you want your template to have and to include that in the hyperscript attribute.

A More Complex Example

Let’s say you want to make an Apps Script Extension (formerly “Add-on”) or container bound script which allows you to fetch data from an API and then selectively insert results into your sheet. You can do that with Hyperscript inside the Apps Script sidebar quite cleanly. We’ll keep the same boilerplate code but define a couple of functions to build a quick sample. I’m going to use the handy Star Wars API as the data source.

Although you can use Hyperscript to get data directly (via the fetch command), templating the response in the script gets messy, especially if you want to have actions on the results of the fetch request. To clean this up, we’ll take advantage of Apps Script’s URLFetchApp class and another HTML template fragment.

Start by adding a button to a sidebar:

Now, we’ll create our IIFE function

And lastly, our new template fragment which handles looping over the results as part of the evaluation step.

To help understand what’s happening, we are:

  • Rendering a sidebar with a button to fetch results from a third party using URLFetchApp.
  • Allowing the Apps Script templating engine to handle rendering results.
  • Collecting the resulting HTML string and sending it back to the client.
  • Hyperscript swaps the HTML result into the DOM and then re-initializes on the new elements.
  • Clicking on a name adds it to the spreadsheet and then removes the option from the page.

Here’s the result:

In the template, we define Hyperscript actions on each element so they also become interactive in the sidebar. Clicking on a name calls the saveName function and adds the value to the next available row in the sheet before removing itself from the sidebar.

Is it worth it?

This seems like a ton of work that could be achieved with out-of-the-box Javascript. So, is it all worth it?

It depends.

If you have minor interactions here and there, it might not be worth adding the extra attributes or taking time to create template fragments. DOM interactions can be pretty simple if you’re just fetching and displaying data.

The real power of Hyperscript comes in locality of behavior and in making interaction plain in the HTML and not burying those actions in script files and event listeners. In the advanced example, I think Hyperscript is worth the effort because it is easy to see exactly what interactions exist on which elements.

Apps Script is notorious for weird behavior just because of the platform. Adding Hyperscript as a tool to manage interaction and behaviors can help identify bugs sooner because you – the developer – have a better idea of which interaction causes which behavior in the application.

If you’re on the fence, take some time just to play around with simple swaps like I showed in the first example. Once you have the hang of writing behaviors on elements rather than in event handlers, some of the benefits will start to emerge.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.

css.php