Using User Interfaces in Spreadsheets and Documents - Google Apps Script for Beginners (2014)

Google Apps Script for Beginners

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.

Pop ups

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 filled a couple of cells with titles and descriptions, it will create a form that we can publish on the Web. All the form answers will get inserted into a logger sheet that typically looks as illustrated in the following screenshot:

Pop ups

In this example, I have only six responses so it's rather easy to read; but when I get hundreds of them, it won't be easy anymore.

The UI looks like the following screenshot:

Pop ups

In this UI, we can view everything we need in one glance; even if we were to have more events and many more responses, it would automatically resize its columns to fit the best possible way.

Now let's have a look at the code:

function countData(){

var day = ScriptProperties.getProperty('day').split('|');

var numQ = ScriptProperties.getProperty('numQuestion');

var genTitle = ScriptProperties.getProperty('genTitle');

var stHour = ScriptProperties.getProperty('stHour').split('|');

var endHour = ScriptProperties.getProperty('endHour').split('|');

var title = ScriptProperties.getProperty('title').split('|');

var descr = ScriptProperties.getProperty('descr').split('|');

var resNum = ScriptProperties.getProperty('resNum').split('|');

var resMax = ScriptProperties.getProperty('resMax').split('|');

The parameters of the current form are stored in ScriptProperties, a convenient place for a small amount of information.

var data = logSheet.getDataRange().getValues();//data come from the logSheet

var resVal = [];

for(var d=0;d<day.length;++d){

var resCount = 0;

for(var n=1;n<data.length;++n){resCount+=Number(data[n][(d+5)])};

resVal.push(resCount);

}

ScriptProperties.setProperty('resVal',resVal.join('|'));

var uniqueDay = [];

var ud = 0;

uniqueDay.push(day[0]);

for(var d=1;d<day.length;++d){

if(day[d]!=uniqueDay[ud]){uniqueDay.push(day[d]);ud++}

}

Logger.log(uniqueDay+' length='+uniqueDay.length);

In this first part, we examine how many events we have for each day and how many responses we received so that we can build our UI with the right column size. We decided not to exceed a width of 900 pixels so that it remains visible even on smaller screens (tablets, for example). The following code depicts this:

var frameWidth = 300*uniqueDay.length.toString();

if(frameWidth>900){frameWidth=900};

var app = UiApp.createApplication().setTitle('Count overview').setHeight(480).setWidth(frameWidth+50).setStyleAttribute('background','#ffffaa');

var panel = app.createVerticalPanel().setStyleAttributes(styleCalc);

var panelV = app.createVerticalPanel().setStyleAttributes(styleCalc);

var panelH = app.createHorizontalPanel().setStyleAttributes(styleCalc).setStyleAttribute('margin-left','7px');

var scroll = app.createScrollPanel().setWidth(frameWidth+25).setHeight('400');

panel.add(panelV).add(panelH);

scroll.add(panel);

var colIdx = [];

var colPanel = [];

var alertCount = 0;

var titleRow = app.createHorizontalPanel();

app.add(app.createHTML(genTitle).setStyleAttributes({'padding':'8px','margin-left':'10px'}));

panelV.add(titleRow.setStyleAttribute('padding-left','6px'));

for(var c=0;c<uniqueDay.length;c++){titleRow.add(app.createTextBox().setText(uniqueDay[c])

.setStyleAttributes(styleRow).setReadOnly(true).setWidth('140'))

.add(app.createTextBox().setText('hour')

.setStyleAttributes(styleRow)

.setReadOnly(true).setWidth('55'))

.add(app.createTextBox().setText('places')

.setStyleAttributes(styleRow).setReadOnly(true)

.setWidth('55')).add(app.createTextBox().setText('max')

.setStyleAttributes(styleRow).setReadOnly(true).setWidth('40')) ;

}

The styles were defined outside the function in the preceding code so as to keep the code tidy.

for(var u=0;u<uniqueDay.length;u++){colIdx.push(0) ; colPanel.push(app.createVerticalPanel()) ; panelH.add(colPanel[u])};

for(var u=0;u<uniqueDay.length;u++){

for(var d=0;d<day.length;++d){if(day[d]==uniqueDay[u]){colIdx[d]=u}}

}

for(var d=0;d<day.length;++d){

Logger.log(' d='+d+' title[d]:'+title[d]+' in column '+colIdx[d])

}

for(var d=0;d<day.length;++d){

var alert = '#ffffde';

if(resVal[d]>=resMax[d]){alert='orange' ; alertCount++}

var row = app.createHorizontalPanel().add(app.createTextArea()

.setText(title[d]).setStyleAttributes(styleRow)

.setReadOnly(true).setWidth('140')).add(app.createTextBox()

.setText(stHour[d]+'-'+endHour[d])

.setStyleAttributes(styleRow).setReadOnly(true).setWidth('55'))

.add(app.createTextBox().setText(resVal[d])

.setStyleAttributes(styleRow)

.setStyleAttribute('background',alert)

.setReadOnly(true).setWidth('55').setId('resVal'+d))

.add(app.createTextBox().setText(resMax[d])

.setStyleAttributes(styleRow).setReadOnly(true).setWidth('40')) ;

colPanel[colIdx[d]].add(row);

// Logger.log('colPanel['+colIdx[d]+'].add(row)')

}

In the preceding code, we count the places on the fly and check if it does not exceed the limits. If it did, we display an alert message and set an orange background for the faulty items.

var commentMsg = "no overflow alert";

if(alertCount>0){commentMsg = alertCount+" overflow alert(s) ! (orange cell(s)"};

var comment = app.createHTML(commentMsg).setId('comment');

if(alertCount>0){comment.setStyleAttributes(stylHeader)}else{comment.setStyleAttributes(styleDate)};

var rHandler = app.createServerHandler('refresh').addCallbackElement(panel);

var refresh = app.createButton('recalculate results', rHandler).setStyleAttributes({'padding':'2px','border-radius':'4px'}).setWidth('160');

var rcHandler = app.createServerHandler('closeApp');

var closAp = app.createButton('close this window', rcHandler)

.setStyleAttributes({'padding':'2px','border-radius':'4px'}).setWidth('160');

A pair of buttons allows us to refresh (to redo the counting) and close the UI respectively. This is done using the preceding code snippet.

app.add(scroll).add(app.createHorizontalPanel()

.add(comment).add(refresh).add(closAp)

.setStyleAttributes({'margin-left':'14px'}));

SpreadsheetApp.getActiveSpreadsheet().show(app);// show app

var bgUser = logSheet.getRange(1,1,logSheet.getLastRow(),logSheet.getLastColumn()).getBackgrounds();

var colorUser = logSheet.getRange(1,1,logSheet.getLastRow(),logSheet.getLastColumn()).getFontColors();

for(var n=1 ; n<bgUser.length;++n){

for(var c in bgUser[n]){

if(c!=2){

bgUser[n][c]='#eeffee'

colorUser[n][c]='#000000'

}

}

}

logSheet.getRange(1,1,logSheet.getLastRow(),logSheet.getLastColumn()).setBackgrounds(bgUser);

logSheet.getRange(1,1,logSheet.getLastRow(),logSheet.getLastColumn()).setFontColors(colorUser);

}

