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
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.
A typical Idea Bank consists of three basic functions:
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:
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.
Session.getUser().getEmail()
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.
createPageFromTemplace()
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);
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:
A real live working example is available here. Full source code is available here.
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 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.
