How to send form data to Google Sheets via Webhooks

In this article we will go through the steps of connecting your Triggerbee Campaign Forms to Google Spreadsheet, without using Zapier or any other service. Instead, we will use the Webhook action available in Triggerbee Automations to send data to a receiving Web app that we set up inside a Google Sheet.

Here are the steps in short:

1. Create your form in Triggerbee 
2. Create a Google Sheet
3. Copy-Paste a prepared script into the Spreadsheet
4. Deploy script as a 'Web app'
5. Configure your Form to call the Web app
6. Done!

Now we will go through each step more in detail:

1. Create your form in Triggerbee

I have created the following form as an example:

2. Create a Google Sheet

Create a new Google Sheet. No need for entering anything - the column headers will be created automatically. Head over to Extensions => Apps Scripts. 

You can name your project anything you want. I have called my project Triggerbee Form

3. Copy-Paste a prepared script into the Spreadsheet

Copy the script found here: https://github.com/oloftorn/oloftorn/blob/main/triggerbeeFormsToGoogleSpreadsheet.gs

Or copy this script if you only want to print the personal data collected (email, name, phone number etc.)

function doPost(e) {
    // get data from webhook payload
    var data = getJsonFromTriggerbeePayload(e.postData);
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

    // nestle to only PersonalData
    var pd = data.visit.PersonalData;

    // Get all key value pairs from PersonalData
    var headers = Object.keys(pd);
    var values = Object.values(pd);

    // Replace "+" characters with spaces in the values
    values = values.map(function(value) {
      return value.toString().replace(/\+/g, ' ');
    });

    // Check if headers are already present in the first row
    if (sheet.getRange(1, 1, 1, headers.length).getValues()[0].join("") !== headers.join("")) {
      sheet.appendRow(headers);
    }
    // Make header bold
    sheet.getRange(1, 1, 1, headers.length).setFontWeight("bold");

    sheet.appendRow(values);
  }


  // function to clean payload from webhook
  function getJsonFromTriggerbeePayload(postData){
    var params = JSON.stringify(postData.contents);
    var jsonparams = JSON.parse(params);
    var decodedJson = decodeURIComponent(jsonparams);

    decodedJson = decodedJson.substring(8);
    return JSON.parse(decodedJson);
  }

Paste the entire script into the Apps Script Editor, and press the "Save"-button.

4. Deploy script as a 'Web app'

Select type "Web app":

No need to add any description, but make sure to select " Anyone" in the "Who has access" field.

Click Create, and then copy the URL to your newly deployed WebApp.

Note: If you decide to make changes to the script, make sure to redeploy the webapp as a new version.

5. Configure your Form to call the Webapp

Go into your Triggerbee account, and go to "Automations". Create a new automation, and as a Trigger, you want to choose "Form submission" and then pick your campaign. 

In the Actions column, add a new action called "Execute Webhook". In the webhook URL paste the URL to your Webapp.

6. Done!

Now try it out! Remember to clear cookies or run in a new inkognito between each form submission.

If you want to debug and check if the webapp has been called or not you can go to Executions in Google App Scripts:

If everything is going as planned your spreadsheet should begin to populate from the form submissions.

If you decide to add or remove form fields the script will detect this and recreate the header row.

Making changes

If you are making changes to the script you need to re-deploy the Webapp:

And then press " Deploy"-button