Chapter 7. Using User Interfaces in Spreadsheets and Documents

There are many situations where we need to present some data from within a spreadsheet or a document but where the standard user interface is not really appropriate. For example, you have a logging sheet of all the visitors of an exhibition and you'd like to show an average count of the people that were present at this exhibition on a given day. It is of course possible to calculate this in a spreadsheet and decide which cell shows this value, but suppose you want to be able to get some variant of this and get a mean value for the exhibition duration; what are you going to do? Create a new cell with another result, and again come up against the need to add a description cell somewhere for identification purposes.

Let's check out an easier solution for this using what we just learned in the previous chapter.

Pop ups

Pop ups are a simple user interface that you could call from a menu or a drawing / image, that presents your result in the form of a couple of listboxes from which you can select the result you want.

Here is a screenshot of such a UI that I created to calculate the working hours of our teachers across three grades and different courses, with horizontal and vertical totals.

The preceding UI panel comes up as a pop-up window and reads data from the current spreadsheet. I don't need to change the data in any way, nor change the layout of my sheet; everything is done from the UI, and I can call it whenever I need it. This is really comfortable to use, but it uses a lot of external resources, such as teacher calendars and custom resources, so it does not become a valid candidate as a demo script.

Let us take a simpler example to show how we can create and use this UI.

The spreadsheet we use here is the main interface of a complete online reservation tool for any public show. After we have...

Sidebars

Sidebars are only available in Google text documents and in form creation UIs. This feature has been introduced quite recently and offers a wide array of possible applications.

We used a very basic example of this in Chapter 2, Create and Manipulate Forms, to show a sample of the last form responses, but we can make it a lot more useful and attractive if we wish.

An impressive example was published a few months ago by a Google engineer on GitHub (https://github.com/google/google-apps-script-samples/tree/master/cursor_inspector).

Cursor Inspector is a script that shows the content and properties of the selected text in a sidebar in a document. From there on, we can imagine all sorts of developments: automatic data insertion in text, contextual help, and so on.

The code of this demo uses Google Apps Script and jQuery in a JavaScript client and is far from simple, but results in something awesome. I can't recommend enough that you give it a try.

The small test we are going to try here is...

Summary

We can see that there are many use cases where a simple UI can at least simplify our work with spreadsheets and documents, either by showing data in a way that is not directly available or by adding extra functionalities.

We can also use the UiApp service or HTML service to create UIs; the latter allows us to create more fancy user interfaces if we are using third-party libraries.

Of course, this means that we'll need to learn a lot of new techniques. The complete documentation about jQuery is about 300 pages long (http://forum.jquery.com/topic/jquery-1-7-reference-documentation-as-a-pdf), and I'm not even sure that reading it entirely would be sufficient to use it easily. However, as we shall see in the next chapter, the learning process can be gradual.