Free Tips

Editors Note: Ferris Argyle is going to present Salesforce Workflow Automation with Google Spreadsheet and Apps Script at Cloudforce. Do not miss Ferriss talk - Saurabh Gupta

As part of Googles 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- theyll 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 submitters 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 youve 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 scripts 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!

Thats 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. Youll 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 Googles Green Team in Mountain View, which makes sure that Googles offices are the healthiest, most sustainable workplaces around. Prior to Google, she had a past life as a wildlife researcher.

The OAuth Playground is a great tool to learn how the OAuth flow works. But at the same time it can be used to generate a "long-lived" access token that can be stored, and used later by applications to access data through calls to APIs. These tokens can be used to make command line tools or to run batch jobs.

In this example, I will be using this token and making calls to the Google Provisioning API using the Python client library for Google Data APIs. But the following method can be used for any of the Google Data APIs. This method requires the token is pushed on the token_store, which is list of all the tokens that get generated in the process of using Python client libraries. In general, the library takes care of it. But in cases where it’s easier to request a token out of band, it can be a useful technique.

Step 1: Generate an Access token using the OAuth Playground.
Go through the following process on the OAuth Playground interface:

• Choose scope(s) of every API you want to use in your application (https://apps-apis.google.com/a/feeds/user/ for the Provisioning API) . Here you can also add scopes which are not visible in the list.
• Choose an encryption method that is the signature method to encode your consumer credentials. (“HMAC-SHA1” is the most common)
• Enter your consumer_key and consumer_secret in the respective text fields. The consumer_key identifies your domain and is unique to each domain.

After entering all the required details you need to press these buttons on the OAuth Playground in sequence:

• Request token: This will call Google’s OAuth server to issue you a request token.
• Authorize: This will then redirect you to the authorization URL where you can authorize or deny access. At this point if you deny the access you will not be able to generate the Access token. Accepting this will convert the Request token generated in the last step into an Authorized Request token.
• Access token: Finally, this step will exchange the authorized Request token for an Access token.

After the last step the text field captioned auth_token in the OAuth Playground has the required Access token and that captioned access_token_secret has the corresponding token secret to be used later.

Step 2: Use the above token when making calls to the API using a Python Client Library.

Here is an example in Python which uses the OAuth access token that was generated from OAuth Playground to retrieve data for a user.

CONSUMER_KEY = “CONSUMER_KEY”
CONSUMER_SECRET = “CONSUMER_SECRET”
SIG_METHOD = gdata.auth.OAuthSignatureMethod.HMAC_SHA1
TOKEN = “GENERATED_TOKEN_FROM_PLAYGROUND”
TOKEN_SECRET = “GENERATED_TOKEN_SECRET_FROM_PLAYGROUND”

DOMAIN = “your_domain”

client = gdata.apps.service.AppsService(source=”app”, domain=DOMAIN)
client.SetOAuthInputParameters(SIG_METHOD, CONSUMER_KEY, consumer_secret=CONSUMER_SECRET)
temp_token = gdata.auth.OAuthToken(key=TOKEN, secret=TOKEN_SECRET);
temp_token.oauth_input_params = client.GetOAuthInputParameters()
client.SetOAuthToken(temp_token)
#Make the API calls
user_info = client.RetrieveUser(“username”)

It is important to explicitly set the input parameters as shown above. Whenever you call SetOuthToken it creates a new token and pushes it into the token_store. That becomes the current token. Even if you call SetOauthToken and SetOAuthInputParameters back to back, it won’t set the input params for the token you set.

Other Practices:

You can use the long-lived token to make command line requests, for example using cURL. It can be useful when you need to counter-check bugs in the client library and to test new features or try to reproduce issues. In most cases, developers should use the client libraries as they are designed, as in this example.

Gunjan Sharma  Profile | Twitter Gunjan is a Developer Programs Engineer working on Google Apps APIs. Before joining Google, he completed his degree in Computer Science & Engineering from Indian Institute of Technology, Roorkee.

In this tutorial we will learn how to create a Frame Animation using Java code instead of an XML file.

Well be using the same 3 drawable resources (images) as frames in this tutorial as we did last time. But instead of creating the sequence in an XML file, well do that in the MainActivity.java class of our project.

The acitivty_main.xml layout file remains like this:


In MainActivity class, we first declare an AnimationDrawable and ImageView instances:


We then add 2 clicks listeners to the two buttons on the screen. When the Start button is pressed, create 3 BitmapDrawable instances that refer to the 3 drawable resources we have. Then set the value of the animation object to a new AnimationDrawable() instance, set its oneShot property to false using setOneShot() method. Add each frame to it using addFrame() method, which has 2 parameters - reference to the drawable used in this frame and the duration of the frame.

Then we set the content of the image to this animation using setImageDrawable() method and start the animation after making it visible using the setVisible() method:


When the Stop button is pressed, stop the animation:


Full code:


Thanks for reading!