PLEASE USE OUR UPGRADED SCRIPT:
NEW GHL to Google Spreadsheet V2 – No Zapier
**THE BELOW SCRIPT HAS BEEN DEPRECATED**
Main Install Video
UI Update – June 10th
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 = 'YOUR_API_KEY';
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 = 'YOUR_SHEET_NAME';
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!
