Connect Paypal To Google Sheets - Get Transaction Data [API Tutorial]

How to connect PayPal REST API to Google Sheets

PayPal is a well-known online payments processor that enables money transfers for companies and regular users alike.

Paypal’s API enables users to create apps that interact with their accounts, automating operations that pertain to billing, invoicing, orders, payments and many other elements.

In this tutorial, you will learn how to connect the PayPal REST API to Google Sheets in 5 steps:

Install the Apipheny Add-on
Create a Paypal Live App
Get a Paypal Access Token
Enter Paypal API into Apipheny
Run the Paypal API request

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.

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

Step 2: Create a PayPal Live App

To use the PayPal REST API, you have to create a dedicated app in your Paypal account. You can create either a sandbox app, used for testing, or a live app that uses the actual information in your account.

In this tutorial we will use a live app, to show you how you can import actual live information from Paypal into your Google Sheets. Here’s the steps:

1.) Log in to your PayPal Developer Dashboard, using the same log in information as your Paypal account. If you don’t yet have a Paypal account, you can create one by clicking on the Sign Up button:

2.) Once logged in, click on My Apps & Credentials and toggle the switch to the Live option, to start creating a live app (Paypal may prompt you to upgrade your account to a business account):

Toggle the switch to "Live" — Toggle the switch to “Live”

3.) After clicking “Live”, scroll down, and in the REST API apps section, click Create App. Then type a name for your app and then click Create App again:

Create New App - Paypal API — Create New App – Paypal API

4.) Your new app’s details page will open and display the app’s identifying information. Copy and save the Client ID and Secret for your app somewhere safe and get ready to use them in the next step.

Client ID and Secret Code you will need in the next step

5.) Review your app’s information and for the purposes of this example, make sure that the Transaction Search option is checked, before saving your app:

Step 3: Get your Paypal API Access Token

In order to access most PayPal REST API endpoints, you will need an access token. To obtain an access token, you’ll either need to do a cURL command or use the Postman app.

Click here for instructions on using the Postman app.

Here’s the instruction for cURL:

To make a cURL command, pass your OAuth 2.0 credentials through a dedicated PayPal REST API command, using the cURL command line tool (command prompt window for Windows OS or a terminal for Mac OS):

 curl -v https://api.paypal.com/v1/oauth2/token \
  -H "Accept: application/json" \
  -H "Accept-Language: en_US" \
  -u "CLIENT_ID:SECRET" \
  -d "grant_type=client_credentials"

Note: For Mac OS you may need to use single quotes ( ‘ ) instead of double quotes ( ” ).

Use the same values for Client ID and for Secret that you copied in the previous step, into the CLIENT_ID and SECRET placeholders you see above.

Note: If the token/key that you generate has an expiration time, you will need to complete this same process again to get a new token when the old one expires.

You can also use other command-line tools besides cURL, but for those, you’ll have to set the Accept header to application/x-www-form-urlencoded instead of application/json.

In response to this command, the PayPal authorization server will return your access token in the access_token field of the JSON result:

 {
     "scope": "<scope>",
    "access_token": "<Access-Token>",
    "token_type": "Bearer",
     "app_id": "<App-Id>",
     "expires_in": 31349,
    "nonce": "<nonce>"
 }

Copy the access token somewhere safe because you will need it in the next step.

Note: This JSON result also contains an expires_in field, containing the number of seconds after which your newly-retrieved access token expires. For instance, access tokens with a value of 3600 in the expires_ field will expire in one hour from the time the response was generated, meaning you will need to get a new access token if it’s been longer than an hour.

More PayPal API endpoint examples

The Show order details endpoint, which displays the a specific order details:

Method: GET
API URL Path: https://api-m.sandbox.paypal.com/v2/checkout/orders/order_id
Headers:
    Header 1 Key:
       Authorization
    Header 1 Value:
       Bearer your_token
    Header 2 Key:
       Content-Type
    Header 2 Value:
       application/json

