Saurabh Gupta, Google Developer Relations
March 3, 2011

Overview

The time booking application enables users to record the time they spent on various activities during a particular month. The application assumes that the users are employees of a law firm. The users can go to a Google Sites page that hosts this application. When users visit this Sites page, they see an input form where they can record time in two categories. The first category is time that can be billed to clients. The other is non-billable time spent on training, vacation, and other activities. The users can book time only for the current month. If the users want to modify their submissions, they can the Sites page again and the form will display the values that were submitted earlier.

The application uses both Google Sites, which hosts the input form (the user interface), and Google Spreadsheets, which stores and processes the data users submit on the form. The Sites page contains a script that creates the input form, collects the data, and stores it to the Spreadsheet. The Spreadsheet contains a script that process the data.

This tutorial teaches you how to create a time booking application in Google Sites using Google Apps Script. At the end of the tutorial, you will have a working application that can be used by Google Apps domain account members to report time. This is a step-by-step tutorial. The second part of the tutorial will analyze the script in detail, including its design, data model, and classes. The time booking application will store its data in a Google Spreadsheet. Therefore, we need to create a script that can easily read and write time sheet data to a spreadsheet. In this tutorial, we are going to learn how to develop a script that will do just that.

• Section 1: Creating a Spreadshet to Store the Time Sheet Data
• Section 2: Creating a Google Site
• Section 3: Creating a Sites Page to Host the Time Booking Application User Interface
• Section 4: Copying the Script to the Sites Page
• Section 5: Embedding a Google Apps Script Gadget in the Sites Page
• Section 6: Running the Time Booking Application

Section 1: Creating a Spreadsheet to Store the Time Sheet Data

For purposes of this tutorial, we've created a Google Spreadsheet that contains the first script you need for this tutorial. You create your own copy of the spreadsheet to start the tutorial.

To create your own spreadsheet:

1. Open this Spreadsheet. The sample script is included in the spreadsheet.
2. Click File > Make a Copy and give the spreadsheet copy a new name.
3. Click File > Save. The new spreadsheet is saved.
4. Copy the spreadsheet key from the URL that is in the browser window. For example, this might be the URL:

   https://spreadsheets.google.com/ccc?key=0AucO96uye4N5dHNCeWVSRmZqT0pYZkhDT1VVNy1HdUE&hl;=en&authkey;=CMvgq6oK

   The spreadsheet key is 0AucO96uye4N5dHNCeWVSRmZqT0pYZkhDT1VVNy1HdUE , or everything between key= and &hl; . The following graphic also shows the spreadsheet key:

   
5. Save the spreadsheet key in a text editor. You'll need it later in this tutorial.

We'll go on to explain how this spreadsheet works in the next part of this tutorial.

Section 2: Creating a Google Site

We need to create a Google Site where the user interface will be hosted and used to enter time records. If you do not already have a Google Site, use the instructions in this section to create a Site. If you already have a Site where you want to host this application, skip the instructions below and go to Section 3.

To create a Google Site:

1. Open this URL to go to Google Sites. You will see the Google Sites that you own. If do not own any Sites, you need to create a new Google Site.
2. Click Create Site.
3. In the Name your Site field, type Time Booking Example. The field Your site will be located at this URL auto-completes.
4. Type in the confirmation code.
5. Click Create Site. Your newly-created Site should look like this:

You can perform various actions to manage your site. The screen shot below shots the locations of the Create Page , Edit Page , and More Actions buttons, which we will be using in this tutorial.

Section 3: Creating a Sites Page to Host the Time Booking Application User Interface

Now we need to create the Google Sites Page that will host the script and display the user interface for the time booking application. This is very easy.

To create a Sites page:

1. On the top right hand corner of the Site, click Create Page.
2. Choose the default Web Page template.
3. In the Name field, type Time Booking.
4. To change the location of the page, select Put page under Home or click Choose a different location and select the new location on the dialog box.
5. Click Create Page.

Section 4: Copying the Script to the Sites Page

To insert a script into the new Sites page, you need to start the Script Editor.

