How to connect the Ontraport API to Google Sheets

Ontraport is a CRM and automation platform designed for any business that’s ready to scale, giving you all the tools you need for selling, marketing and managing a business online — in one app. We can use the Ontraport API to import data from Ontraport into Google Sheets. In this tutorial, we’ll show you how to connect the Ontraport REST API to Google Sheets in 5 steps:

  1. Install the Apipheny add-on
  2. Obtain an API key and an App ID
  3. Choose an Ontraport endpoint
  4. Enter your Ontraport API request into Apipheny
  5. Run the Ontraport API request in your Google Sheet

Step 1.) Install and open the Apipheny add-on for Google Sheets

Apipheny is an API connector for Google Sheets. You can use Apipheny to connect your Google Sheets to unlimited API data sources, make unlimited API requests, and more. There is a 30 day free trial included.

1.) Install Apipheny by opening the following link on desktop and then clicking the Install button in the Google Marketplace: https://apipheny.io/install

2.) After you’ve installed Apipheny, open a Google Sheet and then click on the Extensions dropdown in the menu at the top.

In the dropdown list you should see Apipheny. Click Apipheny then click Import API to open the Apipheny sidebar in your Google Sheet.

open apipheny

Tip: you can open a new Google Sheet by entering this URL in your browser: sheet.new

Step 2.) Obtain an API key and an App ID

After logging into your Ontraport account, go to your account icon, then click Administration from the drop-down menu:

Ontraport Dashboard
Ontraport Dashboard

In the menu on the left, click Integration:

Integrations section
Integrations section

On the next page, click the +New API Key button:

API Keys section
API Keys section

In the API Application form, complete all the fields and check all the Usage Agreement checkboxes:

API Application page
API Application page

At the end, click the SAVE button in the top-right corner:

Save the API Application
Save the API Application

On the next screen, complete the Key name, select the Contact Owner from the drop-down list, check the permissions you want for your API key and then click the SAVE button again:

API Key Permissions section
API Key Permissions section

Your API key and APP ID will be displayed in a dedicated API Keys table:

API Keys section- key displayed on the screen
API Keys section- key displayed on the screen

Copy both of these values and paste them in a safe location, because you’ll need them later.

Step 3.) Choose an Ontraport endpoint

In this section, we’ll show you how to browse the Ontraport API documentation to find the endpoint that retrieves the information you need from your account. If you already know your API URL, or you want to use the same example URL as us, just skip to Step 4.

First, open the Ontraport API documentation page: https://api.ontraport.com/doc/?shell#introduction

The menu on the left contains general information about the Ontraport API and a list of categorized endpoints:

Ontraport API documentation page
Ontraport API documentation page

If we click the Messages category, its description and the corresponding endpoints will be shown:

Messages category
Messages category

An example endpoint is Retrieve a specific message, which retrieves all the information for an existing message. The URL for this endpoint is:

https://api.ontraport.com/1/message?id=id_value

If you use this URL, don’t forget to replace id_value with a real message id.

The endpoint’s documentation contains a short description of its purpose, the endpoint’s URL and its method (GET), the required headers and parameters, the cURL command and an example response:

Endpoint documentation page
Endpoint documentation page

All endpoints can be accessed with the following base domain https://api.ontraport.com/1/

Endpoints should be appended to this base domain.

Step 4.) Enter your Ontraport API request into Apipheny

Now go back to your Google Sheet and make sure that the Apipheny add-on is open on the “Import” tab. With the Import tab open, enter these details into the add-on:

Method: At the top of the Apipheny sidebar, select the HTTP method (GET or POST) required by your API endpoint. For this example, we are using the GET method.

API URL: In Step 3, we explained how you can find the Ontraport REST API endpoint that you need. Now copy and paste your complete Ontraport API URL into the Apipheny add-on, where it says API URL Path, followed by any parameters required for your query (if applicable).

For this example, we are using the Retrieve a specific contact endpoint, which retrieves all the information for an existing contact. The only parameter needed is the ID for the contact, which is returned in the response, upon contact creation. This is what the whole URL looks like:

https://api.ontraport.com/1/Contact?id=contact_id

If you use this URL, don’t forget to replace contact_id with a real value.

Headers: In the Headers section of Apipheny, add two rows with the following keys and values:

Header 1
Key:
Api-Key
Value:
your_api_key

Header 2
Key:
Api-Appid
Value:
your_app_id

When entering the header values, replace your_api_key and your_app_id with the values you previously obtained in Step 2.

Here’s what our API request looks like when entered into Apipheny:

Ontraport API request entered into Apipheny
Ontraport API request entered into Apipheny

Step 5.) Run the Ontraport API request in your Google Sheet

Finally, the last step is to click the Run button at the bottom of the Apipheny add-on and then wait for the Ontraport API data to be imported into your Google Sheet. Here’s what our request looked like when completed:

Ontraport data imported into Google Sheets
Ontraport data imported into Google Sheets

That’s it! You’ve successfully connected your Ontraport account to Google Sheets using the Ontraport API, Google Sheets API, and the Apipheny add-on.

After making a successful request to the Ontraport API, try querying a different Ontraport API endpoint, or try using one of the more advanced features in the Apipheny add-on.


API Tutorials



API Knowledge

What is an API?
What is an API URL?
What are parameters?
What is an endpoint?
What is an API key/token?
What is basic authentication?
What are headers?
What is a GET request?
What is a POST request?