How to connect Google Forms to an external API without using 3rd party tools (with pictures).

Google Forms is a very useful customer facing tool and we find that my of our clients use it. While we have created similar in house tools in the past, a current client requested that we connect his Google Form to a dashboard and management console that we’re developing for him. In short, he wants to use the Google Form to on-board users… And the responses should automatically be available in the tool we’ve created for him. Useful, isn’t it? You might have seen this article about how to do this and wonder why I even bother writing one more article… Well, the truth is, I couldn’t find a single one that actually work. You can jump over to the “How to connect Google Forms to a cloud function” if you know enough about the background of these things. It’s not a bad idea to try it with a dummy cloud function as discussed in the next section, though.

Create a dummy PHP function to send information from the Google Forms

For this post, we’ll assume that we have a web endpoint to which we can send JSON formatted data. For my examples below, this endpoint is going to be https://eidelberglee.com/test.php, but obviously your endpoint will reside on your own domain.

This PHP file might look something like this:

<?php
$json = file_get_contents(“php://input”);
$fp = file_put_contents(“request.log”, $json, FILE_APPEND);
echo “wrote ” . $fp . ” bytes to file.”;
?>

This PHP code will simply dump JSON to a log file.

Next, the part we’ve all gathered here today, is how to configure the Google Form to actually send the data (Formatted as JSON, in this case). This is the tricky part, where I had to be a little creative. The main problem you’re likely to run is that the OnSubmit trigger doesn’t work with the UrlFetchApp function… But I’m getting ahead of myself.

How to connect the Google Form to a Cloud API

First, go to the Script editor

The first step is to create a Google Form. An important step at this point is to obtain the form ID. This can be done by copying the URL during the editing. It should look something like this: https://docs.google.com/forms/d/13-lszGnh6qIawuJPvvFZycqGF-rlVJww0hz7dX0D444/edit. The bold part ( 13-lszGnh6qIawuJPvvFZycqGF-rlVJww0hz7dX0D444 ) is the form ID. Copy it and save it somewhere for later, when we’re creating our scripts.

Once that’s complete, you need to open up the Script Editor as shown in the above image. If you do not have a project, you will need to create a new one and connect it to the Google Account that manages the form. You should see a screen with a script editor, where a myFunction was created for you. Edit that function to be the following code:

function myFunction()
{
var form = FormApp.openById(“13-lszGnh6qIawuJPvvFZycqGF-rlVJww0hz7dX0D444”); // << Replace with your form ID
ScriptApp.newTrigger(“MyFormSubmit”).forForm(form).onFormSubmit();
}

This code will create a custom trigger for the form that will call the MyFormSubmit function when the form is submitted.

Once you’ve changed the ID to your correct ID, select “Run” on the top menu and run the function. You can then go to view->executions to see that the function executed without errors. You can also go to My Triggers on that same page and make sure that a trigger was created.

Next, create a new file in the project editor and put the following code in there:

// This function will be called when the form is submitted
function MyFormSubmit(event)
{
var url = “https://eidelberglee.com/test.php”;
// The event is a FormResponse object:
// https://developers.google.com/apps-script/reference/forms/form-response
var formResponse = event.response;
// Gets all ItemResponses contained in the form response
// https://developers.google.com/apps-script/reference/forms/form-response#getItemResponses()
var itemResponses = formResponse.getItemResponses();
// Gets the actual response strings from the array of ItemResponses
var responses = itemResponses.map(function getResponse(e) { return e.getResponse(); });
// Post the payload as JSON to our Cloud Function
UrlFetchApp.fetch(
url,
{
“method”: “post”,
“payload”: JSON.stringify({
“responses”: responses
})
}
).getContentText();
}

This code will first define the URL to which the JSON should be sent. Next, it will use the event.response object to parse the responses. Finally, the UrlFetchApp.fetch will execute the post command and send the responses as JSON to the cloud function.

Please note, when you save this function, the project manager should ask you to add certain permissions to the project. Make sure you go through that process, as you will get errors if you do not grant these permissions. You can see them under your general project settings, under the graph of calls/errors with the title “Project OAuth Scope”. You should have the following 3 scopes:

  • Connect to an external service
  • Allow this application to run when you are not present
  • View and manage your forms in Google Drive

That’s it! Make sure that the endpoint is written correctly (if you’re using our sample code above for the PHP, please note that PHP will not pass the JSON body at redirects, so make sure you use HTTP / HTTPS and www or non-www versions correctly). Now, go to your test Google Forms and attempt to send a message! You should see the JSON written to your log file. If you need help with any of this, please don’t hesitate to us an email using our contact form.

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top