Google Apps Script - Save Gmail attachments to Salesforce

I recently signed up for Salesforce's force.com developer account to play around with it. It's a rather impressive platform and they recently announced full REST API support. I wanted to see how easy it was to integrate something using this API and decided to create an utility that would save some important Gmail attachments to Salesforce. Seemed like a good fit for using Google Apps Script as well, which recently added support for Gmail.

Disclaimer: There may already be better ways to do this - I never checked because it is not my intent to create an utility for general public use, I merely want to learn the usage of the APIs. However, if it seems like something you could use, feel free to copy. Of course use it at your own risk and the usual yada-yada applies.

Ok, first of all I don't want all my attachments being saved in SF and hence I'm going to filter only those that are sent to "username+Salesforce@gmail.com" - the idea being if it's something important then I would forward it to my address adding the +Salesforce tag. The +tag in Gmail address is one way to do this, you can setup some labels, filters, etc. if you like.

The steps to do this automation are:

1. Register a Remote Application in Salesforce
2. Create a new Folder in Salesforce for saving the documents
3. Create a Google Apps Script  
4. Do a dry run (unit test!) 
5. Set it up on a timer trigger

Now, lets go through it step by step:

Step 1: Register a Remote Application Salesforce
Follow the instructions here to register a remote application - https://na12.salesforce.com/help/doc/en/remoteaccess_define.htm.
Be sure to name it "Gmail2Salesforce" for consistency. I guess it doesn't matter what you call it, you just need the keys.

Step 2: Create a new Folder in Salesforce for saving the documents
Create a Folder under Documents tab called "From Gmail" - if you use something else be sure to update it in the script.

Step 3: Create a Google Apps Script
Like all things Google, it is still evolving but there is plenty of documentation at http://code.google.com/googleapps/appsscript/
a. Go to docs.google.com
b. Create new Spreadsheet
c. Go to Tools -> Script Editor
d. Copy paste the source code below and save it as "Gmail2Salesforce"
e. Open the Properties dialog from File menu and create two new Project Properties named ConsumerKey and ConsumerSecret. The values for which have to come from the screen in step 1 where you registered this app in Salesforce.

Step 4: Do a dry run (unit test!)
To do a dry run first send a test email to your Gmail address with the +tag, i.e., username+Salesforce@gmail.com - with some attachment obviously.

a. In the "Select function" drop-down in Script Editor, select "myFunction" and click the run icon.
b. Click on Authorize for the first popup - which authorizes Google Docs to execute the script on your behalf.
c. Once you do that, the next step will require authorization for Gmail. You should click "Grant Access".
d. Now this sets the script up, ready for actual execution.
e. Go ahead and click run again.
f. Another window should popup requesting authorization, Authorize it.
g. A new popup should open Salesforce page where you have to Allow this application to connect to Salesforce.
h. Once the window automatically closes and you are back to the script page, you should see that your attachment was saved to the "From Gmail" folder in Salesforce.
i. After this step, subsequent executions of the script should not require any additional authorizations, unless you changed something in the source and saved it.

Step 5: Set it up on a timer trigger
To run the script automatically, set it up on a timer. Click Triggers and select Current Project's Triggers from the menu. Add a new row and select the Events as "Time-driven", "Minutes timer" and "Every 30 minutes" (or 10 or 5, depending on your need obviously). You can add a notification if the script failed for some reason.

Save everything and that's it!

You will notice that the script also creates a new label in Gmail called Salesforce and tags the processed emails with it. It also marks them as Read.

Here is the full source code for the script, it should be really easy to follow along.
// begin

// get the consumer key and secret - this should be from the Remote Access setup in SF
var consumerKey = ScriptProperties.getProperty("ConsumerKey");
var consumerSecret = ScriptProperties.getProperty("ConsumerSecret");;

