Integrating Google Docs with Salesforce.com using Apps Script

Monday, March 12, 2012 | 3:01 PM

Labels:

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.

A Case for using Google Docs and Salesforce.com

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:

  1. Liked the way the form looked and functioned
  2. Wanted to retain continuity for our users, including keeping the same URL
  3. Wanted a backup of submissions

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.

Google Docs + Apps Script + Salesforce.com = Integrated Goodness!

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.


A) Getting the HTML in Salesforce.com:

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


B) Setting up Google Apps Form/Spreadsheet:

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.


C) Writing the Google Apps Script:

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.


D) Wiring Script to a Form Submission.

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!


Beth Sturgeon  

Beth Sturgeon is a member of Google's Green Team in Mountain View, which makes sure that Google's offices are the healthiest, most sustainable workplaces around. Prior to Google, she had a past life as a wildlife researcher.

19 comments:

Athar said...

I've been using Google docs for quite some time now, Also found that there are few tools which are freely available in the market, one such tool could be CollateBox http://www.collatebox.com/ , it says easier than excel, better than Google docs.
Gotta wait and watch for this one.

Harry said...

Why need salesforce or collatebox because google form can be customizable with html and can manage account in apps. i would rather use pure google apps

Jack B. Erhart said...

I would like to know if Google Docs would consider developing the ability to get an email notice when people submit information on a form that has been developed in Google Docs.

Ryan Boyd said...

@Jack

Go to "Tools" and then "Notifications".

Saurabh Gupta said...

You can also use onFormSubmit event and execute a script whenever somebody submits an event.

Jason said...

While testing, I came across this issue wherein I have a checkbox type field in my form which I check. But when I see that submission in Salesforce, the checkbox is not checked. Any idea why? Thanks!!

Jason said...

Hi,

We have a form which collects data and pushes it to a spreadsheet and then to salesforce, as per this tutorial.

But we require only certain entries to be pushed to SF from the spreadsheet, whereas the spreadsheet should collect all the entries.

How do we configure this to work? For example, if we want only the higher values from a dropdown to be deemed as valuable leads to be pushed to salesforce, how do we get that done? please help.

GreenTeamBeth said...

@Jason

Good question!

When you have a Google Docs form with checkboxes, the corresponding spreadsheet shows the text around that checkbox instead of a True or False value. For example, if you have a checkbox question with the option 'Apples', the spreadsheet shows the responses as either blank if not checked or Apples if checked- as opposed to True or False.

Because of this, your fields in Leads or Cases in Salesforce should be text fields to capture the text around your checkboxes.

You can still use a checkbox in Salesforce, though! Send the Google Docs submission into Salesforce as a text field and add a checkbox field on the Lead/Case. Once you've done that, use a Workflow Rule (Setup>Create>Workflow & Approvals>Workflow Rules) to update your checkbox based on the text that comes in from your Google Doc submission. You can set up the rule to look at your text field on either Case/Lead, and then do a Workflow Action to Field Update the checkbox field.

I hope that helps!

Jason said...

Hi Beth,

Thanks so much for your reply on the checkbox issue!!

It would be great if you could also help me with my other issue related to certain entries to be pushed to SF from the spreadsheet.

Thanks again!

Todd Thiel said...

I'm currently trying to pass a date value to a SFDC Web-to-Case field.

I've followed the directions above, and everything has worked with the exception of a date range.

I've attempted the following:

var Start_Date = sheet.getRange(row, 6).getValue();
var SFDCSafeDate = Start_Date.getFullYear()+'-'+Start_Date.getMonth()+'-'+Start_Date.getDate();

This has not worked.

Is there a means of norming the date range to fit the 'YYYY-MM-DD' format required by Salesforce?

ramazanayyildiz said...

Hi,

resp.getContentText() returns null. How can i understand if it saved or not

John Sellers said...

Curious if this no longer functions. I can't seem to get it to work though I am certain I followed the steps in detail. For some reason my trigger doesn't work and send the data into Salesforce.

Brandon Phelps said...

Very cool. Do you know of anyone doing the opposite? Take a Salesforce.com record and push it into a Google Doc or Google Sheet? It would be immensely helpful.

wylieatwork said...

This is EXACTLY what I was looking for - and simple enough for me to do - so yay! Thanks

Terry said...

I was thinking about using Apps Scripts to produce an Invoice out of Force.com. It would be a Document (not a spreadsheet) with some nice formatting, then sent via GMail with a BCC to saleforce to attach it to the contact or account. I was planning on triggering it from Force.com. Is this a good fit? If I am used to Conga, would I be please with the speed/results/process?

Todd Thiel said...

"Is there a means of norming the date range to fit the 'YYYY-MM-DD' format required by Salesforce?"

This is an old thread, but my phone started blowing up with the new responses, so I figured I'd add some input.

If you're using Web-to-Case or Web-to-Lead, don't try to format the date. I spent a good part of 2 days trying to figure out how to get SFDC to accept date format.

It has to be in MM/DD/YYYY, and nothing else. Same as it would be for the web-to-lead formatting. Everything else blows up.

The problem with the date field is that unless you have some form of field error-handling. Improper date formats or typo's will fail.

I currently have this working on a Google Form, firing a web-to-case, and web-to-lead to automate my company's 'New Hire' tickets, and it's working smoothly.

Rachel Marie McKay said...

If you are interested in even more integration points between Google Apps and Salesforce, check out this Googler Developer creation, The Scoop Composer (http://www.cloudgizmos.com/salesforce-gmail-integration). It gives you contextual Salesforce data at the bottom of every email and allows you to instantly create new records, log emails, and seamlessly traverse between the two systems. Various Google departments have been using it internally, but it is now publicly available on the Salesforce AppExchange and the Chrome Webstore. I hope this was of interest. Sincerely, Rachel (from CloudGizmos).

Muhammad Bilal said...

Hi
I need to send data to google spreadsheet from salesforce. Can any one guide me.? Thanks in advance.

Integrations said...

Could you please tell me how to post the link of the form and send it in email, link to it on G+, Can you please help on the last Step 2.