How To Connect Google Forms to APIs

How To Connect Google Forms to APIs

Have you ever had that feeling you wanted to capture data and just send that data to just about anywhere? Google Forms gives you an easy way to create personalized surveys with the style that you want. So we got the first requirement down with that one, but what about sending the data elsewhere? That is where TIBCO Cloud Integration comes in.

Some assumptions

There are a few assumptions that I’ve made while writing this tutorial, which should cover the majority of the people reading it. If you have any questions, feel free to post them at the TIBCO Community or here below!

  • You’ve seen TIBCO Cloud Integration before and at least have an active account (if not you can sign up here)
  • You know how to create an API spec in TIBCO Cloud Integration (if not the documentation helps you to create your first API)
  • You’re kinda familiar with Express and Node.js
  • You’ve got access to Google Forms

What will we build

First things first, let’s briefly talk about what the end result of the tutorial will be so that you’ll have a better understanding of the moving pieces. The idea is to create a Node.js app that simply logs the information that is posted to it. The information is sent to the app by a simple Google Forms form.

API first

Most apps on TIBCO Cloud Integration start with the API first and this isn’t an exception to it. Because we’ll model the Google Form based on the API we need to specify first how the API will look like. To create an API spec we need to give it a name and a version:

  • Name: GoogleForm
  • Version: 1.0.0

We’ll need a POST operation and in my example I’ll call it ‘request’. The stuff I want to log is quite simple. I want to have a name, an email address and some feedback. You can generate the request schema from a sample message. That sample will look something like this:

{
  "name": "Leon",
  "email": "some@email.com",
  "feedback": "This is really a cool tutorial",
  "apikey": "12345"
}

As you can see I added a field called ‘apikey’. That field will get a special hardcoded value so my app can check that the input came from the Google Form rather than from somewhere else. The API spec, together with all source code can be found here.

Let’s build the app

Now let’s generate the Node.js code from the API Modeler and implement the logic. To generate the Node.js app go to the API specs page, hover over an API specification and click Generate Node.js code. Once you’ve unzipped the app open the file ‘request.js’ in the handlers folder and remove lines 19 to 27. This is the default generated code and we’ll replace it with something else. What we want the code to do is check if the apikey matches a predefined value and if so log the data. If it doesn’t match just ignore the data. Your code will look something like the code below:

...

var status = 200;
// Checks if the apikey in the body matches the value specified
        if("A985C1924862054FB7308AB6695C6C2D97DAB97B3528338DD76482EF127A6CDE" == req.body.apikey) {
            // Just log the entire message
            Logger.log(Logger.LOG_DEBUG, `Body parameters received ${JSON.stringify(req.body)}`);
        }
res.status(status).send({message:'OK'});

...

The last step is to add the below snippet on line 2 of your file. This will make sure the logger is accessible and logs the message in the correct format.

var Logger = require('../util/logger');

Now simply push the app to TIBCO Cloud Integration using the tibcli utility (or if you use Microsoft Visual Studio Code you can use the plug-in).

Now a simple form

To create a Google Forms form, go to Google Drive and select ‘New -> Google Forms -> Blank form’. Simply put three questions on there with ‘short text answer’ as the type:

pic1

Now comes the ‘hard’ part, we need to create the code that will send the response to the API on TIBCO Cloud Integration. To do that, go into the script editor for Google Forms and first of all name your project. I tend to call the script projects the same as my forms, but the choice is all yours. Now let’s write some code :)

function myOnSubmitHandler(e) {
  // Get the form responses and the values of the questions
  //   note that the questions are in the array in the order
  //   you put them on the form
  var formResponses = e.response.getItemResponses();
  var name = formResponses[0].getResponse();
  var email = formResponses[1].getResponse();
  var feedback = formResponses[2].getResponse();
  
  // Prepare JSON payload
  //   this will also have the apikey specified in the Node.js app
  var data = {
    'name': name,
    'email': email,
    'feedback': feedback,
    'apikey': 'A985C1924862054FB7308AB6695C6C2D97DAB97B3528338DD76482EF127A6CDE',
  };
  
  // Default HTTP options
  var options = {
    'method' : 'post',
    'contentType': 'application/json',
    'payload' : JSON.stringify(data),
    'muteHttpExceptions' : true
  };
  
  // Send the request to TIBCO Cloud Integration
  //    the URL comes from the app details and at the end is the resource you send the request to (the one from the API spec)
  var httpResponse = UrlFetchApp.fetch('https://integration.cloud.tibcoapps.com/nuii2guwbehquemdg2to7hflf7q5ebiy/request', options);
}

After that select ‘Edit’ in the menu and choose the item ‘Current project’s triggers’. You’ll see a short message that you have no triggers and that you can create a new one. You should have myOnSubmitHandler in the run column followed by From form and On form submit

pic2

You might get a message that your new trigger needs authorization to run, which is totally acceptable as it would need your approval to actually run :)

And test

Now preview the form and fill in some sample data (note that the email address must be valid) and hit ‘Submit’

pic3

In the logs of your app in TCI should now be a new line that has the same data:

pic4

Let me know your thoughts in the comments below or on TIBCO Community!

Let's connect

If you have any questions or comments, feel free to drop me a note on Twitter!