PLEASE NOTE: This is a fairly technical task and involves a little bit of coding, our support in this area goes as far as this document, but if you require further assistance we can be commissioned to do this whole work for you!!
Google Sheets has a rich scripting language that allows you to do all sorts of automation for your spreadsheets, and not many people know about it!
It can even receive POST requests without needing a custom piece of infrastructure using webhooks which means we can integrate with it with Databowl and send live leads to it with only a little bit of code added to the sheet.
Set up your Google Sheet
1. First, you'll need to be the owner of the Google Sheet for this to work properly.
In the past we've had issues with shared sheets when users don't own them, so it might be best to start with a fresh sheet.
Once you have this set, from your Google Sheet you need to get to the script editor.
Go to Extensions
and then Apps Scripts
2. Paste this script into the script code editor, replace any code that was there and hit Save.
//this is a function that fires when the webapp receives a GET request
function doGet(e) {
return HtmlService.createHtmlOutput("request received");
}
//this is a function that fires when the webapp receives a POST request
function doPost(e) {
var params = JSON.stringify(e.postData.contents);
params = JSON.parse(params);
var myData = JSON.parse(e.postData.contents);
var sheet = SpreadsheetApp.getActiveSheet();
var lastRow = (Math.max(sheet.getLastRow(),1));
sheet.insertRowAfter(lastRow);
lastRow++;
sheet.getRange(lastRow, 1).setValue(myData.leadid);
sheet.getRange(lastRow, 2).setValue(myData.firstname);
sheet.getRange(lastRow, 3).setValue(myData.lastname);
sheet.getRange(lastRow, 4).setValue(myData.email);
sheet.getRange(lastRow, 5).setValue(myData.phone);
SpreadsheetApp.flush();
return HtmlService.createHtmlOutput("post request received");
}
Map Your Fields
In this example you can see the following mapped fields are being used:
sheet.getRange(lastRow, 1).setValue(myData.leadid);
sheet.getRange(lastRow, 2).setValue(myData.firstname);
sheet.getRange(lastRow, 3).setValue(myData.lastname);
sheet.getRange(lastRow, 4).setValue(myData.email);
sheet.getRange(lastRow, 5).setValue(myData.phone);
These are leadid, firstname, lastname, email, phone
These are your forwarding fields that are mapped in your databowl forwarder.
Our recommendation is to ensure you are mapping to names with no spaces and keep the characters to A-Z _ - and 0-9 for ease
So instead of
First Name
use something like
FirstName / firstName / firstname / first_name etc.
The naming convention here is up to you.
Adding new fields
If you want to add more to this list pay attention to this bit:
sheet.getRange(lastRow, 5).setValue(myData.phone);
The 5 there, is basically the 5th column on your spreadsheet.
So if you want to add a 6th column, say for a field called newfield, you can do it like this:
sheet.getRange(lastRow, 6).setValue(myData.newfield);
So just keep incrementing that number every time you add a new field.
NB: If you don't do this, you may just end up overwriting the previous input on that field
Adding data to a specific sheet or tab
By default we're using the active tab or sheet. That's what this bit of our code means:
var sheet = SpreadsheetApp.getActiveSheet();
If you want to target a specific one by name, you can swap that out with the following.
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("first");
Publishing your Code
On new accounts:
Hit the Deploy button in the top right
Type: Choose Web App if not already selected.
Description: Name your App - this is internal so something useful to you
Execute as, ensure the "Me" option is selected
Who has access: ensure "Anyone" is selected
Click Deploy
Authorise the app if requested
Copy the web app url as shown below
Set up your forwarder
Get Your URL for testing
The URL that you got in the previous step will be the webhook that you can use in your forwarder.
This should look like this:
https://script.google.com/macros/s/UNIQUE_KEY_HERE/exec
Ensure its a JSON as content type.
If your's looks a little different, see the troubleshooting steps below.
You can test this webhook in your browser first by pasting it. It will say “request received” if you have things set up correctly. You should also see a cell in your spreadsheet populate.
Map Your Responses
The response from Google will be a block of HTML code something like this:
<!doctype html> <html> <head> <meta name="chromevox" content-script="no"> <link rel="stylesheet" href="https://fonts.googleapis.com/icon?family=Material+Icons" nonce="G8bfsz_rqHSiIf21J-AKyg"><link rel="stylesheet" href="/static/macros/client/css/1335360090-mae_html_css_ltr.css"> <script type="text/javascript" src="/static/macros/client/js/1362566795-warden_bin_i18n_warden__en_gb.js"></script> </head> <body> <table id="warning-bar-table" class="full_size" cellspacing="0" cellpadding="0"><tr><td><div id="warning" class="warning-bar"></div></td></tr><tr><td style="height: 100%"><iframe id="sandboxFrame" allow="accelerometer *; ambient-light-sensor *; autoplay *; camera *; clipboard-read *; clipboard-write *; encrypted-media *; fullscreen *; geolocation *; gyroscope *; magnetometer *; microphone *; midi *; payment *; picture-in-picture *; screen-wake-lock *; speaker *; sync-xhr *; usb *; web-share *; vibrate *; vr *" sandbox="allow-downloads allow-forms allow-modals allow-popups allow-popups-to-escape-sandbox allow-same-origin allow-scripts allow-top-navigation-by-user-activation allow-storage-access-by-user-activation"> </iframe> </td></tr></table><script nonce="pdoRAAX-SpKRX9HmJliz4A"> (function() { var el = document.getElementById('sandboxFrame'); el.onload = function() { goog.script.init("\x7b\x22functionNames\x22:\x5b\x22doPost\x22\x5d,\x22sandboxMode\x22:\x22IFRAME_SANDBOX\x22,\x22callbackTimeout\x22:390000,\x22deploymentId\x22:\x22AKfycbzgXumU42cLo5HZhUmnwZs1hpxtOd2JXWh_5uCtTxSHYGI4sE_1dR9WF1ap3jePAQukgw\x22,\x22eei\x22:\x22\x22,\x22sandboxHost\x22:\x22https:\/\/n-ywsllbawo7aft6tovz55pr5q7kkp5jnnaw2mdwq-0lu-script.googleusercontent.com\x22,\x22clientSideProperties\x22:\x7b\x22google.script.sandbox.mode\x22:\x22IFRAME_SANDBOX\x22,\x22google.script.host.origin\x22:\x22https:\/\/docs.google.com\x22\x7d,\x22actionPrefix\x22:\x22\/macros\/s\/AKfycbzgXumU42cLo5HZhUmnwZs1hpxtOd2JXWh_5uCtTxSHYGI4sE_1dR9WF1ap3jePAQukgw\x22,\x22userHtml\x22:\x22post request received\x22,\x22ncc\x22:\x22\x7b\\\x22awhs\\\x22:true\x7d\x22\x7d", "", undefined, true , false , "false", "https:\/\/n-ywsllbawo7aft6tovz55pr5q7kkp5jnnaw2mdwq-0lu-script.googleusercontent.com", "\/\/drive.google.com\/abuse?id\x3dAKkXjoyfw5muW7fRbqAETXLYTuIDQ7xzEQHx69J5BM1anlh6otfDGRcp2x8oXb1_IvRJFX9QPD-zPl6MF9v9TM8hiZO6MdOk-Ex0q2f2%3A0");} el.src = 'https:\/\/n-ywsllbawo7aft6tovz55pr5q7kkp5jnnaw2mdwq-0lu-script.googleusercontent.com\/userCodeAppPanel'; }()); </script> </body> </html>
The response header will be Status: 200 OK for a successful delivery.
Rather than map a response body contains, please map using Match Type: Status code Equals and select Status: 200 OK
Troubleshooting
Your URL is not working
Depending on your Google Apps instance, you may need to adjust the URL to make it work.
If your URL is not:
Or:
Take the unique key from the URL you have and use it in the first format above.
Adding new fields or changing your setup
If you need to add more fields or change things, that's fine, just go back through the steps.
Remember that if you have email in column 1, and then you change it to column 2 in the code, you may want to first move your existing data around in the sheet, otherwise you'll end up with emails all over the place.
Data validation on the sheet
If you're supplying data to a sheet that uses dropdowns or other validation, make sure that the data from Databowl is formatted correctly.
Enjoy.
Now you can sit back and chill out whilst we deliver leads to Google Sheets for you.
🚀 Need More assistance?
If you're stuck or if this isn't something you or your team is comfortable with, then please open a new chat/ticket (chat icon in the bottom right of the screen) or email support@databowl.com.
We're here to assist with any questions you have, and if required we can quote on actioning work like this for as part of our Marketing Services.