INFORMATION NOW

Building an Idea Bank using Google Apps Script

Editor’s Note: This is a guest post by Saqib Ali. Saqib is a Google Apps evangelist at Seagate. He has used Apps Script to create a number of applications that leverage Google Apps. His other interests include the curation of Lewis Carroll’s letter and translation of Urdu poetry to English. -- Ryan Boyd

What is an Idea Bank?

Idea Banks are repositories for innovative ideas that Seagate employees can submit, and others can vote on those ideas. Before Google Apps Script we had a custom built Idea Bank on the LAMP stack. With the release of the UI Services in the Google Apps Script, we wanted to port that Idea Bank to Google Apps to easily manage idea submissions in a Google Spreadsheet.

Designing the Idea Bank

A typical Idea Bank consists of three basic functions:

1. Ability to submit and store ideas to a central database.
2. Ability to vote on ideas.
3. Ability to add description comment on ideas.

A traditional application would probably use a Relational Database like MySQL to store the ideas. However we found that using Google Spreadsheet to store the ideas provides two inherent benefits:

1. Entered data can be easily managed using the Spreadsheet Editor;
2. Revision history. Since Spreadsheet provides built-in revision history, we don’t have to create a system for tracking the changes to the submitted ideas.

The number of votes, and the voters are tracked using cells in the spreadsheet. For voters we used the Session.getUser().getEmail() to get the email address of the logged in user, and store them in the spreadsheet.

Since the Ideas Bank is embedded in a Google Site, we were able to simply use the Google Sites Page as a place holder to add description and comments to the ideas. Once the idea is submitted, a Google Sites page gets created corresponding to that idea from predefined template using the createPageFromTemplace() function. The submitter can then add detailed description in the template. Others can add comments to that Site pages.

Implementation Details

Using Spreadsheet Services to Manage Data

All the data is stored in a Google Spreadsheet, which makes it easy for the Idea Bank manager to manage (delete, remove, modify) the ideas using the Spreadsheets Editor.

Code snippet for adding new ideas to the spreadsheet:

var ss = SpreadsheetApp.openById("");  // Spreadsheet id goes here
SpreadsheetApp.setActiveSpreadsheet(ss);
ideas_sheet =  SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Ideas");
   
var last_row = ideas_sheet.getLastRow();
var next_empty_row = last_row+1;
   
ideas_sheet.setActiveCell("A"+next_empty_row);
ideas_sheet.getActiveCell().setValue(e.parameter.ideadescription);   
ideas_sheet.setActiveCell("B"+next_empty_row);
ideas_sheet.getActiveCell().setValue(Session.getActiveUser().getUserLoginId());
ideas_sheet.setActiveCell("E"+next_empty_row);
ideas_sheet.getActiveCell().setValue(Session.getActiveUser().getEmail());

Code snippet to read the ideas from the Spreadsheet and display them:

var ss = SpreadsheetApp.openById(""); // Spreadsheet id goes here
SpreadsheetApp.setActiveSpreadsheet(ss);
ideas_sheet =  SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Ideas");
 
var last_row = ideas_sheet.getLastRow();
var last_column = ideas_sheet.getLastColumn();
var sheet_array = ideas_sheet.getRange(2, 1, last_row, last_column).getValues();
    
var submitIdeaButton = app.createButton("I have another idea");
var submitIdeaButtonHandler = app.createServerClickHandler(showSubmitIdeaDialog);
submitIdeaButton.addClickHandler(submitIdeaButtonHandler);
applyCSS(submitIdeaButton, _submitideabutton);
   
var ideaContents = app.createGrid().resize(last_row,3);
ideaContents.setId("ideacontents");   
ideaContents.setWidth("100%");
ideaContents.setCellSpacing(0);
scrollPanel.add(ideaContents);
app.add(scrollPanel);
    
for (var row_i = 0; row_i < last_row-1; row_i++) {
  var ideaDescriptionLabel = app.createLabel(sheet_array[row_i][0]).setStyleAttribute("font","16px Sans-serif").setWordWrap(true);
  var submitter = sheet_array[row_i][1].split("@");   
  var ideaAuthor = app.createLabel(submitter[0]).setStyleAttribute("font","10px Courier New, Courier, monospace").setStyleAttribute("color", "#CCC")
  ideaContents.setWidget(row_i, 0, app.createVerticalPanel().add(ideaDescriptionLabel).add(ideaAuthor)).setStyleAttribute("overflow","visible").setStyleAttribute("white-space","normal !important");
     
  //Button to display the voters	
  var numberOfVotesForm = app.createFormPanel().setId(numofvotesform);
  var numberOfVotesFormContent = app.createVerticalPanel()
  numberOfVotesForm.add(numberOfVotesFormContent);
  numberOfVotesFormContent.add(app.createTextBox().setName(ideaID).setText(row_i + "").setVisible(false).setSize("0","0"));
  numberOfVotesFormContent.add(app.createTextBox().setName(voters).setText(sheet_array[row_i][4]).setVisible(false).setSize("0","0"));
  var numberOfVotesButton = app.createButton(countVotes(sheet_array[row_i][4]) + " vote(s)").setId("numberOfVotesButton"+row_i);
  applyCSS(numberOfVotesButton, _numofvotesbutton);
  var numberOfVotesButtonHandler = app.createServerClickHandler(showVotersDialog);
  numberOfVotesButtonHandler.addCallbackElement(numberOfVotesFormContent);
  numberOfVotesButton.addClickHandler(numberOfVotesButtonHandler);
  numberOfVotesFormContent.add(numberOfVotesButton);
      
  //Button to cast a vote
  var voteForm = app.createFormPanel().setId(voteform);
  var voteFormContent = app.createVerticalPanel();
  voteForm.add(voteFormContent);     
  voteFormContent.add(app.createHidden(ideaID, row_i + "").setSize("0","0"))
 
  // Identify the function schedule as the server click handler
  var voteButton = app.createButton(I like this!).setId("voteButton"+row_i)
  var voteButtonHandler = app.createServerClickHandler(casteVote);
  voteButtonHandler.addCallbackElement(voteFormContent);
  voteButton.addClickHandler(voteButtonHandler);
  if (sheet_array[row_i][4].indexOf(Session.getActiveUser().getEmail())>-1)
    voteFormContent.add(voteButton.setText("Thanks!").setEnabled(false));
  else
    voteFormContent.add(voteButton);
     
  ideaContents.setWidget(row_i, 1, numberOfVotesForm);
  ideaContents.setWidget(row_i, 2, voteForm);
}
app.add(submitIdeaButton);

Using Ui Services to embed User Interface in Google Sites

Ui Service was used to build the front end for the app. UI Services are based on GWT, so it is a good idea to have a basic understanding of the GWT framework. The following were used in building this app:

1. Horizontal panel to display each idea;
2. Vertical panel to display the list of ideas;
3. Simple button to cast votes and submit ideas;
4. Form panel to accept new ideas.

A real live working example is available here. Full source code is available here.

But why Google Apps Script?

So why did I choose Google Apps Script? Well for one it is at no extra cost, comes with your Google Account, it is in the cloud (i.e. no servers required), integrates well with Google Sites and Spreadsheets, and most importantly it uses GWT UI widgets. Google Apps Script’s UI Services and the ability to easily integrate with any REST interface make Apps Script an easy choice.

Saqib Ali Saqib is a Google Apps evangelist at Seagate. He has used Apps Script to create a number of applications that leverage Google Apps. His other interests include the curation of Lewis Carroll’s letter and translation of Urdu poetry to English.