Apps Script
Hide

Building a Sites Application: Understanding Script Design

Saurabh Gupta, Google Developer Relations
March 3, 2011

Developing a Script to Read and Write from a Spreadsheet

The time booking application stores its data in a Google Spreadsheet. We need a script that can easily read and write time sheet data to a spreadsheet. In this tutorial, we are going to develop a script that will do just that.

Section 1 - How the Time Sheet Data Model Works

The time sheet data model is based on the following assumptions:

  • A user can submit time to two or more categories. In this example, we are going to use only two categories - (1) Billable Hours (2) Non-Billable Hours. The data model that we are designing can be extended to more categories if need be.
  • Time submitted by a user for each category is called a time record. A time record is stored in a row in the spreadsheet. A time record for one user for a particular month and in a particular category is unique.
  • A time sheet is a collection of two or more time records for a unique user for a unique month.
  • A user can only submit one time sheet per month.
  • When users modify their time submissions for the month, their time records are updated in the spreadsheet.

Section 2 - Implementing the Time Sheet Data Model in a Spreadsheet

Understanding the Spreadsheet

In the first part of this tutorial, you made a copy of a Google Spreadsheet for your own application. Here's the explanation of the columns in that spreadsheet.

A time record is the time booked for a category by a user for a particular month. Each time record is stored in a row in the Spreadsheet. To capture this data, we use a spreadsheet with the following columns:

  • TimeStamp - When the user last submitted a TimeRecord
  • UserID - The user who submitted the data
  • TimeBooked - Number of hours booked
  • Type - Identifies the category in which time was booked. The application uses the value 1 for Billable Hours and the value 2 for Non-Billable hours. This example can be expanded to include more types.
  • Month - Represents the numeric value of the month for which the time record was submitted
  • Year - Represents the year in which the time record was submitted.
  • ID - This field is a concatenation of year+month. It can be used to uniquely identify a time sheet, which is all time booked by a particular user for a particular month. Two or more time records for a user for a particular month will have the same ID. The terms ID and uniqueID are used interchangeably throughout this tutorial.

Section 3 - Defining the TimeRecord and TimeSheet Classes in the Script

We need to define two classes in a script that model TimeRecord and TimeSheet. The next two sections show you the code for the two classes.

JavaScript classes are defined using the function method. All member variable of the classes s are declared using the this keyword. For example, the timeStamp member variable in the TimeRecord_ class is declared as this.timeStamp .

TimeRecord Class

Let's start with a TimeRecord, because it represents a lower level of time sheet data. The code that follows shows the TimeRecord_ class. 

/*
* 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 particular category (Billing Hours)
* for a particular month
*/
function TimeRecord_() {
  this.timeStamp = "";
  this.timeBooked = "";
  this.month ="";
  this.year ="";
  this.timeType = "";
}

Time Sheet Class

The TimeSheet_ class described in the code sample below contains two main fields.

  • TimeSheet.timeRecord is an array of TimeRecord_ objects that are a part of a TimeSheet_ object.
  • TimeSheet.uniqueID is the uniqueID of a TimeSheet_ object for a particular user. A combination of uniqueID and userID makes every TimeSheet_ unique.
/*
*
* 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 is the ID column in the spreadsheet
  this.uniqueID = "";

  /*
   * Retrieves the TimeSheet_ for the user for
   * current month
   *
   * @param Label Label which needs to be decorated.
   */

  this.getTimeSheet = function(userID, uniqueID) {
       .........
       .........
  }

  // this method updates the TimeSheet for
  // the current week
  this.updateTimeSheet = function(userID,uniqueID, timeRecordArray) {
       .........
       .........
  }

}

To view the code:

  1. In the spreadsheet that you copied, click Tools > Script Editor. The Script Editor opens and displays the script.
  2. Inspect the TimeRecord and TimeSheet classes.

Section 4 - Methods to Write TimeRecord and TimeSheet Data to the Spreadsheet

The TimeSheet object contains methods to save or update the TimeSheet. A TimeSheet is saved or inserted when its corresponding TimeRecord objects are saved or inserted in the spreadsheet. You will notice that TimeSheet itself does not have any specific identifier. Because TimeSheet is a collection of TimeRecords, saving TimeRecord objects is equivalent to saving a TimeSheet. The TimeSheet.updateTimeSheet() method will save or insert a TimeSheet. If a TimeSheet is being submitted for the first time, this method inserts new rows in the spreadsheet. Otherwise, this method justs update the existing TimeRecord rows. 

  // 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;
  }

