CSS and Styling

Resources

Scripts and Hacks

GHL to Google Spreadsheet – No Zapier

This is a step by step guide and walkthrough on how to connect send data from GHL to a google spreadsheet without having to use Zapier or Integromat.

Main Install Video

Address Update

Step 1: Create a new spreadsheet


Step 2: Add in all the columns you want to capture from the contact information:

Standard Fields

General Fields
contact_id
first_name
last_name
full_name
email
phone
tags
address1
city
state
country
date_created
postal_code
company_name
website
date_of_birth

Opportunity Information:
opportunity_name
status
lead_value
opportunity_source
pipleline_stage
pipeline_id
pipeline_name
owner

Location Fields:
location.name
location.address
location.city
location.state
location.country
location.postalCode
location.fullAddress
location.id

Appointment Fields:
calendar.title
calendar.calendarName
calendar.selectedTimezone
calendar.startTime
calendar.endTime
calendar.status
calendar.appoinmentStatus
calendar.address
calendar.notes

User Fields:
user.firstName
user.lastName
user.email
user.phone
user.extension

For all custom fields, you just need to use the full name you gave it.


Step 3: Add Google App Script

Rename the sheet and paste in the following code:

function doGet(e){
return handleResponse(e);
}
function doPost(e){
return handleResponse(e);
}
function handleResponse(e) {
var lock = LockService.getPublicLock();
lock.waitLock(30000);  // wait 30 seconds before conceding defeat.
try {
var apiKey = PropertiesService.getScriptProperties().getProperty('apiKey');
var queryString = parseQuery(e.queryString);
if(queryString != null && queryString.apiKey) {
if(queryString.apiKey[0] != apiKey) {
return ContentService
.createTextOutput(JSON.stringify({"error":"Please provide API Key"}))
.setMimeType(ContentService.MimeType.JSON);
}
} else {
return ContentService
.createTextOutput(JSON.stringify({"error":"Please provide API Key"}))
.setMimeType(ContentService.MimeType.JSON);
}
// next set where we write the data - you could write to multiple/alternate destinations
var sheetName = PropertiesService.getScriptProperties().getProperty('sheetName');
var sheet = SpreadsheetApp.getActive().getSheetByName(sheetName);    
// we'll assume header is in row 1 but you can override with header_row in GET/POST data
var headRow = e.parameter.header_row || 1;
var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
var nextRow = sheet.getLastRow()+1; // get next row
var row = [];
var data = JSON.parse(e.postData.contents);
var locationArray = ["location.name", "location.address", "location.city", "location.state", "location.country", "location.postalCode", "location.fullAddress", "location.id"];
var calendarArray = ["calendar.title", "calendar.calendarName", "calendar.selectedTimezone", "calendar.startTime", "calendar.endTime", "calendar.status", "calendar.appoinmentStatus", "calendar.address", "calendar.notes"]
var userArray = ["user.firstName", "user.lastName", "user.email", "user.phone", "user.extension"]
var noteArray = ["note.body"]
// loop through the header columns
for (i in headers){
if (headers[i] == "Timestamp"){ // special case if you include a 'Timestamp' column
row.push(Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "MMM d yyyy HH:mm:ss"));
} else if (locationArray.includes(headers[i]) && ('location' in data)){ 
row.push(data.location[headers[i].split('.')[1]]);
} else if (calendarArray.includes(headers[i]) && ('calendar' in data)){ 
row.push(data.calendar[headers[i].split('.')[1]]);
} else if (noteArray.includes(headers[i]) && ('note' in data)){ 
row.push(data.note[headers[i].split('.')[1]]);
} else if (userArray.includes(headers[i]) && ('user' in data)){ 
row.push(data.user[headers[i].split('.')[1]]);
} else { // else use header name to get data
row.push(data[headers[i]]);
}
}
// more efficient to set values as [][] array than individually
sheet.getRange(nextRow, 1, 1, row.length).setValues([row]);
// return json success results
return ContentService
.createTextOutput(JSON.stringify({"result":"success", "row": nextRow}))
.setMimeType(ContentService.MimeType.JSON);
} catch(e){
// if error return this
e = (typeof e === 'string') ? new Error(e) : e;
Logger.severe('%s: %s (line %s, file "%s"). Stack: "%s" . While processing %s.',e.name||'', 
e.message||'', e.lineNumber||'', e.fileName||'', e.stack||'', e.processingMessage||'');
return ContentService
.createTextOutput(JSON.stringify({"result":"error", "error": e}))
.setMimeType(ContentService.MimeType.JSON);
} finally { //release lock
lock.releaseLock();
}
}
function parseQuery(query) {
if (query) {
return query.split("&")
.reduce(function(o, e) {
var temp = e.split("=");
var key = temp[0].trim();
var value = temp[1].trim();
value = isNaN(value) ? value : Number(value);
if (o[key]) {
o[key].push(value);
} else {
o[key] = [value];
}
return o;
}, {});
}
return null;
}


Step 4: Add in custom properties for apiKey and sheetName

You must use a unique apiKey that you generate, this is to secure the sheet so other can’t easily write to it. You also need to create a property for the sheet name that you want to write the data to.


Step 5: Deploy as web app and get the URL that is generated. Make sure to always deploy as a new version and access as Anyone, even Anonymous

*The first time you deploy, you will be asked to authenticate the script, once that is done you won’t have to do it again.


Step 6: Add the URL to a webhook trigger within GHL and append the following query parameter:

?apiKey=[YOUR_API_KEY]

Step 7: Make sure to test!

© 2020 GHL Experts. All Rights Reserved. Made with Love from Montreal             Terms & Conditions            Privacy Policy

This site is not a part of the GoHighLevel website or HighLevel, LLC. Additionally, this site is not endorsed by GoHighLevel in any way. GoHighLevel is a trademark of HighLevel, LLC

© 2020 GHL Experts. All Rights Reserved.Made with Love from Montreal             

Terms & Conditions            Privacy Policy

This site is not a part of the GoHighLevel website or HighLevel, LLC. Additionally, this site is not endorsed by GoHighLevel in any way. GoHighLevel is a trademark of HighLevel, LLC