Google Apps Script (for Spreadsheets)Google Apps Script has many 'Hook' Services. The following are available services for Apps Script: Google Apps Script Services
Here we will look at the Spreadsheet Service and The Mail Service for Apps Script. Spreadsheet Service for Apps ScriptYou 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. | //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 EventsYou 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 HandlerThe example above function uses the Apps Script - Event Handler OnFormSubmit and changes the Color of Cells when the form below is submitted .
Apps Script Triggers![]() Add a Apps Script Function: Spreadsheet > Tools > Insert OR
When creating a template with many triggers, you can also name the function to trigger function onOpen() { } Spreadsheet Apps Script ExampleThe 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.
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 *Update Table Gadget (Automatic 1min Refresh) Mail Service - Email Last RowYou 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 |