Introducing: Spreadsheet Actions

Today I released the first version of ”Spreadsheet Actions”. It is a small framework for Google spreadsheets, allowing actions to be run on selected rows in a spreadsheet. This could for example be used for:

  • Sending customized emails
  • Copying and sharing files
  • Creating folder structures in Google Drive
  • Importing/exporting contacts to Google contacts
  • Managing student workbooks

The whole idea with Spreadsheet Actions is that new plugins could be written when there is reason to. You want to create a new folder in Gmail for every student in your class? That would be (at most) 50 lines of code. You want to append text to Google documents? That would be another small plugin.

In this blog post I’d like to explain a few ideas behind Spreadsheet Actions, but first a few links:

Why a framework for spreadsheet actions?

You may have heard of gClassFolders, Doctopus, mail merging scripts, and maybe even StudentMatrix. They all allow using data in Google spreadsheets to make tasks easier – create a folder for every student, share files with students, and so on. What’s the point in adding another tool for executing actions from spreadsheets?

This is actually my fourth go at something like this. Every time before I have been creating a tool that is intended to do one single task, or tasks within a well defined space (such as colouring cells in multiple spreadsheets at once). An every time I have ended up wanting to do more in the same tool.

The idea with Spreadsheet Actions isn’t necessarily to bring new types of actions, and definitely not to bring the best of user interfaces. It is to be a framework where new types of actions easily could be added.

Good bye, nice user interface

I’ve had the idea for something like Spreadsheet Actions for quite some time, and one of the obstacles was how to create a nice user interface – preferably without making new plugins a hell to write. If the plugins should not only define new actions, but also describe interface for any settings, writing plugins quickly becomes a minor science field in itself. And the framework becomes a mess of abstraction layers. Not the best conditions for an ecosystem of spreadsheet actions.

Then, a few months ago, Google deprecated the UI service. That had been my basis for writing dynamic user interfaces – without it you will have to write more or less standard HTML from scratch to build interfaces. That’s nice if you know what HTML you want, but if you want to allow plugins to define their own interface in a generalized framework, you sooner or later need a whole CMS or something. And having a CMS written in Google-flavoured JavaScript is not my idea of fun.

I thought this was the end of my plans for a spreadsheet action framework. Until I realized that it, in some ways, makes sense to have all the settings and options right in the code.

In more than one way it is absolutely horrible to have settings in code. It ruins version control, it risks having nosy users screwing up the non-settings code, and the usability is crap compared to a slick graphical interface. But all (or most) of these factors are not an issue when it comes to Google scripts – they can’t be version controlled in any regular way, the user only has access to his/her own copy of the code, and nice graphical interfaces are really difficult to get into Google scripts anyway.

So there it was – I went for putting all the options in the code. Time will tell if it works out.

What Spreadsheet Actions contains, so far

So far, Spreadsheet actions has the following:

  • A simple way of declaring ”bulk actions” (executed for all selected rows) and ”global actions” (executed for the spreadsheet as a whole).
  • Dependency declaration – you can tell what other plugins your plugin depends on, and which versions. These plugins will be loaded before any actions are run.
  • A simple framework for automated testing, but no tests yet.
  • A handful of plugins, allowing emailing, file management (including access control), importing/exporting contacts, and management of ”student workbooks”.

Each plugin is its own script file, with any settings at the top. (The plugin itself is a pseudo-class you inherit from.)

(Note: Spreadsheet Actions cannot, as things are now, be an add-on for Google spreadsheets. This is because the code for add-ons cannot be edited by the user.)


If you’re interested in trying out Spreadsheet Actions, I recommend looking at the first few videos in the video list linked above. If you have written Google scripts, I would then recommend looking at the example plugin on GitHub. After that I would create a copy of the latest stable version, and try it out.

If you have any comments or ideas, I’d happy to hear them.


2 thoughts on “Introducing: Spreadsheet Actions

  1. Hej!
    Ser spännande ut. Jag jobbar på gymnasieskola i Ystad här vill man att vi ska arbeta i Office 365 medan vi lärare vill arbeta i GAFE. IT tar hela tiden upp Datainspektionen åsikter om att spara kommentarer om elevernas arbete i molnet. Hur har ni hanterat detta?
    Johan Strandqvist

    • Nyttig fråga. Sollentuna kommun har, vad jag förstår, ett lite speciellt avtal med Google som gör att Datainspektionen är nöjd.

      Det är ett avtal som slutits tillsammans med flera andra kommuner, så det kan vara värt att se om det går att hänga på det.

      Jag vet inte om det kommer från det avtalet, men det har gjorts tydligt för oss lärare att en del saker måste finnas utanför Google-datan, och vissa (känsliga) saker ska förstås inte finnas åtkomligt för Google alls. (Och inte för andra liknande tjänster heller, om det inte är krypterat.)


Fyll i dina uppgifter nedan eller klicka på en ikon för att logga in: Logo

Du kommenterar med ditt Logga ut /  Ändra )


Du kommenterar med ditt Google+-konto. Logga ut /  Ändra )


Du kommenterar med ditt Twitter-konto. Logga ut /  Ändra )


Du kommenterar med ditt Facebook-konto. Logga ut /  Ändra )


Ansluter till %s