CSS AND STYLING

Resources

SCRIPTS AND HACKS

ghl-experts-logo-light
  • 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

    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!

    Welcome to GHL Expert Tools

    We are still in the process of finalizing all of our trainings and setup instructions. If you need help in the meantime, please join our Facebook Group.

    Join our Facebook Group

    Join our Facebook Group to get support, platform insights, ask questions and receive updates.

    Latest Update

    Visit our changelog page to see our most recent update.

    FOMO

    Capture leads before they leave the page by creating
    entincing offers that create a fear of missing out.

    Affiliate area

    Join our affiliate program to earn commissions when you refer people to GHL Experts

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

    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