We change the background in the logger sheet as well so that we know that the colored rows were part of the count at that moment (other responses might have come in while we were checking), as shown in the preceding code.

function closeApp(){

var app = UIApp.getActiveApplication();

return(app.close())

}

The small function in the preceding code is used to close the UI and go back to the normal spreadsheet view.

As you can see, there is nothing too difficult to follow in the code snippets explained earlier; there are only about 80 lines of code in the script editor that result in a very convenient interface.

The entire code and template spreadsheet are available in view mode at the following link:

https://docs.google.com/spreadsheet/ccc?key=0AnqSFd3iikE3dFluVktBVFJrMkd5WUtxYWU2ZlQtUFE&usp=sharing

There are many other functions in this script: to generate the form's UI, to send confirmation e-mails, and to generate daily lists in alphabetical order for easy "real-life checks" using a paper and a pencil. None of these functions are really complicated, but the whole script is pretty long (it has 540 lines of code) and wouldn't fit in here for practical reasons.

Note

The script file has comments that explain every method we have used, so anyone should be able to understand it easily.

One detail I'd like to mention is about the appearance of this form. I mentioned in Chapter 2, Create and Manipulate Forms, that you could design your own forms using a UI service or HTML service (this is a good example) to avoid having the Google logo and name on your form.

Indeed, there is no Google logo on the form we generated; however, users will see a warning message as shown in the following screenshot:

