Spreadsheet Service



Google Apps Script (for Spreadsheets)


Google Apps Script has many 'Hook' Services. The following are available services for Apps Script:


Google Apps Script Services

  • Base
  • Calendar
  • Contacts
  • DocList
  • Finance
  • JDBC
  • Language
  • Mail
  • Maps
  • Properties
  • Sites
  • Soap
  • Spreadsheet
  • UI
  • URLfetch
  • Utilities
  • XML 
Here we will look at the Spreadsheet Service and The Mail Service for Apps Script.

Spreadsheet Service for Apps Script


You can Access , Modify and Create (Google Docs) Spreadsheets using the Spreadsheet Services of Apps Script.


Google Apps Script for spreadsheets combined with the existing functionality of Google docs Spreadsheets, like Calculations and Cell functions like Xpath , makes for a very powerful business tool.






Apps Script Sample Function


//Sets the row color depending on the 
value in the "Status" column.
function setRowColors() {
  var range = SpreadsheetApp.getActiveSheet().getDataRange();
  var statusColumnOffset = getStatusColumnOffset();

  for (var i = range.getRow(); i < range.getLastRow(); i++) { 
    rowRange = range.offset(i, 0, 1);
    status = rowRange.offset(0, statusColumnOffset).getValue();
    if (status == 'Completed') {
      rowRange.setBackgroundColor("#E3FBE9");
    } else if (status == 'In Progress') {
      rowRange.setBackgroundColor("#FFFFCC");     
    } else if (status == 'Blocked') {
      rowRange.setBackgroundColor("#FFDDFF");          
    } else {
      rowRange.setBackgroundColor("white");
    }
  }
}

Trigger (for Function) : 
'setRowColors' 'From spreadsheet' 'On form Submit'


Spreadsheet Events


You can run Apps Scripts Manually anytime. (eg. Send Selected form Entry an Email of The entire latest form submissions)

There are also Apps Scripts Event Handlers for Spreadsheet with will run automatically and trigger the Script you have installed.

Apps Script Event Handler


The example above function uses the Apps Script - Event Handler OnFormSubmit and changes the Color of Cells when the form below is submitted .
  • OnOpen - Apps Script is triggered when Opening a spreadsheet.
  • OnEdit - Apps Script is triggered when Editing a spreadsheet.
  • OnInstall - Apps Script is triggered when a Script is Installed.
  • OnFormSubmit - Apps Script is triggered when a Form is Submitted to a Spreadsheet.

Apps Script Triggers


You must create or add a function for the Spreadsheet/Form, then It will be available to trigger.



Add a Apps Script Function: Spreadsheet > Tools > Insert

OR

  1. Create a Apps Script Function(eg. setRowColors): Spreadsheet > Tools > Script Editor
  2. Then set the Trigger: Spreadsheet > Tools > Script Editor > Triggers > Current Script's Triggers
  3. Select Add New Trigger - Under 'Run' you will see the Functions Inserted /created. 
  4. Select your function (eg. setRowColors) and the Events (eg. 'From spreadsheet' + 'On Form Submit')


When creating a template with many triggers, you can also name the function to trigger
function onOpen() { }


Spreadsheet Apps Script Example


The Form has 3 columns Project, Priority and Status. It is important for this particular script that the 'Status' column name does not change. The Order does not matter in this example.
  1. Select your Project.
  2. Select the Project Priority.
  3. Select The Project Status.

The Apps Script will format the Color of your selected Project by the 'Status' Selection.
(ie. Completed, In Progress or Blocked)

Submit the Form and Press F5


Press F5 to Refresh the Spreadsheet Results

Google Spreadsheet



*Update

Output as Html and table query filters tq= tqx=

Table Gadget (Automatic 1min Refresh)

Mail Service - Email Last Row


You can automatically Email the last Row Entry of the Spreadsheet by adding the following Apps Script Mail Function:

Note: The A to D columns last rows are only sent. you may require more Columns , If your form is larger.

function Email() {
  
  var sheet = SpreadsheetApp.getActiveSheet() //Active Spreadsheet
  var last = sheet.getDataRange().getLastRow() //last Row Number
  var value = sheet.getRange("A"+last+":D"+last).getValues() //Last Row Value of A to D columns
  
//Email
  MailApp.sendEmail("name@website.com", "Spreadsheet Information", "***Information sent from Spreadsheet via Apps Script ***" + value);
 
//Browser Message
  Browser.msgBox("Email Information Sent: " + value); 
}​


Follow the on form submit tutorial or add the main Mail function code to the existing colorchange function.

If the spreadsheet is open it will also display a browser message of the Data that was sent by mail.


You MUST RUN any additional new Services and Authorize them before they can run automatically.

The Entire Function Code


//Sets the row color depending on the value in the "Status" column.
function setRowColors() {
  var range = SpreadsheetApp.getActiveSheet().getDataRange();
  var statusColumnOffset = getStatusColumnOffset();

  for (var i = range.getRow(); i < range.getLastRow(); i++) { 
    rowRange = range.offset(i, 0, 1);
    status = rowRange.offset(0, statusColumnOffset).getValue();
    if (status == 'Completed') {
      rowRange.setBackgroundColor("#E3FBE9");
    } else if (status == 'In Progress') {
      rowRange.setBackgroundColor("#FFFFCC");     
    } else if (status == 'Blocked') {
      rowRange.setBackgroundColor("#FFDDFF");          
    } else {
      rowRange.setBackgroundColor("white");
    }
  }

//Emails the Last Row Using The Mail Service
  var sheet = SpreadsheetApp.getActiveSheet() 
  var last = sheet.getDataRange().getLastRow()
  var value = sheet.getRange("A"+last+":D"+last).getValues()
   
 MailApp.sendEmail("name@website.com", "Spreadsheet Information", "***Information sent from Spreadsheet via Apps Script ***" + value);
 
  Browser.msgBox("Email Information Sent: " + value); 
}



Time-Driven Triggers







Javascript for Google Sites


Inline Javascript for Google Sites.
Javascript Example in a embedded Google Gadget.