Sending Data to n8n from Google Forms

mcnaveen · April 15, 2021, 3:22am

You can send data from Google Sheets to n8n via Simple Google Apps Script.

This script is originally authored by @mskian for Google Forms to Pushbullet. I have made little changes to it.

Paste the Below Script into Google Apps Script and Set the Trigger to FormSubmit

Please change the values according to your requirements.

It currently support two values from Google Forms. (You can add more)

function onFormSubmit(e) {
  var url = "WEBHOOKURL"; //n8n WebHook URL
  var Field1 = ' ';
  var Field2 = ' ';

  var form = FormApp.openById("FORMID"); // Copy the Form ID from the URL
  var formResponses = form.getResponses();

  var formResponse = formResponses[formResponses.length - 1];
  var itemResponses = formResponse.getItemResponses();
  var response1 = itemResponses[0]; // Field Starts from 0 (For Example if Name Text box in Google Form has the ID 0)
  var response2 = itemResponses[1];

  response1 = Field1 + response1.getResponse();
  response2 = Field2 + response2.getResponse();

  var data = {
    "response1": response1,
    "response2": response2
  };
  var options = {
    method: "post",
    headers: {
      "Content-Type": "application/json"
    },
    payload: JSON.stringify(data),
  };
  var response = UrlFetchApp.fetch(url, options);
}

If you face any issues. Please comment below.

mrchrisadams · January 11, 2022, 7:56pm

Thanks so much for creating this.

I used it as the basis for some work today, and extended it to build a json object from the form fields. This way if you add new fields in the form, they appear in the n8n webhook payload.

function onFormSubmit(e) {
  const url = "N8N_WEBHOOK_URL"; //n8n WebHook URL

  // get our form details
  const form = FormApp.openById("FORM_ID"); // Copy the Form ID from the URL
  const formResponses = form.getResponses();  
  const fields = form.getItems()
  const fieldNames = fields.map( (item) => item.getTitle() )
  
  // list of field name like ["title", "message", "name", etc]
  console.log( {fieldNames} ) 

  // now, fetch the latest submission
  const formResponse = formResponses[formResponses.length - 1];
  const itemResponses = formResponse.getItemResponses();
  const formSubmission = itemResponses.map( (field) => field.getResponse() )

  // build our payload to send to n8n
  let index = 0
  let payload = {}

  for (const field of fieldNames) {
    payload[field] =  formSubmission[index]
    index++
  }

  console.log({formSubmission})
  console.log({payload})

  // finally send along our request as a POST request to the n8n endpoint
  const options = {
    method: "post",
    headers: {
      "Content-Type": "application/json"
    },
    payload: JSON.stringify(payload),
  };
  const response = UrlFetchApp.fetch(url, options);
}

djangelic · January 11, 2022, 8:03pm

If you don’t mind granting a 3rd party plugin access to your form, you can easily set up a webhook from your google form using this extension. It’s limited to 20 submissions per day, but my forms typically don’t need more than that for others that want to avoid a coding solution: https://workspace.google.com/marketplace/app/email_notifications_for_google_forms/984866591130

mcnaveen · January 12, 2022, 7:25pm

That’s awesome

Mr_Matt · January 26, 2022, 12:18pm

I just wanted to post an update. You can do this direct from Google Forms with Google Apps Script rather than doing it from Google Sheets. See this blog by Eyal Gershon for more details. I used it as it was a little cleaner code and added a couple of extra lines in to return also the timestamp and the user’s email address in the response.

var POST_URL = "WEBHOOKURL"; //n8n WebHook URL
function onSubmit(e) {
    var form = FormApp.getActiveForm();
    var allResponses = form.getResponses();
    var latestResponse = allResponses[allResponses.length - 1];
    var response = latestResponse.getItemResponses();
    var payload = {};
    for (var i = 0; i < response.length; i++) {
        var question = response[i].getItem().getTitle();
        var answer = response[i].getResponse();
        payload[question] = answer;
    }
    payload['Username'] = latestResponse.getRespondentEmail();
    payload['Timestamp'] = latestResponse.getTimestamp();
  Logger.log(payload)
    var options = {
        "method": "post",
        "contentType": "application/json",
        "payload": JSON.stringify(payload)
    };
UrlFetchApp.fetch(POST_URL, options);
};

Thanks for showing it is possible first though @mcnaveen. Great to see the N8N examples growing.

mcnaveen · January 26, 2022, 1:07pm

That’s really great to hear

Daniel_Barra · August 29, 2023, 9:19am

Hi guys!

First of all, thanks a lot for the code. I’ve tested it with postman and it delivers exactly what I wanted.

I’m having an issue with the webhooks node.

Below is the exact code I’m using in google apps script (just changed the form ID):


function onFormSubmit(e) {
  const url = "http://n8n.navarratrader.com/webhook-test/webhook_chase"; //n8n WebHook URL

  // get our form details
  const form = FormApp.openById("XXXXXXXXXXXXXXXXXXX"); // Copy the Form ID from the URL
  const formResponses = form.getResponses();
  const fields = form.getItems()
  const fieldNames = fields.map((item) => item.getTitle())

  // list of field name like ["title", "message", "name", etc]
  console.log('fieldnames ->', { fieldNames })

  // now, fetch the latest submission
  const formResponse = formResponses[formResponses.length - 1];
  const itemResponses = formResponse.getItemResponses();
  const formSubmission = itemResponses.map((field) => field.getResponse())

  // build our payload to send to n8n
  let index = 0
  let payload = {}

  for (const field of fieldNames) {
    payload[field] = formSubmission[index]
    index++
  }

  console.log('formsubmission ->', { formSubmission })
  console.log('payload ->', { payload })

  // finally send along our request as a POST request to the n8n endpoint
  const options = {
    method: "POST",
    headers: {
      'Accept': 'application/json',
      "Content-Type": "application/json"
    },
    payload: JSON.stringify(payload),
  };

  const response = UrlFetchApp.fetch(url, options);
  Logger.log(JSON.stringify(response));

  //UrlFetchApp.fetch(url, options).then(res => res.JSON)
    //.then(response => console.log('res yo', JSON.stringify(response)))
    //.catch(err => console.log('fucking error!!!', err))
}

This is my workflow:

As you can see, my code uses POST. In the workflow I have inserted both a GET webhook and a POST webhook. Not sure why, but only the GET works.

When I disable the GET webhook node and try to use only the POST webhook node, I get the following error:

Exception: Request failed for http://n8n.navarratrader.com returned code 404. Truncated server response: {“code”:404,“message”:“This webhook is not registered for GET requests. Did you mean to make a POST request?”,“hint”:"Click the ‘Execute workflow’ … (use muteHttpExceptions option to examine full response)

I am currently running tests with the test link (workflow disabled), but I have also tried with the production link (workflow enabled) and still only the GET worked. (image below)

The main issue with the GET call is that is comes without the payload, so I can’t use the data.

I’m running an auto-hosted docker n8n on ubuntu.
My version is 0.237.0

I’m sure I’m missing something, but by myself have been unable to figure it out.
Any ideas?

Daniel_Barra · August 31, 2023, 9:17am

@mcnaveen do you think this could have something to do with the webhook tunnel?
Seems unlikely because the GET node works, right?