Pop ups

This, I admit, is also annoying (although the user can dismiss it), but we can't do anything about it. Google considers it has to exclude its responsibility in case a script functions incorrectly.

Also, the URL of the form is a bit long but, as we learned before, we can use a URL Shortener service to make it shorter: http://goo.gl/jrtjlJ. All in all, this solution to use a web app to create forms is, in my humble opinion, quite satisfying.

This example shows all the possible ways to utilize user interfaces in spreadsheets, such as custom menus, browser pop ups, and custom UIs, but text documents, because they appear later in history, have even more ways to implement custom user interfaces.

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 simpler in many aspects and is also shorter, but it uses a few funny and interesting tricks.

Everybody knows that Google Docs has an integrated chat feature that allows editors to chat in real time while they are both active on the same document. A few months ago, someone on the Stack Overflow forum asked for an equivalent feature because he apparently couldn't use it due to some browser limitations (http://stackoverflow.com/questions/19648798/creating-a-basic-chatbar/19653610#19653610). I found the request rather funny, so I participated in the thread, and we finally came to a working solution that I will show here with a couple of improvements as an example of a "live" UI in a text document's sidebar.

function onOpen() {

DocumentApp.getUI().createMenu('Chat Utilities')

.addItem('Open Chat', 'createChatBox')

.addItem('Show your current ID','showUser')

.addItem('Change your user name', 'setUserName')

.addToUI();

}

A simple onOpen function is used to create a menu in the document as shown in the preceding code.

