How to Push Webhook data into Google Sheets

Introduction 

Pushing webhook data into Google Sheets is a zero-cost option for small and medium enterprise companies who do not have a CRM. 

The Google Sheet can be shared with multiple stakeholders and can be easily updated allowing you to easily track and follow up leads from multiple sources. 

Set Up and Configuration

The following steps will be required:

1. Create or open a spreadsheet in Google Sheets.
2. Select the menu item Extensions > Apps Script.

3. Delete any code in the script editor. Simply copy and paste the below script code into the script editor.

functiondoPost(e) {

varparams = Utilities.jsonStringify(e.postData.contents);

params = JSON.parse(JSON.parse(params));

varsheet = SpreadsheetApp.getActive().getSheetByName('delacon');

vardataRange = sheet.getDataRange();

varvalues = dataRange.getValues();

varcall_id = params["PLA_Call_Id"];

varrow_index = -1;

for (vari = 0; i < values.length; i++) {

if (values[i][12] == call_id) {

row_index = i;

    }

  }

varlastRow = row_index === -1 ?Math.max(sheet.getLastRow(),1) :Number(row_index);

vartimestamp = newDate();

if (row_index === -1) {

sheet.insertRowAfter(lastRow);

varphone_number = params["PLA_Caller_Phone_Number"];

varutm_source = params["PLA_Search_Engine_Used"];

varutm_medium = params["PLA_Search_Type"];

varlanding_page = params["PLA_Landing_Page"];

varpage_called_from = params["PLA_Page_Called_From"];

varbusiness_name = params["PLA_Dealer_Name"];

varbusiness_cat=params["PLA_Dealer_Category"]

varcity = params["PLA_City"];

//sheet.getRange(lastRow + 1, 1).setValue("Not Evaluated");

sheet.getRange(lastRow + 1, 1).setValue(phone_number);

sheet.getRange(lastRow + 1, 2).setValue(timestamp);

//sheet.getRange(lastRow + 1, 8).setValue(city);

sheet.getRange(lastRow + 1, 3).setValue(utm_source);

sheet.getRange(lastRow + 1, 4).setValue(utm_medium);

sheet.getRange(lastRow + 1, 5).setValue(landing_page);

sheet.getRange(lastRow + 1, 6).setValue(page_called_from);

//sheet.getrange(lastRow + 1, 7).setValue(business_name);

//sheet.getrange(lastRow + 1, 8).setValue(business_cat);

sheet.getRange(lastRow + 1, 13).setValue(call_id);

  }

else {

varcall_duration = params["PLA_Duration"];

varrecording_url = params["PLA_RecordingFile"];

varcall_result = params["PLA_CallResult"];

sheet.getRange(lastRow + 1, 9).setValue(call_duration);

sheet.getRange(lastRow + 1, 10).setValue(recording_url);

sheet.getRange(lastRow + 1, 11).setValue(call_result);

  }

SpreadsheetApp.flush();

returnContentService.createTextOutput(JSON.stringify({

"PLA_Call_Id": call_id,

"success":true

  }));

}

4. At the top, click Save 

5. The next step is to getthe variables based on the webhook data you would like to receive for example if you would like to receive the caller’s Phone Number you will need to get the Phone number parameter "PLA_Caller_Phone_Number":"+61444444444". This information is available in our webhook support documentation: 

https://support.delaconcorp.com/hc/en-us/articles/360026572414-Delacon-Live-Call-Data-Push-API-WebHook-

6. If you would like to capture additional information 

7. Based on the line items that are required, add them in this section of the code and once they have been added click ‘Deploy’

8. Once that section of the code has been deployed, click on the arrow and select ‘Manage Deployments’ to collect the URL you will use in your webhook. 

9. Go to the Web app section and copy the URL 

10. Once you have copied the URL, Log into the Delacon portal

11. Navigate to Services > List Services 

12. Click on Edit for the service for which you want to enable the webhook.

13. Under Data Integration, set the toggle to Push live data through to the webhooks provided and add the copied URL into ‘Update URL Hook’