/*
* 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);

}

Running the Test Script

The script contains the following test function, which you'll run to test inserting data into the spreadsheet.

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);

}
  1. Click Tools > Script Editor.
  2. 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; .

  3. In the script, replace the SPREADSHEET_ID variable in the script with the spreadsheet key you obtained from the URL.
  4. On the Script Editor toolbar, open the Select a function to run drop-down list.
  5. Select the testInsertTimeSheet function.
  6. Click the Run icon. This executes the test method testInsertTimeSheet().
  7. Inspect the spreadsheet for newly saved TimeSheet data.

Section 5 - Developing Scripts to Read TimeSheet Data from the Spreadsheet

A TimeSheet is a unique combination of userID and uniqueID. The TimeSheet class contains a method called TimeSheet.getTimeSheet(userID, uniqueID) . This method can be used to retrieve a TimeSheet for any user for a given month. This method iterates over the entire spreadsheet to find all the TimeRecords that match the given userID and uniqueID. Each matching TimeRecord is stacked in an array which then becomes part of a TimeSheet. This is in the code that follows. 

// 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;
  }

  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;
      }
    }
  }

Action:

  1. Click Tools > Script Editor.
  2. Click the Run button and choose testGetSheet().

    3. /*
* Logs a TimeSheet
*/
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);
}
  3. Click View > Logs and view all the Logger statements generated by this method.

Section 6 - Designing a UiApp Form

The Time Booking application uses a form constructed with UI Services in Google Apps Script.

  • Form Layout - We use a VerticalPanel widget to stack all the form widgets. These widgets include labels, text boxes, and buttons. In order to organize our code better, we have created a separate createForm_ which is called in doGet method. This helps us manage our code well within a long scripts. You will notice that the createForm_ method sets up the entire form and its widgets in a VerticalPanel. The container here is the application, which is returned back to the doGet method. See the code snippet below to understand the use of VerticalPanel. 
    
/*
* @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");

  .........
  .........
  .........
  .........
  .........

  // add all the widgets to the application
  vPanel.add(message);
  vPanel.add(monthMessageLabel);
  vPanel.add(monthDataLabel);
  vPanel.add(projectMessageLabel);
  vPanel.add(projectSubMessageLabel);

  .........
  .........

  container.add(vPanel);
}
  • Response Layout - When a user submits the form, as part of the feedback we present the submitted data in a filled out form. We use a similar approach earlier in the script when we set up this page. The method submitPage_ contains code that sets up the reponse panel widgets. This method is called from submitHandler_ method.
  • Decorating Widgets - We created a reusable method called decorateLabel_ which contains reusable code for decorating all the widgets on both the panels. We use the method available in most widgets called setStyleAttribute to setup CSS styles for the widgets. See the code below: 
    /*
* @param UiInstance Private method which creates all the widgets in the App
*/
function createForm_(container) {
  // create a Vertical Panel
  var vPanel = container.createVerticalPanel();
  .........
  .........

  // 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");

  .........
  .........

  // add all the widgets to the application
  .........
  .........

  vPanel.add(projectMessageLabel);
  vPanel.add(projectSubMessageLabel);
  vPanel.add(projectType);
  vPanel.add(projectTimeTextBox);
  .........
  .........

  container.add(vPanel);
}

/*
* 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");
}
  • Capturing Submit Clicks - Apps Script UI Services have a callback mechanism to capture clicks on widgets. This requires that a ServerClickHandler be created and attached to a widget such as Button. If you look at the createForm_ then you will notice that the following code snippet. 
    
  // submit form
  var submitButton = container.createButton();
  submitButton.setText("Submit Time Sheet");
  decorateLabel_(submitButton);

  // submit handlers
  var submitServerHandler = container.createServerClickHandler('submitHandler_');
  submitButton.addClickHandler(submitServerHandler);
  submitServerHandler.addCallbackElement(vPanel);

    This code shows three important steps in creating a ServerClickHandler (this can be extended for any type of handler). (1) How a ServerClickHandler is created and attached to a callback method. In this example, the callback method is submitHandler_ . So when the handler is invoked by a click, Apps Script is going to call submitHandler_ method in the script. (2) A button (or any other widget) registers itself to the callback handler. Thus, when a user clicks this button, this button now know which handler to notify this click to. (3) The handler itself could be capturing clicks from a group of widgets in a panel. Thus, ServerClickHandler registers itself to the clicks orignating from the vPanel .

Summary

You have now learned the inner workings of the Time Booking application. This application involves complex use of Spreadsheet Services and UI Services. The application code in its current form can be extended to meet any specific use case.

Except as otherwise noted, the content of this page is licensed under the Creative Commons Attribution 3.0 License, and code samples are licensed under the Apache 2.0 License. For details, see our Site Policies.

Send feedback about...

This page
Documentation feedback
Apps Script
Product feedback