revolunet blog

web technologies for desktop and mobile

Extend Google Spreadsheets With npmcdn.com and React

| Comments

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

You google spreadsheets can be extended with Google Apps Scripts. These are hosted Javascript files that execute on the server-side, in the Google infrastructure, and have access to various Google APIs and can be triggered right from your documents.

In the case of spreadsheets, these scripts can open custom isolated modals or sidebars, which can host random client-side javascript inside an iframe, get data from your docs and send back results to them.

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 .gs scripts (server-side javascript) and .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 .gs files.

The .gs files

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
// add a custom menu when the spreadsheet opens
function onOpen() {
   SpreadsheetApp.getUi()
      // create a new menu in your spreadsheet
      .createMenu('My Custom menu')
      // add an entry to that menu
      .addItem('Select Guests', 'selectGuests')
      // Warn: forgetting this line can drive you nuts
      .addToUi();
}

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 :

  • create a full bundle with all your javascripts and insert it in the page
  • 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
<!DOCTYPE html>
<html>
  <head>
    <link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons1.css">
    <script src="//npmcdn.com/react@0.14.7/dist/react.js"></script>
    <script src="//npmcdn.com/react-dom@0.14.7/dist/react-dom.js"></script>
    <script src="//npmcdn.com/react-multiselect-revolunet@1.0.5"></script>
  </head>
  <body></body>
  <script>

    // receive some values from spreadsheet here
    // turn back strings to JSON
    var choices = JSON.parse("<?= choices ?>");

    // instantiate the react component with props
    // using the umd library name
    var cmp = React.createElement(window['react-multiselect-revolunet'], {
       choices: choices,
       onChange: function(selection) {
          // send result to spreadsheet function
          google.script.run.setActiveCellValue(selection.join(','));
       }
    });

    // render the component
    ReactDOM.render(cmp, document.body);

  </script>
</html>

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
// this function will be called by our sidebar to update the cell value
function setActiveCellValue(value) {
  SpreadsheetApp.getActiveSheet().getActiveCell().setValue(value);
}


// this is called when user selects the entry in the custom menu
function selectGuests() {
  // open our sidebar with a range of possibles from A2:A range
  openSideBar("Select Guests", "'Sheet1'!A2:A", 0, 0);
}

/*
 * title: Title of the SideBar
 * range: range where to get choices from
 * valueIndex: 0-based index of the column in the range responsible of values
 * labelIndex: 0-based index of the column in the range responsible of labels (can be the same as values)
*/
function openSideBar(title, range, valueIndex, labelIndex) {

  // grab list of available choices
  var choices = SpreadsheetApp.getActiveSheet().getRange(range).getValues();

  // create the template from HTML
  var tpl = HtmlService.createTemplateFromFile('pane.html');

  // add data to the templates. needs to be passed as strings !
  // data structure depends on your widget
  tpl.choices = JSON.stringify(choices.map(function(choice) {
    return {
      value: choice[valueIndex],
      text: choice[labelIndex]
    };
  }).filter(function(val) {
    return val.value && val.text;
  }));

  // now, evaluate and execute our template inside a sidebar
  var result = tpl.evaluate().setSandboxMode(HtmlService.SandboxMode.IFRAME);
  result.setTitle(title).setWidth(300);
  SpreadsheetApp.getUi().showSidebar(result);
}

Conclusion

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.

Comments