0

I have a google form setup that emails me upon a manual submission when somebody fills it out (new lead) and transfers the information to a Google spreadsheet. Easy enough to figure that out.

However, now I'm trying to figure out how to send the same information information contained within a url string and automatically POST that information to the form. Or find a company who offers that ability, via an api or other means. So far I've tested out jotform and a few others. The information passed along fine, but it doesn't auto populate the fields. I assume it's because it doesn't know that x=y due to the fields being named differently. I've found a ton of documentation about pre-populating the forms, but not much about filling out a form every time a new POST url is generated.

URL looks like the following

VARhttps://script.google.com/macros/s/XXXXXXXXXXXXXXXX/exec?/--A-- 
first_name--B--/--A--last_name--B--/--A--address1--B--/--A--city--B--/--A-- 
state--B--/--A--postal_code--B--/--A--phone_number--B--/--A--date_of_birth-- 
B--/--A--email--B--

Information passed is as follows

https://website 
here.com/Pamela/Urne/123+Test+Street/Henderson/TX/75652/281XXXXXX/1974-01- 
01/test0101cw@test.com

The script I'm testing out

    // original from: http://mashe.hawksey.info/2014/07/google-sheets-as-a-database-insert-with-apps-script-using-postget-methods-with-ajax-example/
// original gist: https://gist.github.com/willpatera/ee41ae374d3c9839c2d6 

function doGet(e){
  return handleResponse(e);
}

//  Enter sheet name where data is to be written below
        var SHEET_NAME = "Sheet1";

var SCRIPT_PROP = PropertiesService.getScriptProperties(); // new property service

function handleResponse(e) {
  // shortly after my original solution Google announced the LockService[1]
  // this prevents concurrent access overwritting data
  // [1] http://googleappsdeveloper.blogspot.co.uk/2011/10/concurrency-and-google-apps-script.html
  // we want a public lock, one that locks for all invocations
  var lock = LockService.getPublicLock();
  lock.waitLock(30000);  // wait 30 seconds before conceding defeat.

  try {
    // next set where we write the data - you could write to multiple/alternate destinations
    var doc = SpreadsheetApp.openById(SCRIPT_PROP.getProperty("key"));
    var sheet = doc.getSheetByName(SHEET_NAME);

    // we'll assume header is in row 1 but you can override with header_row in GET/POST data
    var headRow = e.parameter.header_row || 1;
    var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
    var nextRow = sheet.getLastRow()+1; // get next row
    var row = []; 
    // loop through the header columns
    for (i in headers){
      if (headers[i] == "Timestamp"){ // special case if you include a 'Timestamp' column
        row.push(new Date());
      } else { // else use header name to get data
        row.push(e.parameter[headers[i]]);
      }
    }
    // more efficient to set values as [][] array than individually
    sheet.getRange(nextRow, 1, 1, row.length).setValues([row]);
    // return json success results
    return ContentService
          .createTextOutput(JSON.stringify({"result":"success", "row": nextRow}))
          .setMimeType(ContentService.MimeType.JSON);
  } catch(e){
    // if error return this
    return ContentService
          .createTextOutput(JSON.stringify({"result":"error", "error": e}))
          .setMimeType(ContentService.MimeType.JSON);
  } finally { //release lock
    lock.releaseLock();
  }
}

function setup() {
    var doc = SpreadsheetApp.getActiveSpreadsheet();
    SCRIPT_PROP.setProperty("key", doc.getId());
}

I get a success message after accessing the url, but all information listed in the spreadsheet is "Undefined."

That's as far as I got so far. If somebody knows an easier solution or can point me in the right direction I'd appreciate it.

J.R.
  • 59
  • 1
  • 9
  • So use ...``exec?x=Pamela&z=Urne``... and so on – TheMaster Oct 28 '18 at 02:05
  • I'd love to do that, but unfortunately it's hard coded into the software with those variables. I'm just trying to find the easiest way to extract that information and link it to the respective field using php/html/javascript/ and auto populate a lead form. – J.R. Oct 28 '18 at 23:38
  • You need to try multiple versions of [this](https://stackoverflow.com/questions/1034621) for a few days as this is inside a iframe. If you can get the url client side, then pass it server side and parse the url. – TheMaster Oct 29 '18 at 10:28

0 Answers0