To start the Script Editor and insert the script:

1. Click More Actions > Manage Site.
2. In the left-hand sidebar, click Apps Scripts.
3. Click Launch Editor.
   
   
4. Copy and paste the following code into the editor, ensuring that you paste over the default function that is in the editor. Note that the paste operation may take a few seconds because of the length of the script. :

   // global constants
   var LABEL_WIDTH="200";
   var TEXTBOX_WIDTH="400";
   // Spreadsheet where all the time booking data is stored
   var SPREADSHEET_ID = "PASTE_SPREADSHEET_KEY_HERE";
   
   // Type of TimeRecord
   var PROJECT_TYPE = "1";
   var NON_PROJECT_TYPE = "2";
   
   /*
   * @return UiInstance Application created using UI Services
   *                    which includes all the widgets and callback handler hooks
   *
   */
   function doGet(e) {
    var app = UiApp.createApplication().setTitle("Employee Time Sheet Application");
    //create form widgets
     createForm_(app)
     return app;
   }
   
   
   /*
   * @param UiInstance Private method which creates all the widgets in the App
   */
   function createForm_(container) {
     // create a Vertical Panel
     var vPanel = container.createVerticalPanel();
     vPanel.setWidth("810");
     vPanel.setStyleAttribute("padding","20px");
     vPanel.setStyleAttribute("fontSize", "12pt");
     vPanel.setId("panel");
   
     // label for future message
     // default mode is hidden
     var message = container.createLabel();
     message.setStyleAttribute("fontSize", "8pt");
     message.setStyleAttribute("font-weight", "normal");
     message.setStyleAttribute("color","green");
     message.setId("message");
     message.setVisible(false);
   
     // create all the widget
     // each label is 200px and Text Box is 400px
     var monthMessageLabel = container.createLabel().setText("Record Time for the Month:");
     monthMessageLabel.setWidth(LABEL_WIDTH);
     decorateLabel_(monthMessageLabel);
     var monthDataLabel = container.createLabel().setText("February, 2011");
     monthDataLabel.setStyleAttribute("fontSize","12pt");
     monthDataLabel.setStyleAttribute("margin-top","5px");
     monthDataLabel.setStyleAttribute("font-weight","bold");
     monthDataLabel.setStyleAttribute("color","blue");
     monthDataLabel.setWidth(LABEL_WIDTH);
   
     // Project Work Time
     var projectMessageLabel = container.createLabel().setText("Billable Hours");
     var projectSubMessageLabel = container.createLabel().setText("(Time you spent on client-billable hours)");
     var projectType = container.createHidden("projectType").setValue(PROJECT_TYPE);
     projectMessageLabel.setWidth(LABEL_WIDTH);
     decorateLabel_(projectMessageLabel);
     decorateLabel1_(projectSubMessageLabel);
     var projectTimeTextBox = container.createTextBox();
     projectTimeTextBox.setWidth(TEXTBOX_WIDTH);
     projectTimeTextBox.setMaxLength(80);
     projectTimeTextBox.setName("projectTimeTextBox");
   
     // Non-Project Work Time
     var nonProjectMessageLabel = container.createLabel().setText("Non-Billable Hours");
     var nonProjectSubMessageLabel = container.createLabel().setText("(Time you spent sick, in training, or on vacation)");
     var nonProjectType = container.createHidden("nonProjectType").setValue(NON_PROJECT_TYPE);
     nonProjectMessageLabel.setWidth(LABEL_WIDTH);
     decorateLabel_(nonProjectMessageLabel);
     decorateLabel1_(nonProjectSubMessageLabel);
     var nonProjectTimeTextBox = container.createTextBox();
     nonProjectTimeTextBox.setWidth(TEXTBOX_WIDTH);
     nonProjectTimeTextBox.setMaxLength(80);
     nonProjectTimeTextBox.setName("nonProjectTimeTextBox");
   
     // submit form
     var submitButton = container.createButton();
     submitButton.setText("Submit Time Sheet");
     decorateLabel_(submitButton);
   
     // submit handlers
     var submitServerHandler = container.createServerClickHandler('submitHandler_');
     submitServerHandler.addCallbackElement(vPanel);
     submitButton.addClickHandler(submitServerHandler);
   
     // get current timesheet
     var timeSheet = getCurrentTimeSheet_();
     if (timeSheet.timeRecord.length == 2) {
      message.setText("Last time you entered your Time Sheet:"+timeSheet.timeRecord[0].timeStamp);
      message.setVisible(true);
      projectTimeTextBox.setText(""+timeSheet.timeRecord[0].timeBooked);
      nonProjectTimeTextBox.setText(""+timeSheet.timeRecord[1].timeBooked);
     }
   
     // add all the widgets to the application
     vPanel.add(message);
     vPanel.add(monthMessageLabel);
     vPanel.add(monthDataLabel);
     vPanel.add(projectMessageLabel);
     vPanel.add(projectSubMessageLabel);
     vPanel.add(projectType);
     vPanel.add(projectTimeTextBox);
     vPanel.add(nonProjectMessageLabel);
     vPanel.add(nonProjectSubMessageLabel);
     vPanel.add(nonProjectType);
     vPanel.add(nonProjectTimeTextBox);
     vPanel.add(submitButton);
     container.add(vPanel);
   }
   
   /*
   * @param Event Callback method which is called when a user clicks on Submit.
   *              This method name was provided to the ServerClickHandler class.
   *
   */
   function submitHandler_(e) {
     var app = UiApp.getActiveApplication();
     var timeSheet = getFormData_(e);
     submitPage_(app,e, timeSheet);
     app.close();
     return app;
   }
   
   /*
   * @param Event Retrieve data from UI Widgets after Submit is clicked.
   *
   */
   function getFormData_(e) {
     // initialize TimeRecord_ classes
     var timeRecord1 = new TimeRecord_();
     var timeRecord2 = new TimeRecord_();
     // get data
     var timeStamp = new Date();
     var year = Utilities.formatDate(timeStamp,Session.getTimeZone() ,"yyyy");
     var month = Utilities.formatDate(timeStamp,Session.getTimeZone(), "MM");
     var user = Session.getActiveUser().getUserLoginId();
     var uniqueID = year+"-"+month;
     // put values in TimeRecord_ object
     timeRecord1.timeStamp = timeStamp;
     timeRecord1.timeBooked = e.parameter.projectTimeTextBox;
     timeRecord1.timeType = e.parameter.projectType;
     timeRecord1.month = month;
     timeRecord1.year = year;
     // TimeRecord for second type
     timeRecord2.timeStamp = timeStamp;
     timeRecord2.timeBooked = e.parameter.nonProjectTimeTextBox;
     timeRecord2.timeType = e.parameter.nonProjectType;
     timeRecord2.month = month;
     timeRecord2.year = year;
     // create a TimeRecord_ array
     var timeRecordArray = new Array(2);
     timeRecordArray[0] = timeRecord1;
     timeRecordArray[1] = timeRecord2;
     //create a TimeSheet_
     var timeSheet = new TimeSheet_();
     timeSheet.updateTimeSheet(user,uniqueID,timeRecordArray);
   
     return timeSheet;
   }
   
   /*
   * @param UiInstance Response widgets are attached to the UiInstance page.
   * @param Event      Event object to retrieve data from.
   * @param TimeSheet_ TimeSheet data entered by the user
   *
   */
   function submitPage_(container,e,timeSheet) {
     // hide the form
     var panel = container.getElementById("panel") ;
     panel.setVisible(false);
   
     // response is another panel with data that was submitted
     // create a Vertical Panel
     var vPanel = container.createVerticalPanel();
     vPanel.setWidth("810");
     vPanel.setStyleAttribute("padding","20px");
     vPanel.setStyleAttribute("fontSize", "12pt");
     vPanel.setId("panel");
   
     // label for future message
     // default mode is hidden
     var message = container.createLabel();
     message.setStyleAttribute("fontSize", "12pt");
     message.setStyleAttribute("font-weight", "normal");
     message.setId("message");
     message.setVisible(false);
   
     // create all the widget
     // each label is 200px and Text Box is 400px
     var monthMessageLabel = container.createLabel().setText("You booked the below time for:");
     monthMessageLabel.setWidth(LABEL_WIDTH);
     decorateLabel_(monthMessageLabel);
   
     var monthDataLabel = container.createLabel().setText("February, 2011");
     monthDataLabel.setStyleAttribute("fontSize","12pt");
     monthDataLabel.setStyleAttribute("margin-top","5px");
     monthDataLabel.setStyleAttribute("font-weight","bold");
     monthDataLabel.setStyleAttribute("color","blue");
     monthDataLabel.setWidth(LABEL_WIDTH);
   
     // Project Work Time
     var projectMessageLabel = container.createLabel().setText("Billable Hours");
     var projectSubMessageLabel = container.createLabel().setText("(Time you spent on client-billable hours)");
     projectMessageLabel.setWidth(LABEL_WIDTH);
     decorateLabel_(projectMessageLabel);
     decorateLabel1_(projectSubMessageLabel);
   
     var projectTimeLabel = container.createLabel();
     projectTimeLabel.setText(timeSheet.timeRecord[0].timeBooked+" hours");
     projectTimeLabel.setWidth(TEXTBOX_WIDTH);
     projectTimeLabel.setStyleAttribute("margin-top","5px");
     projectTimeLabel.setStyleAttribute("font-weight","bold");
     projectTimeLabel.setStyleAttribute("color","blue");
   
     // Non-Project Work Time
     var nonProjectMessageLabel = container.createLabel().setText("Non-Billable Hours");
     var nonProjectSubMessageLabel = container.createLabel().setText("(Time you spent sick, in training, or on vacation)");
     nonProjectMessageLabel.setWidth(LABEL_WIDTH);
     decorateLabel_(nonProjectMessageLabel);
     decorateLabel1_(nonProjectSubMessageLabel);
   
     var nonProjectTimeLabel = container.createLabel();
     nonProjectTimeLabel.setText(timeSheet.timeRecord[1].timeBooked+" hours");
     nonProjectTimeLabel.setWidth(TEXTBOX_WIDTH);
     nonProjectTimeLabel.setStyleAttribute("margin-top","5px");
     nonProjectTimeLabel.setStyleAttribute("font-weight","bold");
     nonProjectTimeLabel.setStyleAttribute("color","blue");
   
     vPanel.add(message);
     vPanel.add(monthMessageLabel);
     vPanel.add(monthDataLabel);
     vPanel.add(projectMessageLabel);
     vPanel.add(projectSubMessageLabel);
     vPanel.add(projectTimeLabel);
     vPanel.add(nonProjectMessageLabel);
     vPanel.add(nonProjectSubMessageLabel);
     vPanel.add(nonProjectTimeLabel);
   
     container.add(vPanel);
   
   }
   
   /*
   * @return TimeSheet_ Returns the current month's Timesheet for the user
   *                    This data is pulled from the spreadsheet.
   */
   function getCurrentTimeSheet_() {
    var uniqueID = getUniqueID();
    var userID = Session.getActiveUser().getUserLoginId();
    var timeSheet = new TimeSheet_();
    timeSheet.getTimeSheet(userID,uniqueID);
    return timeSheet;
   }
   
   /*
   * Helper method to decorate a label.
   *
   * @param Label Label which needs to be decorated.
   */
   function decorateLabel_(label) {
      label.setStyleAttribute("fontSize","12pt");
      label.setStyleAttribute("margin-top","20px");
      label.setStyleAttribute("font-weight","bold");
      label.setStyleAttribute("color","black");
   }
   
   /*
   * Helper method to decorate a label using secondary style.
   *
   * @param Label Label which needs to be decorated.
   */
   function decorateLabel1_(label) {
      label.setStyleAttribute("fontSize","8pt");
      label.setStyleAttribute("margin-top","2px");
      label.setStyleAttribute("font-weight","normal");
      label.setStyleAttribute("color","gray");
   }
   
   /*
   * Class that represents an individual TimeRecord_.
   * Each TimeSheet_ is made up of one or more TimeRecord_
   * Each TimeRecord_ is a row in spreadsheet
   *
   * A TimeRecord_ is data represented by a Time Entered
   * by a user for a pariticular category (Billing Hours)
   * for a particular month
   */
   function TimeRecord_() {
     this.timeStamp = "";
     this.timeBooked = "";
     this.month ="";
     this.year ="";
     this.timeType = "";
   }
   
   /*
   * Class that represents an individual TimeSheet_.
   * Each TimeSheet_ is made up of one or more TimeRecord_
   *
   * A TimeSheet_ is data represented by a Time Entered
   * by a user for all categories (ex. Billing Hours)
   * for a particular month. In the spreadsheet, it will be
   * more than one row of user data.
   */
   function TimeSheet_() {
     this.timeRecord = new Array();
     this.userID = "";
     // this is string which describes unique sheet
     this.uniqueID = "";
   
     /*
      * Retrieves the TimeSheet_ for the user for
      * current month
      *
      * @param Label Label which needs to be decorated.
      */
   
     this.getTimeSheet = function(userID, uniqueID) {
       // initialize TimeSheet as an Array (it contains TimeRecord)
       this.userID = userID;
       this.uniqueID = uniqueID;
       var sheet  = SpreadsheetApp.openById(SPREADSHEET_ID).getSheetByName("TimeBooking");
       var lastRow = sheet.getLastRow();
       var range = sheet.getRange("A2:G"+lastRow);
       // iterate over the entire range to find TimeSheets
       var values = range.getValues();
       for(var ii=0; ii < values.length; ii++) {
        // check for values of id and similar
         if ((values[ii][6] == uniqueID) && (values[ii][1] == userID)) {
           // we have a TimeRecord, create one now
           var timeRecord = new TimeRecord_();
           timeRecord.timeStamp = values[ii][0];
           timeRecord.timeBooked = values[ii][2];
           timeRecord.type = values[ii][3];
           timeRecord.month = values[ii][4];
           timeRecord.year = values[ii][5];
           var size = this.timeRecord.length;
           this.timeRecord[size] = timeRecord;
         }
       }
     }
   
     // this method updates the TimeSheet for
     // the current week
     this.updateTimeSheet = function(userID,uniqueID, timeRecordArray) {
   
       // check if the record exists
       var sheet  = SpreadsheetApp.openById(SPREADSHEET_ID).getSheetByName("TimeBooking");
       var lastRow = sheet.getLastRow();
       var range = sheet.getRange("A2:G"+lastRow);
       // iterate over the entire range to find TimeSheets
       var values = range.getValues();
       var recordExists = false;
       var recordRowNum = lastRow+1;
       for(var ii=0; ii < values.length; ii++) {
        // check for values of id and similar
         if ((values[ii][6] == uniqueID) && (values[ii][1] == userID)) {
           // we have a TimeRecord, create one now
           recordExists = true;
           recordRowNum = ii+2;
           break;
         }
       }
       // save
       saveTimeRecord_(userID, uniqueID, timeRecordArray, recordRowNum);
       this.timeRecord = timeRecordArray;
     }
   
   }
   
   // weekId is yyyy-ww
   function getUniqueID() {
     var timeStamp = new Date();
     var year = Utilities.formatDate(timeStamp,Session.getTimeZone() ,"yyyy");
     var month = Utilities.formatDate(timeStamp,Session.getTimeZone(), "MM");
     var uniqueID = year+"-"+month;
     return uniqueID;
   }
   
   /*
   * Saves an individual TimeRecord_ to the Spreadsheet
   */
   function saveTimeRecord_(userID, uniqueID, timeRecord, rowNum) {
     var endRange = rowNum+1;
     var sheet  = SpreadsheetApp.openById(SPREADSHEET_ID).getSheetByName("TimeBooking");
     var range = sheet.getRange("A"+rowNum+":G"+(endRange));
     // get values
     var values = new Array(2);
     for(var i = 0; i < 2; i++) {
       values[i] = new Array(6);
       values[i][0] = timeRecord[i].timeStamp;
       values[i][1] = userID,
       values[i][2] = timeRecord[i].timeBooked;
       values[i][3] = timeRecord[i].timeType;
       values[i][4] = timeRecord[i].month;
       values[i][5] = timeRecord[i].year;
       values[i][6] = uniqueID;
     }
     // save the values
     range.setValues(values);
   
   }
   
   
   /*
   * Below are all the tests
   */
   function testGetSheet() {
     var timeSheet = new TimeSheet_();
     timeSheet.getTimeSheet("jackm","2011-02");
     Logger.log("userid:"+timeSheet.userID);
     Logger.log("uniqueID:"+timeSheet.uniqueID);
     Logger.log("Time Booked for Type:"+timeSheet.timeRecord[0].timeType+" Hours:"+timeSheet.timeRecord[0].timeBooked);
     Logger.log("Time Booked for Type:"+timeSheet.timeRecord[1].timeType+" Hours:"+timeSheet.timeRecord[1].timeBooked);
   
   }
   
   function testUpdateSheet() {
     var timeSheet = new TimeSheet_();
     timeSheet.getTimeSheet("jackm","2011-02");
     timeSheet.timeRecord[1].timeBooked = 5;
     timeSheet.updateTimeSheet (timeSheet.userID, timeSheet.uniqueID, timeSheet.timeRecord);
   }
   
   function testInsertTimeSheet() {
    var timeSheet = new TimeSheet_();
    var timeRecord1 = new TimeRecord_();
    var timeRecord2 = new TimeRecord_();
   
    // set the values
    var timeStamp = new Date();
    var year = Utilities.formatDate(timeStamp,"America/New_York" ,"yyyy");
    var month = Utilities.formatDate(timeStamp,"America/New_York", "MM");
    timeRecord1.timeStamp = timeStamp;
    timeRecord1.timeBooked = 200;
    timeRecord1.timeType = 1;
    timeRecord1.month = month;
    timeRecord1.year = year;
   
    timeRecord2.timeStamp = timeStamp;
    timeRecord2.timeBooked = 500;
    timeRecord2.timeType = 2;
    timeRecord2.month = month;
    timeRecord2.year = year;
   
    var timeRecordArray = new Array(2);
    timeRecordArray[0] = timeRecord1;
    timeRecordArray[1] = timeRecord2;
    timeSheet.updateTimeSheet("jackm", year+"-"+month, timeRecordArray);
   
   }

