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:
Create your form in Triggerbee
Create a Google Sheet
Copy-Paste a prepared script into the Spreadsheet
Deploy script as a 'Web app'
Configure your Form to call the Web app
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.)
// Handles GET requests
function doGet(e) {
console.log("doGet fired at: " + new Date().toISOString());
return HtmlService.createHtmlOutput("Request received");
}
// Handles POST requests
function doPost(e) {
console.log("doPost fired at: " + new Date().toISOString());
console.log("Incoming postData: " + e.postData.contents);
try {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("Sheet1");
// Parse the incoming JSON payload
var jsonData = JSON.parse(e.postData.contents);
console.log("Parsed JSON data: " + JSON.stringify(jsonData));
// Ensure header row (row 1) is current with the incoming data
ensureHeader(sheet, jsonData);
// Determine the row to insert data
var dataRow = sheet.getLastRow() + 1;
// Add the data row
AddResponses(sheet, dataRow, jsonData);
SpreadsheetApp.flush();
console.log("doPost completed at: " + new Date().toISOString());
return HtmlService.createHtmlOutput("POST request received");
} catch (error) {
console.log("Error in doPost: " + error);
return HtmlService.createHtmlOutput("Error: " + error);
}
}
// Ensures that the header row (row 1) matches the expected structure based on the JSON
function ensureHeader(sheet, json) {
var expectedHeaders = getExpectedHeaders(json);
var currentHeaders = [];
if (sheet.getLastRow() >= 1 && sheet.getLastColumn() > 0) {
currentHeaders = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
}
if (currentHeaders.length === 0 || currentHeaders.join('|') !== expectedHeaders.join('|')) {
sheet.getRange(1, 1, 1, expectedHeaders.length).setValues([expectedHeaders]);
ApplyHeaderStyle(sheet.getRange(1, 1, 1, expectedHeaders.length));
console.log("Header row updated to: " + expectedHeaders.join(", "));
} else {
console.log("Header row is up-to-date.");
}
}
// Builds the expected header array based on known fields and dynamic fields
function getExpectedHeaders(json) {
var headers = [
"Timestamp", "SessionId", "Uid", "Time", "Identifier", "GeoLocation",
"Email", "Name", "FirstName", "LastName", "Organization", "Phone",
"Username", "Title"
];
if (json.visit && json.visit.Fields) {
var fieldKeys = Object.keys(json.visit.Fields);
fieldKeys.sort();
headers = headers.concat(fieldKeys);
}
headers.push("text");
return headers;
}
// Appends a new data row based on the JSON payload
function AddResponses(sheet, row, json) {
var col = 1;
sheet.getRange(row, col++).setValue(new Date().toISOString());
var visit = json.visit;
if (visit) {
sheet.getRange(row, col++).setValue(visit.SessionId !== undefined ? visit.SessionId : "");
sheet.getRange(row, col++).setValue(visit.Uid !== undefined ? visit.Uid : "");
sheet.getRange(row, col++).setValue(visit.Time || "");
sheet.getRange(row, col++).setValue(visit.Identifier || "");
sheet.getRange(row, col++).setValue(visit.GeoLocation || "");
} else {
col += 5;
}
var pd = (visit && visit.PersonalData) ? visit.PersonalData : {};
var pdFields = ["Email", "Name", "FirstName", "LastName", "Organization", "Phone", "Username", "Title"];
pdFields.forEach(function (field) {
sheet.getRange(row, col++).setValue(pd[field] || "");
});
if (visit && visit.Fields) {
var fieldKeys = Object.keys(visit.Fields).sort();
fieldKeys.forEach(function (key) {
sheet.getRange(row, col++).setValue(visit.Fields[key]);
});
}
sheet.getRange(row, col++).setValue(json.text || "");
console.log("Data row " + row + " added, ending at column " + (col - 1));
SpreadsheetApp.flush();
}
// Applies header style (bold, font size 11, border)
function ApplyHeaderStyle(range) {
var style = SpreadsheetApp.newTextStyle()
.setFontSize(11)
.setBold(true)
.build();
range.setBorder(null, null, true, null, false, false, "black", SpreadsheetApp.BorderStyle.SOLID);
range.setTextStyle(style);
}
// Test function for manual execution in the Apps Script editor
function testFunction() {
var testData = {
"visit": {
"SessionId": 1026578280,
"Uid": 1597130962,
"Time": "2025-02-18T14:23:02",
"Identifier": "[email protected]",
"NetName": null,
"GeoLocation": "Hägersten, Sweden",
"Tags": null,
"Goals": ["Submitted the form \"Test Webhook\""],
"Revenue": 0,
"Consent": {
"Title": null,
"CurrentWebsiteUrl": null,
"AcceptedTitle": null,
"AcceptedUrl": null,
"PolicyUrl": null,
"PolicyText": null,
"IsCheckboxPresent": null,
"PolicyVersionDate": null,
"PolicyRevisionNumber": null
},
"PersonalData": {
"Email": "[email protected]",
"Name": "Testar",
"FirstName": null,
"LastName": null,
"Organization": null,
"Phone": "11111111111111",
"Username": null,
"Title": null
},
"Fields": {
"TextInput1": "Hello TestArFriurcuy",
"SmileyRating1": "5",
"responseListId": "7748ae88-e20e-4c82-be80-d032007ba35e",
"submitPath": "/",
"more test": "jajajajaj"
},
"Utm": null
},
"text": "Testar visited your website, viewed 1 pages and completed goals \"Submitted the form \\\"Test Webhook\\\"\".",
"blocks": null
};
var e = {
postData: {
type: "application/json",
contents: JSON.stringify(testData)
}
};
var result = doPost(e);
console.log("Test function result: " + result.getContent());
}
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":
Select your own account as "Run as" and authenticate it properly. You can test authentication by testing the webapp using the Test-button.
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