The Apps Script team held a hackathon in Washington DC on March 7. Over 80 developers attended and we had some great demos at the end of the evening. One of the demos was from Rusty Mellinger, who explains his script in this blog post. If you missed the DC hackathon, sign up for our next one in Chicago on April 19. -Jan Kleinert
I was lucky enough to attend Google’s Apps Script Hackathon at their office in DC, recently, and got a chance to play with Apps Script. After a quick walk-through tutorial, Jan gave us a couple of hours to hack around with it.
Scripts in Apps Script are written in JavaScript and stored, edited, and run on Google's servers, interfacing with a big list of included services. You can call the scripts from spreadsheets, Google Sites, or from hits to a generated URL.
My roommates and I keep a spreadsheet on Google Docs to track who owes what, but since we’re a house full of software engineers, it’s gotten pretty complicated. Each row records the details of a single transaction: who paid, the total, and what percentages of the payment are on behalf of which roommates. All these interpersonal debts are added up into the (J5:M8) matrix, cancelled out across the diagonal into (P5:S8) to get a single debt for each roommate pairing, and then those are totalled into the final "Shake Out", (F4:F7), which says whether you owe or are owed. Maybe Apps Script could make my life simpler here?
First, I’m currently owed a fair amount of money, so I set up automated reminder emails to the roommates who are behind:
// Send emails to everybody with their current status. function emailDebtors() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var results = ss.getRange( "G4:G7" ).getValues(); var emails = ss.getRange( "O3:R3" ).getValues(); var numUsers = 4; for(var i = 0; i != numUsers; i++) { var val = Math.round(results[i][0]); if (val > 0) { // This guy owes money in the shake-out. MailApp.sendEmail( emails[0][i], "You're a deadbeat!", "You owe $" + val + ". Please pay it!"); } } }
This just pulls the current totals from the (G4:G7) "Shake Out", as well as their respective email addresses from (O3:R3). When this function is called, if any of them owe more than $0, they get a friendly reminder!
I could set that up to trigger daily or weekly, but it only really needs to happen when somebody needs to collect what they’re owed, so I’ve added it as an option to the sheet’s menu on start-up.
function onOpen() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var menuEntries = [ { name: "Email debtors", functionName: "emailDebtors"}]; ss.addMenu( "SkyCastle", menuEntries ); }
Easy! Now when somebody wants to collect, they just click the “SkyCastle -> Email debtors” option and the appropriate reminder emails are sent out, from their own Gmail address.
I still had a couple of hours, and wanted to play with the UI and Google Charts services, so I decided to chart the “Shake Out” values over the history of the spreadsheet. The existing cells are hard-coded to operate on the total sums from the full sheet, so I had to re-implement the math to track it line-by-line. (This isn’t all bad, because I can use it to double check the existing arithmetic, which was sorely needed.)
The basic sketch is as follows:
var data = Charts.newDataTable() .addColumn(Charts.ColumnType.NUMBER, "Row"); for (var i = 0; i != 4; i++) { data.addColumn(Charts.ColumnType.NUMBER, names[i]); } for (var i = 0; i != NUMROWS; i++) { var row = Array(5); // … // Process the current line here, and compute the shake-out. // … data.addRow(row); } data.build();
I’ve omitted the actual calculation, because it’s just a bunch of hacks specific to our spreadsheet formulas. Each row contains the row number, and the accumulated shake-out thus far, and gets added to the `data` table. I break out of the loop once I go off the end of my data and start hitting `NaNs`.
To create the line chart and add it to a new UI window:
var chart = Charts.newLineChart() .setDataTable(data) .setDimensions(700, 400) .setTitle("Debt History") .build(); var uiApp = UiApp.createApplication() .setWidth(700) .setHeight(400) .setTitle("Payment History"); uiApp.add(chart); ss.show(uiApp); return uiApp;
After adding this function as another option in our custom `SkyCastle` menu and clicking it, we see a nice graph. (I’m almost always on the bottom, but that’s because I make the actual rent and utility payments.) The final entries are equal to the original "Shake Out" cells, so our old arithmetic seems correct, too.
The built-in debugger isn’t bad; use the `Select function` dropdown and click the bug icon. I also used Logger.log() liberally while trying to get things working right. (Go to `View -> Logs` in the Script Editor to view that output.)
Apps Script seems to work well, overall, and hooks into a nice and expanding array of Google products and data sources. The GWT-backed UI service is a clever idea, though I barely had a chance to touch it.
Thanks again to Jan and Google for hosting this Hackathon; I can’t wait for the next one!
PATCH requests allow you to perform partial updates on many of our REST APIs and in most cases can save bandwidth.
PATCH
If you have ever tried to do a PATCH request on an App Engine application, you probably realized that it is not possible and that the list of HTTP Methods allowed is whitelisted to the following methods only: GET, POST, HEAD, PUT and DELETE. Trying to perform a PATCH request raises the following Exception:
GET
POST
HEAD
PUT
DELETE
java.net.ProtocolException: PATCH is not one of the supported http methods: [GET, POST, HEAD, PUT, DELETE]
There is a workaround to this. Most of our APIs support the X-HTTP-Method-Override header. This header can be used in a POST request to “fake” other HTTP methods. Simply set the value of the X-HTTP-Method-Override header to the HTTP method you would like to actually perform.
X-HTTP-Method-Override
For example, to make a PATCH request to the Google Tasks API to update only the Notes field of a particular task you could use the following HTTP request:
Notes
POST /tasks/v1/lists/@default/tasks/TASK_ID HTTP/1.1 Host: www.googleapis.com X-HTTP-Method-Override: PATCH Authorization: Bearer Content-Type: application/json Content-Length: 31 {“Notes” : “Patch is working!”}
Which would be equivalent to this HTTP Request, which is not supported on App Engine:
PATCH /tasks/v1/lists/@default/tasks/TASK_ID HTTP/1.1 Host: www.googleapis.com Authorization: Bearer Content-Type: application/json Content-Length: 31 {“Notes” : “Patch is working!”}
For instance, in an App Engine Java environment you could construct and execute this request this way:
URL url = new URL("https://www.googleapis.com/tasks/v1/" + "lists/@default/tasks/" + TASK_ID); HTTPRequest request = new HTTPRequest(url, HTTPMethod.POST); request.addHeader(new HTTPHeader("X-HTTP-Method-Override", "PATCH")); request.addHeader(new HTTPHeader("Authorization", "Bearer " + ACCESS_TOKEN)); request.setPayload("{\"Notes\" : \"Patch is working!\"}".getBytes()); URLFetchService fetchService = URLFetchServiceFactory.getURLFetchService(); HTTPResponse response = fetchService.fetch(request);
This trick can also be used if your application server is behind a firewall, behind a proxy server or in any other environment where HTTP methods other than POST might not be allowed. In that case you could use the X-HTTP-Method-Override header the same way to workaround these limitations.
You may also use our Google APIs Client library for Java or our Google APIs Client library for Python, both of which have support for PATCH requests and use the X-HTTP-Method-Override header when appropriate.
Editor's Note: Ferris Argyle is going to present Salesforce Workflow Automation with Google Spreadsheet and Apps Script at Cloudforce. Do not miss Ferris's talk - Saurabh Gupta
As part of Google's Real Estate and Workplace Services (REWS) Green Team, the Healthy Materials program is charged with ensuring Google has the healthiest workplaces possible. We collect and review information for thousands of building materials to make sure that our offices are free of formaldehyde, heavy metals, PBDEs and other toxins that threaten human health and reduce our productivity.
My team, as you might imagine, has a great deal of data to collect and manage. We recently implemented Salesforce.com to manage that data, as it can record attributes of an object in a dynamic way, is good at tracking correspondence activity and allows for robust reports on the data, among many other functions.
We needed Saleforce.com to integrate with our processes in Google Apps. We wanted to continue collecting data using a Google Docs form but needed it integrated with Salesforce.com because we:
And this is where Google Apps Script came to our rescue. We found that we could use Google Apps Script to create a new Case or Lead in Salesforce.com when a form is submitted through our Google Docs form. This allowed us to continue using our existing form and get our data directly and automatically into Salesforce.com.
Salesforce.com has two built-in options for capturing data online - Cases and Leads. Google Docs Forms can capture data for both of them. Set up your Case or Lead object with the desired fields in Salesforce.com. The next step is to generate the HTML for a form. You will use the IDs in the Salesforce.com-generated HTML when writing your Google Apps script.
1. Login to Salesforce.com and go to Your Name > Setup > Customize > Leads or Self-Service (for Cases) > Web-to-Lead or Web-to-Case
2. Make sure Web-to-Lead/Web-to-Case is enabled. Click on Edit (Leads) or Modify (Cases) and enable if it is not.
3. Click on the 'Create Web to Lead Form' button (for Leads) or the 'Generate the HTML' link (for Cases)
4. Select the fields you want to capture and click 'Generate'. Save the HTML in a text file. You can leave 'Return URL' blank
Create your form and spreadsheet (or open up the one you already have and want to keep using). This is very easy to do. Go to your Docs and click on 'Create' to open a new form. Use the form editor to add the desired fields to your form- they'll show up as column headings in the corresponding spreadsheet. When someone fills out your form, their answers will show up in the right columns under those headings.
The script is set up to take the data in specified cells from the form/spreadsheet and send it into designated fields in your Salesforce.com instance (identified by the org id in the HTML generated above). For example, the form submitter's email is recorded through the form in one cell, and sent into the email field in either the Lead or Case object in Salesforce.com.
1. Create a new script (Tools > Script Manager > New).
2. Write the script below using the pertinent information from your Salesforce.com-generated code (shown further down).
function SendtoSalesforce() { var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); var row = sheet.getLastRow(); var firstname = sheet.getRange(row, 2).getValue(); var lastname = sheet.getRange(row, 3).getValue(); var email = sheet.getRange(row, 4).getValue(); var company = sheet.getRange(row, 5).getValue(); var custom = sheet.getRange(row, 6).getValue(); var resp = UrlFetchApp .fetch( 'https://www.salesforce.com/servlet/servlet.WebToLead?encoding=UTF-8', { method: 'post', payload: { 'orgid' : '00XXXXXXXX', 'first_name' : firstname, 'last_name' : lastname, 'email' : email, 'company' : company, '00YYYYYYYY' : custom, 'external' : '1' } }); Logger.log(resp.getContentText()); }
Define your variables by directing the script to the correct cell (row, column number). Then in the payload section, match the field id from your Salesforce.com HTML (red) to the variable you defined (blue). For example, the email address of the submitter is defined as variable 'email', can be found in the 4th column of the last row submitted, and the id for that field in Salesforce.com is 'email'.
Note that any custom fields you've created will have an alpha-numeric id.
3. Save your script and do a test run.
To send your data automatically into Salesforce.com, you need to set a trigger that will run the script every time a form is submitted. To do this, go to your script and click Resources>Current script's triggers.
1. Create a Trigger for your function so that it runs when a form is submitted.
2. Post the link to your form on your website, send it in an email, link to it on G+, etc. Get it out there!
That's it! Now when someone submits a form, the information will come into your spreadsheet, and then immediately be sent into Salesforce.com. You can adjust your Salesforce.com settings to create tasks when the information comes in, send out an auto-response to the person filling out the form and set up rules for who is assigned as owner to the record. You'll also have the information backed up in your spreadsheet.
This has been a great solution for our team, and we hope others find it useful as well!