Recently, I was approached by my Church to develop a digital version of its membership form; a data-capture form in print that is handed out to new members for ease of communicating with them. The first solution that crossed my mind was to simply design a Google Form; that way, we could text or email the link to members to fill at their convenience. Easy enough! But when I sat down with my Pastor to further understand the wider context, it turns out the Church needed more than just a digital data-capture form. The data itself was going to be part of a funnel, hence sent to an underlying database or CRM system as part of a mailing list. That way, the Church could then communicate related information such as Church activities via email on a regular basis.
With those, I had an itch to scratch, hence the problem statement,
How do we auto-add members to our mailing list database when they fill and submit a Google Form?
As part of a different project for the Church, I already developed a central database and APIs for adding members to the database. So it was a matter of connecting the data from Google Form to the API.
To that end, I decided to explore something similar to a Webhook for Google Forms. In other words, something that triggers another action when a Google form is submitted. In this case, when a Google form is filled and submitted, the results should be sent to an API endpoint for adding members to a database.
Google Apps Script
I found an answer in Google Apps Script, a coding language based on Javascript which allows for programmatically extending Google Apps like Google Forms, Sheets, Docs etc. It is maintained by Google, well documented and pretty easy especially if you already have a basic understanding of Javascript. It comes with a fully integrated cloud-based environment for writing, running and testing the code.
How?
We’ll design the Google form and wire it up with the script to call our API endpoint for adding members to our database. I touch briefly on the API endpoint and data model without going in-depth as this isn’t about creating APIs.
The API endpoint and Data model
I share a few snippets of my API endpoint and model below. Both are part of a different Asp.Net Web API project as stated earlier. For my model, I have a SubscriberCreate DTO that gets mapped to a Subscriber model before calling the underlying service that adds it to the database. Below is a snippet from my Subscriber API Controller which points to http://babafunkenewsletterapi.com/api/subscriber
[HttpPost("subscriber")]
public async Task<IActionResult> CreateSubscriber([FromBody] SubscriberCreate dto)
{
var subscriber = _mapper.CreateDtoToEntity(dto);
var response = await _subscriberMgr.CreateEntity(subscriber);
if (!response.IsSuccess)
{
return BadRequest(response);
}
return Ok(response);
}**I’ll assume that you already have an API endpoint for adding members to your database or mailing list. If not, create one using any technology preference. It could be similar to my model below in terms of properties.
Designing the Google Form
Create a simple Google Form with the same properties as the SubscriberCreate DTO – Email, Firstname and Lastname. Make sure these are required per the API object by adding the validations. Remember to add a comment or some sort with a link to a Terms and Condition or Privacy Policy if you’ll be adding contacts to your membership database and also emailing them; think GDPR!
**Take note of the URL of the form in the browser address bar. Specifically, you’ll need the alphanumeric values just after docs.google.com/forms/d and before /edit.
Google Script Editor
On the Forms creation page, select the 3 vertical dots to the right and pick ‘Script Editor’. If you come across a message saying it can’t be open at this time, then it may be that you have multiple Gmail accounts open and logged in on your browser. I’ve found that simply signing out of all my Gmail accounts and only logging into one (in this case, the one being used for the Google Form creation) solves the issue.
On the Dashboard, you could rename the project and the default function. My file is named Babafunke.gs and in it, the onSubscriptionFormSubmit() function is explained below. You can get the Id of your form in the Address bar of your browser while editing the form. If you look at the first screenshot of the Google Form above, my Id is the alphanumeric values just after docs.google.com/forms/d and before /edit of the browser address bar.
Go to Triggers on the left of the Dashboard menu by selecting the ‘clock’ icon on the left menu. Create a new Trigger by selecting ‘Add Trigger’ and fill out the form with the appropriate options as seen in the screenshot below – event source (From form), event type (On form submit), etc. – then Save.
In the script editor, select the appsscript.json and edit it as follows to provide the right scope, else the trigger will fail. This involves adding certain permissions as below.
Testing
To test, send a link of the Google Form to yourself, fill and submit it then check that you have a response. Go to the Script Editor Dashboard. On the left menu, click the icon with the 3 horizontal bars highlighted in the screenshot below, select Executions and you should see the result. The result shown is consistent with the response from my API endpoint for successful requests.
Conclusion
That’s it. A simple way to use Google Apps Scripts with an API endpoint. That’s just one of many possibilities with Google Apps Script. For example, you could have the completed form data sent via email if that’s your required functionality without any APIs involved. The documentation provides lots of use cases. If you need any help, feel free to contact me.
References
Automate tasks with Apps Script
Google Forms notification
Authorization Scopes
Google Form Responses
HTTP and HTTPS Requests using URL Fetch Service
