Send a Google Apps Script Form to Salesforce as a Lead, using Web-to-lead POST webthod

Question

I'm trying to build a form using Google Apps script that send form data (text+file Attachment) to a Google Spreadsheet and then this Spreadsheet has a Google Apps Script that send form data to Salesforce as a lead, via a web-to-lead POST method.

Part 1) HTML Form to Google Spreadsheet (this works)

A Google Apps script web app (Code.gs + Form.html + Thanks.html) has an HTML form with text fields and a file upload mechanism. On submit this form adds a new row to the Google Spreadsheet with form data and the URL of the uploaded file.

As per this Tutorial: Form and file upload with htmlService and app script not working​

Part 2) Google Spreadsheet to Salesforce using Web-to-Lead POST method (Not working)

In the above mentioned Google Spreadsheet, the following code.gs is adding based on a Google Developers' post (http://googleappsdeveloper.blogspot.sg/2012/03/integrating-google-docs-with.html):

It essentially takes the row data from the Spreadsheet and pushes it to the Web to Lead service which creates a new lead in Salesforce:

Code.gs (of the spreadsheet)

    function SendtoSalesforce() {
      var sheet = SpreadsheetApp.openById("--Spreadsheet ID--").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 description = sheet.getRange(row, 6).getValue();
      var resp = UrlFetchApp
          .fetch(
              'https://www.salesforce.com/servlet/servlet.WebToLead?encoding=UTF-8',
              {
                method: 'post',
                payload: {
                  'oid' : '--SFDC Org ID--',
                  'first_name' : firstname,
                  'last_name' : lastname,
                  'email' : email,
                  'company' : company,
                  'description' : description,
                  'external' : '1'
                }
              });
      Logger.log(resp.getContentText());
    }

I then added a trigger for "SendtoSalesforce" that fires on a "form submit" event.

Issue The Leads get captured inside the Google Spreadsheet but don't get passed to Salesforce. It's only after I manually run the above Code.gs script manually that the data is passed through to Salesforce successfully.

I would appreciate some help in achieving this transfer from Google Scripts to Salesforce automatically after each form submit event.

I tried adding a Time based trigger in combination with On edit, on change, on open trigger but they behave erratically and resubmit the entire sheet's rows every minute/hour leading to duplicates in Salesforce.

I would appreciate some help in getting the code to run each time automatically, avoiding duplications in SFDC.

Answer 1

you are misunderstanding the documentation in several areas:

1. onFormSubmit triggers only for entries submitted throught google forms, not a random post from a webapp.

2. the other triggers (onEdit onChange) happen only while a user inputs data through the web sheets page, not by api calls.

your solution is to modify your webapp so that it also calls your code that processes the new row added.

alternatively (and more robust in general) is to create a recurring time trigger (say every 10 minutes) that scans the sheet starting from the last scanned row and processes the new rows. use script properties to save the last processed row.