Google Scripts code for integrating with CiviCRM (via Form Processor)

gistfile1.txt

/**
 * @OnlyCurrentDoc
 */

function onFormSubmit(event) {
  // Set the values on these four lines for your Form Processor and site.
  var url = <your REST endpoint URL, e.g. https://mysite.org/wp-json/civicrm/v3/rest>
  var api_key = <your CiviCRM API key>;
  var site_key = <your CiviCRM site key>;
  var form_processor_machine_name = 'google_forms_member_profile';

  // Don't modify anything below this line.
  var submission = {};
  var choices = [];
  var item;
  var answer;
  var responses = event.response.getItemResponses();
  for (var i = 0; i < responses.length; i++) {
    item = responses[i].getItem();
    // Convert all non-alphanumeric values in the title to underscores, lowercase as well, max 80 characters (79 because of non-unique names, see below).    
    title = item.getTitle().replace(/[\W]+/g,"_").toLowerCase().slice(0, 79);
    // If we have a non-unique name, add a final character of "A".
    // If that's not good enough, change to B, C, then through lowercases.  This should gives us 53 permutations before causing a bug.
    // If that's needed, modify this to change the last two characters, which should give us 52^2.
    var asciiCode = 65;
    var titleChanged = false;
    while (submission[title]) {
      if (titleChanged) {
        title = title.slice(0, -1) + String.fromCharCode(asciiCode);
      }
      else {
        titleChanged = true;
        title = title + String.fromCharCode(asciiCode);
      }
      asciiCode++;
    }

    answer = responses[i].getResponse();
    // Multiple-choice questions with answers "Yes" and "No" should get converted to Booleans.
    // Or a single question with answer "Yes".
    if (item.getType() == FormApp.ItemType.MULTIPLE_CHOICE) {
      choices = item.asMultipleChoiceItem().getChoices();
      if (choices.length <= 2 && choices[0].getValue() == 'Yes' && (choices.length == 1 || choices[1].getValue() == 'No')) {    
        answer = (answer == 'Yes' ? 1 : 0);
      }
    }
    submission[title] = answer;
  }
  

  var payload = { 
    "entity": "FormProcessor",
    "action": form_processor_machine_name,
    "json": JSON.stringify(submission),
    "api_key" : api_key,
    "key": site_key,
  }
  var options = {
    "method" : "post",
    "payload" : payload,
    "muteHttpExceptions" : true,
  };

  Logger.log(JSON.stringify(submission));
  // Fire the webhook.
  var response = UrlFetchApp.fetch(url,options);
}

See also https://github.com/mhawksey/CiviService-for-Google-Script, which I haven't used but seems like a more robust version of this (though not designed for Form Processor).

Jon, how could I modify your code above so that instead of submitting the option text in a multiple-choice question (call this the "label") the script submits a "value" derived from the choice, e.g., if the options are "London Borough of Richmond", "Isle of Wight", and "West Sussex" and the user chooses "Isle of Wight" then the value "2" will be sent to the form processor. I can see this isn't a whole lot different from the manipulation you do above for option text "Yes" and "No" but I'm not knowledgeable of this script language and so can't work out how to make this modification. Your script is super useful. Thank you.

Update: Jon replied on this thread and it does the job just as well. https://civicrm.stackexchange.com/questions/39461/how-can-i-integrate-google-forms-with-civicrm