Are you looking for a way to send data from Ontraport to Google Sheets without the use of Zapier?

Even if not, here are some use cases why you might need it - if you want to send Ontraport data to Google sheet so that you can then use it for Google Data Studio, or just to get some analysis done using sheet formula or any other requirement that you may have which requires to send data to Google sheets.

For this task we are going to make use of Ontraport's API. Ontraport provides a REST API that allows you to access and manipulate data in your Ontraport account programmatically. You can use the API to retrieve data from Ontraport and then use a Google Sheets API to insert the data into a Google Sheet. This may sound a bit complicated, but it is not.

What you require is Ontraport API keys to send webhooks from Ontraport, a google sheet and a free Google sheet add-on called 'Webhooks for Sheet'

Let's starts with the Google Sheet add-on.

Using Webhooks for Sheets

Webhooks for sheets is a free Google sheet add-on that can instantly create Apps Script-native Webhooks for Google Sheets.

To get started, all you have to do is either go to this link - Webhooks for Sheets

or go to Google Sheets >> Extensions >> Add-ons >> Get Add-ons and search for Webhooks for sheets.

This is a free add-on and very easy to use. Below is an easy to follow instructions on how to set it up. It takes a couple of minutes to set up.

Once the set up is finished, you will end up with a webhook url like this one.

https://script.google.com/macros/s/AKfycbyM0NC9LIAGJrUokXxxxxxxxxxxxxxX6FttIVIO8zdv/exec?gid=0

So this will be you endpoint url you will be using in Ontraport. We will come back to this url later. If you don't understand what this means, you will figure it out in the next steps.

Sending webhooks from Ontraport to Google Sheets

Now that we have an endpoint ready to send the webhooks, we just need to set up the Ontraport automation to send that webhook. Let's see how it is done.

First we need a trigger. In the above example the trigger is 'A contact field getting updated'. When someone's birthday field gets updated, it triggers a webhook.

This will work on any custom objects. You can trigger this webhook when a new deal is made, or a new sale is made or whatever trigger you want which is possible in Ontraport.

So I believe you now have a trigger in mind.

Now, we will look into the actual webhook.

Creating the Webhook

To set up a webhook in ontraport, you need a few pieces - an end point url, a Webhook method, header data for authorization and things like that, and the actual data that you want to send in json format.

Destination Url : We already have that url from Google sheet add-on

Find your url from the add-on. It will look something similar to the one below. (https://script.google.com/macros/s/AKfycbyM0NC9LIAGJrUokXxxxxxxxxxxxxxX6FttIVIO8zdv/exec?gid=0)

Webhook method : POST Method : As we are going to add a new row to google sheets

Header : Any authorization code, if needed and other meta info (For this particular case, we don't need any data in this field)

Data in JSON format : Depending on your requirement, create the data from Ontraport merge fields.

How to create the Data to send from Ontraport to Google Sheet

Sending the data in correct format is very important when it comes to webhooks. But Ontraport team has made it easier by giving us access to their Live api testing tool.

All you have to do is, go to the API Live link and insert your Ontraport api app id and API key. It will then generate all the objects that are available on your account and generate all the relevant methods like POST, PUT, GET etc that are available for those objects.

You can add details like contact_id and click on Try now and it will generate a Response body along with Response code. If it is 200, you have successfully send a webhook.

But we are more interested in figuring out the correct JSON format. So we look at the response body and look specifically at the notations after "attrs"

An example is given below.

{
      "dlm": "1671847053",
      "id": "1",
      "email": "[email protected]"

    }

In the above example, "dlm" is an auto generated timestamp when the operation was carried out. We don't have to worry about it. But we just got the format in which we need to send the data.

{
      "column_name_1": "<merge_field_data_1>",
      "column_name_2": "<merge_field_data_2>",
      "column_name_3": "<merge_field_data_3>"

    }

Important Note : The last element doesn't have a comma at the end. If you add it by mistake, it wouldn't work as expected.

So the column names will be the column names that you want to appear on your Google sheet.

Merge field data are the data from ontraport you want to send to google sheet corresponding to the column names.

So an actual example will look like this

In the above example, we will be sending Birth Date of a contact.

So when you finally send the webhook, google sheet will get updated as follows

Once you publish this automation, whenever the trigger gets activated, the webhook will send the data to Google Sheet.

Live Demo

Want to see a magic and live demo? Click here to see this in action in Ontraport and Google Sheets.

Hope this helps.