Editor's note: This post is cross-posted from the Google Developers Blog.
Editor’s Note: This blog post is authored by Dito’s Steve Webster who is a Google Apps Script Top Contributor - Saurabh Gupta
Recently a company who operates retail stores throughout a few states reached out to Dito. When their associates conduct business discussions with vendors or customers where monetary exchanges are involved, their Ethics Disclosure policy requires a log for each event, a supervisor approval, and committee review.
The customer’s existing disclosure review process was cumbersome and time consuming. The employees would add a disclosure review request to a spreadsheet with 29 columns. The new review request would then be reviewed by their supervisor. The supervisor and the employee would go back and forth exchanging emails and making changes to a dense spreadsheet until an approval was granted. It was clear that the customer needed a workflow solution. They decided to hire Dito to build a workflow solution based on Google Apps Script.
To make the process more user friendly and productive, Dito decided to build a user interface to collect ethics disclosure events, make updates, and automate the routing of email notifications. Writing a Google Apps Script to create a user interface (UI), enabled associates to interact with their contacts to select their supervisor’s email address and simplify the data collection with list boxes. The script sends approval emails with HTML form radio buttons, text box, approve/decline buttons, and a “Post” command to invoke other workflow scripts. Below are some of the main design points for this Approval Workflow script.
The Disclosure Review workflow requires (a) Associates to fill out the Ethics Disclosure form. (b) Supervisor to either approve or decline the submission. (c) If supervisor approves, the Ethics Disclosure Committee is notified. (d) If supervisor declines, the associate is notified to make corrections. (e) After improving the submission, the workflow repeats itself.
Dito developed a custom review request form. A form was developed using Google Apps Script’s UI Services. The form provides the ability to look up data to populate such things as a drop-down list box. This form allowed real-time email look-ups by using the Apps Script’s Contacts service. First efforts included per character look-ups in a list box, but since they have over 1,000 users, it was best to enter the first and/or last name of their supervisor before initiating the look-up (see code snippet below).
var byName = ContactsApp.getContactsByName(searchKey); for (var i in byName) { var emailStr = byName[i].getPrimaryEmail(); // If there is no 'primary' email, try again for the next email if (emailStr == null) var emailStr = byName[i].getEmails()[0].getAddress(); // If emailStr is still null, try again by getting the next email if (emailStr == null) var emailStr = byName[i].getEmails()[1].getAddress(); }
Another dynamic field was the “activity type”. Depending on the selection more form fields are displayed. For example, if the activity type is “Meals”, display a list box to select lunch or dinner.
When an associate submits his/her review request by using the custom form within a spreadsheet, their supervisor receives an email with easy-to-read HTML formatted results. The approval decision, as well as a comment field (e.g. decline reason), is made within the email. This is more productive and prevents unnecessary back and forth into the spreadsheet.
If the request is declined by the supervisor, the associate who submitted the review request receives an email and can review the details. The email also contains a “Continue” button which opens the form in a new browser tab. After corrections are submitted, the supervisor receives another email and the workflow repeats itself.
When approved, the Ethics Disclosure Committee is notified by sending a group email within the script.
Since history records existed in their original spreadsheet form and they wanted to insert these records into the new work flow spreadsheet as a one-time task, an Apps Script was used to copy the data. Of course their columns did not match the new spreadsheet. By using a mapping approach and a “read once” and “write once” technique, the Apps Script quickly made the changes.
function myFunction() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheetByName('Sheet 1'); var rowsWithData = sheet.getRange(2, 1, sheet.getLastRow(), sheet.getLastColumn()).getValues() var sheet1Data = []; var sheet2Data = []; for (var i = 0; i < rowsWithData.length; i++) { switch (rowsWithData[i][4]) // This is the activity type { ... case "Gift": sheet1Data.push([rowsWithData[i][12], rowsWithData[i][13], rowsWithData[i][14]]); sheet2Data.push([rowsWithData[i][15]]); continue; ... default: continue; } } sheet.getRange(2, 6, sheet1Data.length, 3).setValues(sheet1Data); sheet.getRange(2, 12, sheet2Data.length, 1).setValues(sheet2Data); }
Google Apps Script is very powerful and Dito uses it to build interesting solution for its customers. If you are using Google Apps then be sure to use Google Apps Script. You’ll be amazed with what you can build with it.
Crowd sourcing has been growing substantially in popularity. More and more businesses and individuals are interested in gathering data from the general public for real-time data analysis and visualization. The concept is being adopted in several fields, including journalism, public health and safety, and business development. During this election year, for example, a journalist might be interested in learning what candidate his or her readers support, and the reasons why they support this candidate.
Google Forms, Fusion Tables, and Apps Script make both data collection and analysis super simple! Using Google Forms, a journalist can quickly create an HTML form for readers to submit their opinions and feedback. Fusion Tables make data analysis easy with several cool data visualization options. Apps Script acts as the glue between Google Forms and Fusion Tables, enabling the Form to send data directly to Fusion Tables.
Let’s take a look at how our journalist friend would use all these tools to collect her reader’s candidate preferences.
Google Forms provides a simple UI tool to develop forms perfect for collecting data from readers. Here’s an example of a simple form the journalist can create to get information from her readers:
Once the form has been created, it can be embedded directly into the journalist’s website or blog using the embeddable HTML code provided by Google Forms.
Google Fusion Tables makes data analysis simple with its visualization capabilities. Using Fusion Tables, the journalist can create maps and charts of the collected data with just a few clicks of the mouse!
Using some fake data as an example, here’s a pie chart that can be created using Fusion Tables to show the the results of the survey:
With Fusion Tables, it’s also easy to filter data and create a pie chart visualization showing why people like Mitt Romney:
These visualizations can also be embedded in the journalist’s website or blog, as Fusion Tables provides embeddable HTML code for all its visualizations. Now, any time someone visits the webpage with the embedded visualization, they will see the current poll result!
Finally, Apps Script acts as the glue between the Google Form and the Fusion Table, since there is currently no direct way to send Google Form submissions to a Fusion Table. During a hack event last year, I took some time to write an Apps Script script that submits the form data to Fusion Tables. The script uses the onFormSubmit Apps Script functionality as described in this blog post. The Fusion Tables code is based on the code described in this blog post.
To learn how to set up your own Google Form to collect data and save that data in a Fusion Table, please see these instructions.
Triggers in Apps Script allow you to schedule your script’s execution in response to an event, at a specific time and date, or at given time intervals. Publishing scripts as services allows you to deploy your web apps with a click of a button. The new Script service lets you perform both of these tasks programmatically. With the Script service at your disposal, you can create smooth install flows, chain your scripts’ executions, generate summaries of current triggers, and even programmatically publish the scripts as services.
You can now set up your triggers from a menu. Here is an example of how you would schedule function foo() to run one year from now via a custom menu in Google Spreadsheets:
foo()
function onOpen() { SpreadsheetApp.getActive() .addMenu("Setup", [{name: "Create Trigger", functionName: "createTrigger"}]); } function createTrigger() { var now = new Date(); ScriptApp.newTrigger("foo") .timeBased() .atDate(parseInt(now.getFullYear())+1, now.getMonth(), now.getDate()) .create(); }
By programmatically setting triggers, you are able to schedule a future script’s executions from the script that is currently running. Here is an example of a function that schedules another function to execute two hours after it completes:
// this function is run to send the initial reminder and schedule a subsequent one function sendRequest() { GmailApp.sendEmail("class@example.edu", "Assignment #1 is due in 2 and a half hours!", "Your first assignment is due in 2 and a half hours!"); var now = new Date(); var later = new Date(now.getTime() + 2 * 60 * 60 * 1000); ScriptApp.newTrigger("sendReminder").timeBased().at(later).create(); } // this function will execute two hours later, sending another reminder function sendReminder() { GmailApp.sendEmail("class@example.edu", "Assignment #1 is due in 30 minutes!", "Your first assignment is due in half an hour!"); }
With the ability to schedule triggers programmatically, it is important to keep track of the existing ones. The function below logs all the triggers that are associated with the current script. Alternatively, you could also write this summary to a spreadsheet or email it to yourself.
function logMyTriggers() { var triggers = ScriptApp.getScriptTriggers(); for(i in triggers) { Logger.log("Trigger ID: " + triggers[i].getUniqueId() + "\nTrigger handler function: " + triggers[i].getHandlerFunction() + "\nTrigger type: " + triggers[i].getEventType() + "\n----------------------"); } }
You can now let your users publish your scripts through a Google Spreadsheets custom menu as a part of the script’s set up process. Below is an illustration of this process using an example of a simple note-taking web app. Once a user clicks the menu item, the script becomes published as a service and the URL is presented back to the user via a dialog box.
function doGet(e) { var app = UiApp.createApplication(); createApp(app, false); return app; } function doPost(e) { var now = new Date(); MailApp.sendEmail(Session.getEffectiveUser(), "Notes taken at " + now, e.parameter.notes); var app = UiApp.getActiveApplication(); createApp(app, true); return app; } function createApp(app, isSubmitted) { app.setWidth(550) .setHeight(700); var vp = app.createVerticalPanel(); var title = app.createHTML("Enter your notes into the text area below." + "<br>Hit submit to email them to yourself!") .setStyleAttribute("fontSize", "25px"); var lbl = app.createLabel("Notes submitted!") .setStyleAttribute("fontSize", "17px"); var form = app.createFormPanel(); var notes = app.createTextArea() .setWidth("500px") .setHeight("500px") .setName("notes"); var sb = app.createSubmitButton("Submit"); vp.add(title) .add(notes) .add(sb); if (isSubmitted) vp.add(lbl); form.add(vp); return app.add(form); } function onInstall() { onOpen(); } function onOpen() { SpreadsheetApp.getActive() .addMenu("Setup", [{name: "Publish as a service.", functionName: "setup"}]); } function setup() { var url = ""; try { ScriptApp.getService() .enable(ScriptApp.getService().Restriction.MYSELF); url = ScriptApp.getService().getUrl(); Browser.msgBox("Your web app is now accessible at the following URL:\n" + url); } catch (e) { Browser.msgBox("Script authorization expired.\nPlease run it again."); ScriptApp.invalidateAuth(); } }
The Script service provides you with the means of creating even more powerful applications and makes the development and deployment process smooth.
For more information, please visit the Script service Reference Documentation and User Guide.