function createChatBox(){

ScriptProperties.setProperty('chatContent','');

var app = UIApp.createApplication().setWidth(252);

app.setTitle("Chat Bar");

var vPanel = app.createVerticalPanel().setId('chatPanel').setWidth('100%');

var chatHandler = app.createServerHandler("sayChat").addCallbackElement(vPanel);

var textArea = app.createTextArea().setId('chatBox').setName('chatBox').setReadOnly(true).setText('').setSize('250px', '450px');

var textBox = app.createTextArea().setId('messageBox').setName('messageBox').setText('Start chat...').setPixelSize(250,100).setStyleAttributes({'padding':'5px','background':'#ffffcc'}).addKeyPressHandler(chatHandler);

var clearTextBoxClientHandler = app.createClientHandler().forTargets(textBox).setText('');

textBox.addClickHandler(clearTextBoxClientHandler);// a ClientHandler to delete old text when we click the textArea

var chatButton = app.createButton().setId("sayButton").setText("Say!").addMouseUpHandler(chatHandler);

The preceding part of the code is a classical UiApp code with a button and two text areas, one to see the conversation (as read only) and a second to actually type in the messages. The issue at this point was to find a way to update the conversation panel in every instance of the script in different documents at the same moment so that each user would see the new messages without having to do anything.

As I already mentioned before, each instance of a script is new and completely ignores what is happening in another instance, so we had to use a common memory where we could store the messages. Doing this was simple as ScriptProperties belongs to the script (that is the document), so each document user has access to it; it is typically a shared resource.

On the other hand, each UI is unique for each user, and the only way to actually update the UI is to use a handler function.

However, as we have seen before, a handler function needs a trigger, generally some action from the user: a mouse click or the pressing of a key.

In this case, we don't want the user to do anything; he should receive the chat messages automatically.

What I used here is a special property of the checkbox widget (see the documentation at https://developers.google.com/apps-script/reference/ui/check-box?hl=fr-FR#setValue(Boolean,Boolean) that is able to fire an event when its value is changed programmatically.

Using this feature, we can easily create a timer based on a positive feedback loop on this checkbox. After a chosen delay, the handler function changes the checkbox value that in turn triggers the handler function, and so on.

We intentionally limit the number of cycles to let it work for a "reasonable" amount of time and stop after a certain period of inactivity. (In this test version, the limit is set to 30 seconds so that we see it working without having to wait for 5 minutes.) The following code depicts this:

var chkHandler = app.createServerHandler('autoUpdate').addCallbackElement(vPanel);

var chk = app.createCheckBox().setId('chk').addValueChangeHandler(chkHandler);

vPanel.add(textArea);

vPanel.add(textBox);

vPanel.add(chatButton);

vPanel.add(chk);

app.add(vPanel);

DocumentApp.getUI().showSidebar(app);

return app;

}

function sayChat(e){

var app = UiApp.getActiveApplication();

var user = '['+getCurrentUser()+'] : ';

if(e.parameter.messageBox=="You have been put offline because you didn't type anything for more than 5 minutes..., please click here to refresh the conversation"){

app.getElementById("chatBox").setText(content);// refresh chatBox

app.getElementById('messageBox').setText('');// clear messageBox

ScriptProperties.setProperty('chatTimer',0);// reset counter

return app;

}

if(e.parameter.source=='messageBox'&&e.parameter.keyCode!=13){return app};// if we are simply typing a message then return immediately

var content = ScriptProperties.getProperty('chatContent');

ScriptProperties.setProperty('chatContent',content+"\n"+user+e.parameter.messageBox);// store the conversation with user names

app.getElementById("chatBox").setText(content+"\n"+user+e.parameter.messageBox+'\n');// update the conversation area

app.getElementById('messageBox').setText('');// and clear the message area

app.getElementById('chk').setValue(true,true);// start the timer

ScriptProperties.setProperty('chatTimer',0);// initialize the counter

return app;

}

We use e.parameter.source to identify which event caused the handler to be triggered because the same handler applies to the "say" button and the message's text area. The source parameter in e tells us if it is a button or a text area.

function autoUpdate(){ // this function called on checkBox event

var app = UiApp.getActiveApplication();

var content = ScriptProperties.getProperty('chatContent');

var counter = Number(ScriptProperties.getProperty('chatTimer'));

++counter;// increment counter

if(counter>30){ // if > limit then stop the timer, change this value to get a longer delay

app.getElementById('chk').setValue(false);

app.getElementById('messageBox').setText("You have been put offline because you didn't type anything for more than 5 minutes..., please click here to refresh the conversation");

return app;

} ; // else update the chat Area

ScriptProperties.setProperty('chatTimer',counter);// store the counter value

var content = ScriptProperties.getProperty('chatContent');

app.getElementById("chatBox").setText(content+'*'); // the * is there only for test purpose

app.getElementById('chk').setValue(false);

Utilities.sleep(950);// wait 950 millisec to get approximately a one second total delay

app.getElementById('chk').setValue(true,true).setText('timer = '+counter);// create a new event (= activate the positive feedBack loop)

return app;// update the UI

}

function showUser(){

DocumentApp.getUi().alert("Your userId is: "+getCurrentUser());

}

function getCurrentUser(){

var email = Session.getEffectiveUser().getEmail();

var user = UserProperties.getProperty('user')==null || UserProperties.getProperty('user')=='' ? email.substring(0,email.indexOf("@")) : UserProperties.getProperty('user');

return user;

}

In the following code, the setUserName() function allows us to change the username. The shown username is stored in UserProperties, which is different for every user, of course. When we change this name, it shows a message in the chat messages area to warn every other user about this (no way to cheat!).

function setUserName(){

var oldName = getCurrentUser();

var user = DocumentApp.getUi().prompt('Please enter your desired user name as it will be shown\n(old name was '+oldName+', default is your mail ID)', DocumentApp.getUi().ButtonSet.OK_CANCEL);

if(user.getSelectedButton() == DocumentApp.getUi().Button.CANCEL) return;

ScriptProperties.setProperty('chatContent','\n['+oldName+'] is changing his user name to ['+user.getResponseText()+']\n');// show the new name along with a warning in the chat history

UserProperties.setProperty('user',user.getResponseText());// store the new user name

}

If no name has been defined, the username will default to the user's e-mail ID and be stored for later use.

Note that this last function uses a simplified if/then syntax without braces. JavaScript accepts this simplified form but it makes the code less easy to read, so I'd recommend that you keep using normal syntax with parentheses and braces.

The document with the embedded script is viewable as a shared document at the following link:

https://docs.google.com/document/d/1ZEHXFXn5zH8AfnMrImtrQmFoRD1m1PNuq7lnUC8YABI/edit?usp=sharing

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.