Sometimes you need a solid platform where people can collaborate in real-time and input data in a structured, reusable way. Depending on your needs and planning, Google Spreadsheet can be helpful in such cases, specially if you dont have time or ressources to create a full-blown “admin interface”.
Of course, a tailored-made admin interface can be “better”, but well, Google Spreadsheet is here, instantly available, battle-tested, powerful, very flexible and as we’ll see below, you can even extends the UI.
Also note the powerful Google data APIs gives you full access to your data in various ways and there many other ways to exploit yout documents data (create executable APIs, publish the feeds, embed spreadsheet…)
The main issues for me yet are the google apps scripts disastrous developer experience, but i’ll give you some tips to reduce the pain :) (most of it is due to security model) and the UI that can be a bit slowish due to client/server interactions.
This example use React, because it’s sooo 2015, but you’d better use what makes you happy :)
How it works
So in this example, we’ll add a multi-selection widget to our spreadsheet, which will help our users create many-to-many relations in our spreadsheet.
Google Apps Script editor
This is a major pain in the @$$. When you want to script your documents, you must use that editor which lacks all the basics. There are ways to use your preferred editor and GIT, but it’s for a later article.
We’ll reduce the code needed here to the minimum : just some glue between our documents and our react widgets.
From you spreadsheet, select
Tools > Script Editor.
From here, you can add
.html templates which are your custom modals or sidebars and where you can interpolate some values from your docs, using old-school php-like tags.
The code from your html files cannot talk directly to the documents but can execute functions from your
The code here is interpreted when the document opens, on the server-side.
For example, here’s how you’d add a new menu entry to your document :
1 2 3 4 5 6 7 8 9 10
Then you can define a
selectGuests function that do what you need.
In our case, it will open our custom sidebar and pass necessary data (a list of available guests to select from a range in the spreadsheet).
A custom sidebar with a React widget
There are various approaches here :
- use external scripts and add minimal code to the html
I prefer the second approach because its lighter and allow cient-side caching for 3rd-party libraries which is faster.
There is a little gem recently published at npmcdn.com; this service from the well-named Michael Jackson allows you to grab any npm module just by adding a script-tag to your html doc. This is the perfect tool to inject umd libraries into our iframe.
Here’s an example sidebar.html :
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30
Open the Sidebar
The is an example
code.gs that triggers our sidebar, send and receives values from it.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42
With these few tips, you’re now able to build on top of Google Spreadsheets and add the missing parts you need.
Dont build your company on top of any closed-source product.
Exploit the tools, push the limits, but always keep full control of your data. (see the recent Parse shutdown)
Once your spreadsheet is well-strutured, you can use the Google Data APIs to extract them, or use one of many available npm modules to do it, like spreadsheet-to-json.