Integrated Google Form and Salesforce Through Google Sheet and WebToAny


To integrate Google Forms and Salesforce.com, I choose to use POST method to send data from Sheets to Web to Any and let it do the rest of everything. It provides more security than the building-in W2L by assigning a pageToken to every web form it generates. If a post doesn’t contains pageToken, it simply returns HTTP Error 400 Bad request.

Generate a WebToAny HTML Form

I have detail explain how to configure Web to Any in Web to Any: My Missing Manual. After creating a configuration in WebToAny – Setup, click on [Create Form] to generate a html form. pageToken are different every time you create a form even with the same configuration.

<!-- NOTE: Please add the following <META> element to your page <HEAD>. -->
<!-- If necessary, please modify the charset parameter to specify the -->
<!-- character set of your HTML page. -->
<!-- ---------------------------------------------------------------------- -->

<META HTTP-EQUIV="Content-type" CONTENT="text/html; charset=UTF-8">

<!-- ---------------------------------------------------------------------- -->
<!-- NOTE: Please add the following 
<FORM> element to your page. -->
<!-- ---------------------------------------------------------------------- -->

<form action="https://my-developer-edition.ap5.force.com/services/apexrest/WTA/webtoany" method="POST">
<input type=hidden name="oid" value="00D90000000d54WEAQ">
<input type=hidden name="pageToken" value="wl0riE2c8K3coko+B9okb+dcfw19CtygsFp3/M540hCKGtZaxLrfeS0GTY7Yh2HH8mMP6jYdtoB00mA6ZOMmOgrlJhqnSNwTPL2fOO7/ztI7OyC0QwC/ZGyGZMrfgrYe1T19iBRoCiJGLT5Euis45omt9ZK751o1c99hFr/oQYC6jI/nuEFt/wVbfdXyeu+y">
<!-- ---------------------------------------------------------------------- -->

<label for="ExsZmzKy5hw5GXJ34B4J4oTusFPI8+DXLxVu0BIF/BE=">Name</label><input id="ExsZmzKy5hw5GXJ34B4J4oTusFPI8+DXLxVu0BIF/BE=" maxlength="40" name="ExsZmzKy5hw5GXJ34B4J4oTusFPI8+DXLxVu0BIF/BE=" size="20" type="text"/>

<input type="submit" name="submit">
</form>

We will need following information in Google Apps Script.

  1. oid value “00D90000000d54WEAQ”
  2. pageToken value “wl0riE2c8K3coko+B9okb+dcfw19CtygsFp3/M540hCKGtZaxLrfeS0GTY7Yh2HH8mMP6jYdtoB00mA6ZOMmOgrlJhqnSNwTPL2fOO7/ztI7OyC0QwC/ZGyGZMrfgrYe1T19iBRoCiJGLT5Euis45omt9ZK751o1c99hFr/oQYC6jI/nuEFt/wVbfdXyeu+y”
  3. field id “ExsZmzKy5hw5GXJ34B4J4oTusFPI8+DXLxVu0BIF/BE=”

WebToAny Form Security

When I disable the configuration which generate active html forms, POST returns HTTP Error 400 Bad request. Wondering if it is related to expiring pageToken. But I have no idea where it keeps them, I will check it when I have more understanding how it works.

Google Form to Sheets

Forms is just a mini web site while Sheets keeps the real data.

To save form data to Sheets, switch to [Response] tab and click on a green icon with cross which pop-up a balloon help “View responses in Sheets” to setup links.

I use the three-dot icon next to Sheets for future management like Unlink form.

Noted: Once re-link to the same Sheets, it will duplicate existing data to a new tab and use the new tab to append new data.

Apps Script to POST

In Sheets, click [Tools] → [Script editor…] to launch keeps the real data. Put following code in your editor and save.

function SendToSalesforceCapture() {
 var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
 var row = sheet.getLastRow();
 var Name = sheet.getRange(row, 2).getValue();
 var resp = UrlFetchApp
 .fetch(
 'https://my-developer-edition.ap5.force.com/services/apexrest/WTA/webtoany',
 {
 muteHttpExceptions : false,
 method: 'post',
 payload: {
 'oid' : '00D90000000d54WEAQ',
 'pageToken' : 'wl0riE2c8K3coko+B9okb+dcfw19CtygsFp3/M540hCKGtZaxLrfeS0GTY7Yh2HH8mMP6jYdtoB00mA6ZOMmOgrlJhqnSNwTPL2fOO7/ztI7OyC0QwC/ZGyGZMrfgrYe1T19iBRoCiJGLT5Euis45omt9ZK751o1c99hFr/oQYC6jI/nuEFt/wVbfdXyeu+y',
 'ExsZmzKy5hw5GXJ34B4J4oTusFPI8+DXLxVu0BIF/BE=' : Name,
 'external' : '1'
 }
 });
 Logger.log(resp.getContentText());
}

