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.
- oid value “00D90000000d54WEAQ”
- pageToken value “wl0riE2c8K3coko+B9okb+dcfw19CtygsFp3/M540hCKGtZaxLrfeS0GTY7Yh2HH8mMP6jYdtoB00mA6ZOMmOgrlJhqnSNwTPL2fOO7/ztI7OyC0QwC/ZGyGZMrfgrYe1T19iBRoCiJGLT5Euis45omt9ZK751o1c99hFr/oQYC6jI/nuEFt/wVbfdXyeu+y”
- 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:
- 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.
- Web to Any needs pageToken to pass security check.
- 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
- CheckUpDown: HTTP Error 400 Bad request
- CheckUpDown: HTTP Error 401 Unauthorized
- Google Apps Script
- Google Apps Script: Class Sheet
- Google Apps Script: Class UrlFetchApp
- Google Forms
- Google Sheets
- Google Sheets: Add-ons: G-Connector for Salesforce
- Google: G Suite Developers Blog: Integrating Google Docs with Salesforce.com using Apps Script
- Salesforce: Web to Any
- StackOverflow: How can I see the full server response for this API error message in Google Scripts?
- w3schools.com: HTTP Methods: GET vs. POST
- Web to Any: My Missing Manual
Amigo's Technical Notes 