Make sure to replace your_token with the token you previously obtained and order_id with a real order ID.

The Show details for authorized payment endpoint, which displays details for an authorized payment, by ID.

Method: GET
API URL Path: https://api-m.sandbox.paypal.com/v2/payments/authorizations/payment_id
Headers:
    Header 1 Key:
       Authorization
    Header 1 Value:
       Bearer your_token
    Header 2 Key:
       Content-Type
    Header 2 Value:
       application/json

Make sure to replace your_token with the token you previously obtained and payment_id with a real authorized payment ID.

The List transactions endpoint, which lists transactions:

Method: GET
API URL Path: https://api-m.sandbox.paypal.com/v1/reporting/transactions
Headers:
    Header 1 Key:
       Authorization
    Header 1 Value:
       Bearer your_token
    Header 2 Key:
       Content-Type
    Header 2 Value:
       application/json

Make sure to replace your_token with the token you previously obtained.

The List plans endpoint, which lists billing plans.

Method: GET
API URL Path: https://api-m.sandbox.paypal.com/v1/billing/plans
Headers:
    Header 1 Key:
       Authorization
    Header 1 Value:
       Bearer your_token
    Header 2 Key:
       Content-Type
    Header 2 Value:
       application/json

Make sure to replace your_token with the token you previously obtained.

PayPal API pagination

Pagination is a process that is used to divide a large dataset into smaller chunks (pages). Usually the endpoints that return a list of resources support pagination.

Optionally, you can include query parameters on GET calls to filter, limit the size of, and sort the data in the responses.

page – the number of the page you want to obtain
page_size – the number of records per page

Example:
https://api-m.sandbox.paypal.com/v1/invoicing/invoices?page=3&page_size=4&total_count_required=true

In the example above, the data set will be divided in sets of 4 records per page, and only the third page (page number 3) will be displayed in Google Sheets.

Step 4: Enter your PayPal API URL into the Apipheny Add-on

Back in the Google Sheet, at the top of Apipheny add-on, select your HTTP method (GET or POST) as required by your API endpoint. For this example we are using GET.

Next, enter your URL for a PayPal REST API endpoint into the Apipheny add-on, where it says API URL Path, followed by any GET parameters required for your query.

Here’s a list of available Paypal API parameters you can use.

For this example, we will use the Transaction Search API to get your PayPal account’s transaction history. This example URL uses the GET HTTP method and includes a number of parameters: start_date, end_date, fields, page_size and page.

https://api.paypal.com/v1/reporting/transactions?start_date=2020-03-10T00:00:00-0700&end_date=2020-03-24T23:59:59-0700&fields=all&page_size=100&page=1

See the screenshot below to see the URL in Apipheny.

After you enter the API URL into Apipheny, we’re going to enter some keys and values into the Headers section.

In the Headers section in Apipheny, you should add two rows with the following keys and values:

Header 1
Key 1:
Content-Type
Value 1:
application/json

Header 2
Key 2:
Authorization
Value 2:
Bearer your-access-token

It doesn’t matter what order the headers are entered. Replace your-access-token with the value from the access_token field in the JSON response you obtained in Step 3. Enter the access token after “Bearer”, with a space between “Bearer” and the token (see screenshot below).

The Content-Type and Authorization headers are also required, so you should add them as their own header:

Paypal API URL and 2 headers with access token entered in Apipheny app

Step 5: Click “Run” to query the Paypal API in Google Sheets

Click the Run button at the bottom of the Apipheny sidebar and then wait for the Paypal API data to be retrieved and added in to your Google spreadsheet, like this:

In this example, we got our Paypal transaction history data in Google Sheets by using the Apipheny add-on.

Take it a step further

Now that you know how to query the Paypal API, try using different parameters in your Paypal API URL. Click here to see a list of available parameters.

Keep in mind that the access token you obtained expires after an hour, so you’ll need to get a new access token if it’s been more than an hour.