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:
Ability to submit and store ideas to a central database.
Ability to vote on ideas.
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:
Entered data can be easily managed using the Spreadsheet Editor;
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:
Horizontal panel to display each idea;Vertical panel to display the list of ideas;Simple button to cast votes and submit ideas;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.
8 comments :
Hi Saquip,
In you first code snippet:
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());
I don't believe you need to activate a cell before setting its value. Could save some code and trips to the server.
ScampMichael
A better way might be:
ideas_sheet.getRange("A" + next_empty_row + ":C" + next_empty_row).setValues([e.parameter.ideadescription, Session.getActiveUser().getUserLoginId(), Session.getActiveUser().getEmail()])
All in one with one trip.
Thanks Michael. I agree. Excellent suggestion. It's never a good idea to call the services/apis unnecessarily.
Great interface! Looking forward to all the cool things that can be done with Google Script.
Thanks James!
I too am very impressed with the GWT widget support that Google has added to Google Apps Script. It has made the creation of user interfaces a walk in the park. And no knowledge of AJAX required :)
Hi all,
I am trying to install this but I am missing something. Would someone tell me where should I paste it? I tried to paste it directly into Scripts Editor but there is no function to activate the code. Could someone provide me newbie step by step instructions :) Thank you so much
Hi,
i just copyed and past the source and is full of errors.
I mean, i only write down my Sheet ID and it says:
1: impossible run method text.Split of undefined
2: Impossible run method IndexOF undefined.
Why? what's wrong? i'm just running the same code!..
thanks
interesting concept... I tried out the sample... what causes the delay? It takes about 90 secs to load. Doesn't seem like the data is extraordinarily big and certainly not complex.
What is the limitation on data records before performance is degraded?
Post a Comment