5. Copy the script key you saved to a text editor in Section 1.
6. Paste it on Line 5 between the quotation marks, substituting the key for the text PASTE_SPREADSHEET_KEY_HERE.
7. Click the Save icon.
8. Give the script a name and click Save.
9. Save a version of your script by clicking File > Manage versions, then Save New Version, then OK.
10. To publish the script as an application that Google Sites can access, click Publish > Deploy as web app. In the Who has access to the app list menu, select Anyone if you want users other than you to be able to see the app. Click Deploy.
11. A dialog will provide you with a URL for your published script. Copy the URL; you will use it in the next section.
12. Close the Script Editor window.
13. Click the Return to Site link. You have now created a script in Google Sites.

Section 5: Embedding a Google Apps Script Gadget in the Sites Page

The script is installed and we need to embed it in the Sites page. The Sites page will display the user interface for the application. All scripts are embedded into a Sites page as Google Apps Script Gadgets.

To embed a Gadget in a Sites page:

1. Click Edit Page
2. Click Insert > Apps Script Gadget.
   
   
3. Paste the URL for your published script into the URL field of the dialog that appears.
4. Click Select, then Save.

You now have an Apps Script Gadget embedded in your Sites page.

Section 6: Running the Time Booking Application

Our time booking application is ready now. Look on the left-hand side of your sites page for the URL of the application. To run the application, click the URL. You'll see the following:

Next Steps

In Part 2 of the tutorial, we'll go into some detail about how the time booking application works.