// add the OAuth service
var oAuthConfig = UrlFetchApp.addOAuthService("salesforce");
oAuthConfig.setAccessTokenUrl("https://login.salesforce.com/_nc_external/system/security/oauth/AccessTokenHandler");
oAuthConfig.setRequestTokenUrl("https://login.salesforce.com/_nc_external/system/security/oauth/RequestTokenHandler");
oAuthConfig.setAuthorizationUrl("https://login.salesforce.com/setup/secur/RemoteAccessAuthorizationPage.apexp?oauth_consumer_key="+encodeURIComponent(consumerKey));
oAuthConfig.setConsumerKey(consumerKey);
oAuthConfig.setConsumerSecret(consumerSecret);

// login to Salesforce to get a session id
var sessionLoginUrl = "https://login.salesforce.com/services/OAuth/u/21.0";
var options = { method : "POST", oAuthServiceName : "salesforce", oAuthUseToken : "always" };
var result = UrlFetchApp.fetch(sessionLoginUrl, options);
var txt = result.getContentText();
var accessToken = txt.match(/<sessionId>([^<]+)/)[1];
var serverUrl = txt.match(/<serverUrl>([^<]+)/)[1];
var instanceUrl = serverUrl.match(/^https?:\/\/[^\/]+/)[0];

function myFunction() {
    // setup the Gmail label name, creating it if it doesn't exist
    var FOLDER_FROM = "Salesforce";
    var label = GmailApp.getUserLabelByName(FOLDER_FROM);
    if (!label) {
        GmailApp.createLabel(FOLDER_FROM);
        label = GmailApp.getUserLabelByName(FOLDER_FROM);
    }

    // get the Salesforce folder name and id
    var FOLDER_TO = "From Gmail";
    var FOLDER_TO_ID = getFolderId(FOLDER_TO);

    // Fetch unread emails sent to +Salesforce
    var threads = GmailApp.search("is:unread to:+" + FOLDER_FROM);
    if (threads) {
        for (var i in threads) {
            var messages = threads[i].getMessages();
            for (var j in messages) {
                if (messages[j].isUnread()) {
                    var attachments = messages[j].getAttachments();
                    for (var k in attachments) {
                        saveAttachment(FOLDER_TO_ID, attachments[k]);
                    }
                }
            }
        }
        label.addToThreads(threads);
        GmailApp.markThreadsRead(threads);
    }
}

function saveAttachment(folderId, blob) {
    var docName = blob.getName();
    var contentType = blob.getContentType();
    var encodedDoc = Utilities.base64Encode(blob.getBytes());

    var docUrl = instanceUrl + "/services/data/v20.0/sobjects/Document/";
    var queryStr = { "Name" : docName, "FolderId" : folderId, "ContentType" : contentType, "Body" : encodedDoc };

    // Salesforce REST API expects the methods to submit JSON requests - set contentType and payload accordingly
    response = UrlFetchApp.fetch(docUrl, {
                            method : "POST",
                            contentType : "application/json",
                            headers : { "Authorization" : "OAuth "+accessToken },
                            payload : Utilities.jsonStringify(queryStr) }
                      );
}

function getFolderId(folderName) {
    var soql = "SELECT Id,Name FROM Folder WHERE Name='" + folderName + "'";
    var queryUrl = instanceUrl + "/services/data/v21.0/query?q="+encodeURIComponent(soql);
    var response = UrlFetchApp.fetch(queryUrl, { method : "GET", headers : { "Authorization" : "OAuth "+accessToken } });
    var queryResult = Utilities.jsonParse(response.getContentText());
    var folderId = (queryResult && queryResult.records[0]) ? queryResult.records[0].Id : null;

    return folderId;
}

// end

The timers are setup to execute in Google's cloud infrastructure, so you don't have to be logged in to either Gmail or Google Docs even though you create the Apps Script from Spreadsheet. Neat!

The REST API developer documentation for Salesforce is available at: http://developer.force.com/REST

Also, I should mention that I built upon the code from the blog posts Gmail Snooze with Apps Script and Connecting to Salesforce from Google Apps Script via OAuth and RESTful API.