Difference Between WebToAny and W2L

Integrating Google Docs with Salesforce.com using Apps Script is worth reading but there are some differences:

  1. Web to Any use oid which is different from orgid generated using W2L in Salesforce.com The later is “00D90000000d54W” which lost the last 3 characters of oid.
  2. Web to Any needs pageToken to pass security check.
  3.  Web to Any use its own notion to replace API field names.

UTF-8 Encoding

I encounter UTS-8 issue with  fetch() default settings. I try to use the header generated by Web to Any  but fails:

<META HTTP-EQUIV="Content-type" CONTENT="text/html; charset=UTF-8">

After reading comments in fetch(url, params), I finally found the right way to set to UTF-8:

Because payload is a JavaScript object, it will be interpreted as form data. (No need to specify contentType; it will automatically default to either ‘application/x-www-form-urlencoded’ or ‘multipart/form-data’)

contentType: 'application/x-www-form-urlencoded;charset=UTF-8'

Therefore, the code should be re-written as below to handle UTF-8 correctly:

function SendToSalesforceCapture() {
 var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
 var row = sheet.getLastRow();
 var Name = sheet.getRange(row, 2).getValue();
 var resp = UrlFetchApp
 .fetch(
 'https://my-developer-edition.ap5.force.com/services/apexrest/WTA/webtoany',
 {
 muteHttpExceptions : false,
 method: 'post',
 contentType: 'application/x-www-form-urlencoded;charset=UTF-8',
 payload: {
 'oid' : '00D90000000d54WEAQ',
 'pageToken' : 'wl0riE2c8K3coko+B9okb+dcfw19CtygsFp3/M540hCKGtZaxLrfeS0GTY7Yh2HH8mMP6jYdtoB00mA6ZOMmOgrlJhqnSNwTPL2fOO7/ztI7OyC0QwC/ZGyGZMrfgrYe1T19iBRoCiJGLT5Euis45omt9ZK751o1c99hFr/oQYC6jI/nuEFt/wVbfdXyeu+y',
 'ExsZmzKy5hw5GXJ34B4J4oTusFPI8+DXLxVu0BIF/BE=' : Name,
 'external' : '1'
 }
 });
 Logger.log(resp.getContentText());
}

No RecordType as a Hidden Value

Unlike build-in W2L, you cannot pass RecordType within a form in Web to Any. Instead, you need to create a filed to get the RecordType value. And create a update field trigger on Salesfoce.com to change RecordType.

Assignment, and Comparison

To assign variable value, use =.

var row = sheet.getLastRow();

To compare values, use ==.

  if (Name == '') {
     Name = 'N/A';
  }

Debug Google Apps Script

I spend a lot of time figuring out why POST returns HTTP Error 400 Bad request. Google Script Editor asks me to enable muteHttpExceptions to read the detail error. After reading How can I see the full server response for this API error message in Google Scripts?, I learned and enabled but see no difference.

If you provides wrong url receiving POST, it returns HTTP Error 401 with “message”:”Session expired or invalid”,”errorCode”:”INVALID_ SESSION_ID”.

For SSL Error, usually it’s related to wrong url in Web to Any [Custom Settings].

Trigger to Automate POST

To automatically POST to Salesforce.com once a new row has been added in Sheets, I need to use the clock-like icon in tool bar or [Edit] → [Current project’s triggers] in Google Script Editor to add a new trigger. It needs to run SendToSalesforceCapture when an event From spreadsheet On form submit.

Reference

  1. CheckUpDown: HTTP Error 400 Bad request
  2. CheckUpDown: HTTP Error 401 Unauthorized
  3. Google Apps Script
  4. Google Apps Script: Class Sheet
  5. Google Apps Script: Class UrlFetchApp
  6. Google Forms
  7. Google Sheets
  8. Google Sheets: Add-ons: G-Connector for Salesforce
  9. Google: G Suite Developers Blog: Integrating Google Docs with Salesforce.com using Apps Script
  10. Salesforce: Web to Any
  11. StackOverflow: How can I see the full server response for this API error message in Google Scripts?
  12. w3schools.com: HTTP Methods: GET vs. POST
  13. Web to Any: My Missing Manual
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s