I. Setup

In order to integrate a webhook with a Google Sheets file, you will first need to create a new document

Start by navigating to Google Sheets and creating a new Blank spreadsheet.

Once in the document, click on Tools and then on Script Editor, as shown below.

This will take you to a text editor, where you can insert any sort of script of your own.

In this editor, you will need to insert the code, which can be found at the bottom of the article. Save the document after you paste the code. The final result should look like the following:

II. (Optional) Add Names to the Columns of the Spreadsheet

Once this is done, you might want to add names to the columns of your empty table. To do so, save your project and click the Select function button. A list of the functions in the editor will appear. Select “populateHeaders” and click the “Run” button.

The first time you do this, you will be asked to sign in with your Google account and will be warned that this is not an official Google application. Agree with the warnings and authenticate yourself. Once this is done, the first row of your spreadsheet should have values in it.

III. Connecting the Spreadsheet to Expandi

Now, for the actual connection between Expandi.io and your Google Sheet file. Start by publishing your script. Click on the “Publish” button in the top bar and select the first option – “Deploy as web app…”

In the newly appeared screen, it is important that you choose “Anyone, even anonymous” in the “Who has access to the app” field. Now, click the blue “Deploy” button.

After you click Deploy a pop up window will appear - copy the link from the newly displayed link.


Finally, simply create a new webhook in Expandi.io. This can be done by going to the website. On your left, there are menus. Choose the menu Web hooks. Click on  Add a Web hook by clicking on the button in the upper right corner.

When you click on Add a new hook a pop up screen will appear. 

  1. You should fill in a name for your web hook
  2. You should choose the event you want your hook to be triggered (connection request sent, connection request accepted, Contact replied to campaign message)
  3. You should choose the campaign for which you want this web hook to be triggered (you choose from your existing campaigns).
  4. Enter the URL you copied earlier.

Click on Create new web hook. Every time the hook is fired, an entry will be inserted to your spreadsheet.

Push  manually

A new feature that Expandi has is to manually push the people through a web hook. You can do it in to ways - either select the people you want to push, or apply filters and push everyone who matches these filters.

You can find this export feature in the menus Search, Campaign and My Network.

Code for Google Sheets (for step 1):

/* Receive Expandi Webhooks in Google Sheets */
// Below function could be ran to populate the header columns
function populateHeaders(hook_load) {
if(hook_load === undefined) {
hook_load = {
'hook': {
'id': 62,
'li_account': 30,
'name': 'Google Sheets Test',
'event': 'linked_in_messenger.new_contact'
},
'contact': {
'id': -1,
'first_name': 'Example First Name | can be null',
'last_name': 'Example Last Name | can be null',
'tags': [
'tag_1',
'tag_2'
],
'sales_nav_tags': [
'sales_nav_tag_1',
'sales_nav_tag_2'
],
'phone': '0800-EXAMPLE-900 | can be null',
'address': 'Exampletown | can be null',
'company_name': 'Example Inc. | can be null',
'job_title': 'Example Job Title | can be null',
'profile_link': 'www.example.com | can be null',
'image_link': 'www.example.com/picture.jpg | can be null',
'email': 'example@example.com | can be null'
},
'messenger': {
'contact_status': 'New Contact',
'conversation_status': 'Awaiting Reply',
'li_account': 30,
'new_messages': false,
'message': null,
'connected_at': 'The time of becoming connection | can be null',
'campaign_instance': 'Example campaign instance name | can be null',
'note': 'Note you have about your example contact | can be null'
}
};
}
var sheet = SpreadsheetApp.getActiveSheet();
var last_row = Math.max(sheet.getLastRow(),1);
sheet.insertRowAfter(last_row);
var headers_row = [];
headers_row.push('timestamp');
var hook_load_keys = Object.keys(hook_load)
for (i = 0; i < hook_load_keys.length; i++) {
var inner_object = hook_load[hook_load_keys[i]]
var inner_object_keys = Object.keys(inner_object)
for (k = 0; k < inner_object_keys.length; k++) {
if (inner_object_keys[k] == "placeholders") {
var placeholders = Object.keys(inner_object[inner_object_keys[k]])
for (p = 0; p < placeholders.length; p++) {
headers_row.push(hook_load_keys[i] + '_' + inner_object_keys[k] + '_' + placeholders[p])
}
} else {
headers_row.push(hook_load_keys[i] + '_' + inner_object_keys[k])
}
}
}
sheet.appendRow(headers_row);
SpreadsheetApp.flush();
};
// Catch a post from Expandi
function doPost(e) {
var sheet = SpreadsheetApp.getActiveSheet();
var last_row = Math.max(sheet.getLastRow(),1);
sheet.insertRowAfter(last_row);
var hook_load = JSON.parse(e.postData.contents);
var hook_load_keys = Object.keys(hook_load)
var new_sheet_row = [];
if (sheet.getLastColumn() == 0) {
populateHeaders(hook_load)
}
var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
new_sheet_row.push(new Date());
placeholders_to_add = []
for (i = 0; i < hook_load_keys.length; i++) {
var inner_object = hook_load[hook_load_keys[i]]
var inner_object_keys = Object.keys(inner_object)
for (k = 0; k < inner_object_keys.length; k++) {
if (inner_object[inner_object_keys[k]] instanceof Object && inner_object[inner_object_keys[k]] != null) {
if (inner_object_keys[k] == "placeholders") {
var placeholders = Object.keys(inner_object[inner_object_keys[k]])
for (p = 0; p < placeholders.length; p++) {
if (!headers.includes(hook_load_keys[i] + '_' + inner_object_keys[k] + '_' + placeholders[p])) {
sheet.getRange(1,1,1).offset(0, sheet.getLastColumn()).setValue(hook_load_keys[i] + '_' + inner_object_keys[k] + '_' + placeholders[p]);
}
}
}
}
}
}
headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
for (h in headers){
for (i = 0; i < hook_load_keys.length; i++) {
var inner_object = hook_load[hook_load_keys[i]]
var inner_object_keys = Object.keys(inner_object)
for (k = 0; k < inner_object_keys.length; k++) {
if (inner_object[inner_object_keys[k]] instanceof Object && inner_object[inner_object_keys[k]] != null) {
if (inner_object_keys[k] == "placeholders") {
var placeholders = Object.keys(inner_object[inner_object_keys[k]])
for (p = 0; p < placeholders.length; p++) {
if (headers[h] == hook_load_keys[i] + '_' + inner_object_keys[k] + '_' + placeholders[p]) {
new_sheet_row[h] = placeholders[p]
}
}
} else {
if (headers[h] == hook_load_keys[i] + '_' + inner_object_keys[k]) {
if (Object.prototype.toString.call(inner_object[inner_object_keys[k]]) === '[object Array]') {
new_sheet_row[h] = inner_object[inner_object_keys[k]].join(", ")
} else {
new_sheet_row[h] = inner_object[inner_object_keys[k]]
}
}
}
} else {
if (headers[h] == hook_load_keys[i] + '_' + inner_object_keys[k]) {
new_sheet_row[h] = inner_object[inner_object_keys[k]]
}
}
}
}
}
sheet.appendRow(new_sheet_row);
SpreadsheetApp.flush();
}


Did this answer your question?