Connecting the Planning Center API to Google Sheets

Planning Center is a popular church management software. We can use the Planning Center API to import data from Planning Center into Google Sheets. In this tutorial, I’ll show you how to connect the Planning Center API to Google Sheets in 6 steps:

  1. Install the Apipheny add-on
  2. Create an OAuth App
  3. Obtain an access token
  4. Choose a Planning Center API Endpoint URL
  5. Enter the Planning Center API request into Apipheny
  6. Run the Planning Center API request in your Google Sheets

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

To pull data from the Planning Center API to Google Sheets, first install and open the Apipheny Google Sheets add-on.

Apipheny is a no-code API integrator for Google Sheets that you can use to make unlimited API requests, connect to unlimited APIs, save API requests, schedule API requests, and more. 

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

2. After you’ve installed Apipheny, open a Google Sheet and then click on the Add-Ons or Extensions option in the top menu. In the add-ons menu, you should see Apipheny.

Click Apipheny > Import API to open the Apipheny sidebar in your Google Sheets.

Opening the Apipheny add-on in Google Sheets

Step 2.) Create an OAuth app

After logging into your Planning Center account, access your developer settings at https://api.planningcenteronline.com/oauth/applications and start creating a new app by clicking the Register one now button:

Planning Center dashboard
Planning Center dashboard

Complete all the required fields:

Register OAuth application
Register OAuth application

The values we’ve entered are just examples. You can enter your own website and any App Name that you want. Click the Submit button to finish creating the app:

Finish registering OAuth application
Finish registering OAuth application

Your app has been created. Copy the values for Client ID and the Secret and paste them in a safe place, because you’ll need them in the next steps:

OAuth application
OAuth application

Step 3.) Obtain an access token

Next, give your developer application access to your account, by entering this URL in your browser:

https://api.planningcenteronline.com/oauth/authorize?client_id=<client_id> &redirect_uri=<redirect_URI>response_type=code&scope=people

In that URL, replace the various identifiers with the following values:

  • <client_id> – This should be the client ID that you obtained earlier, while creating your developer application
  • <redirect_uri> – This should be the redirect URI that you filled in earlier in your app’s settings

In this example, we chose the people scope, but depending of the product you use, the value for the scope parameter may differ. Possible values are displayed in the following table:

Scopes table
Scopes table

Once you’ve customized your URL and visited the URL in your browser, a window will pop up. Click the Allow button to grant access:

Authorization request
Authorization request

The browser will then navigate to the redirect URL that you provided earlier. One query parameter (code) will be added in your browser’s address bar, as a parameter of this redirect URL. The code parameter is a single use login code, which we will now use to acquire a refreshable access token:

Obtain the code in browser
Obtain the code in browser

Open up a terminal window and compose the following curl command:

curl -X POST https://api.planningcenteronline.com/oauth/token -F grant_type=authorization_code -F code=<code> -F client_id=<client_id> -F client_secret=<client_secret> -F redirect_uri=<redirect_uri>

You’ll need to fill in a few parts:

  • <code> – This should be the value of the code query parameter from the previous step
  • <client_id> – This should be the client ID that you obtained earlier, while creating your developer application
  • <client_secret> – This should be the application secret, that you obtained earlier while creating your developer application
  • <redirect_uri> – This should be the redirect URI that you filled in earlier in your app’s settings
Access token in a terminal window
Access token in a terminal window

The response access_token may be used in order to access the ads API for up to two hours. The refresh_token may be used to get a new access token when that time period is up. Refreshing the access token can be done with the following command:

curl -X POST "https://api.planningcenteronline.com/oauth/token" -H "Content-Type: application/json" -d "{\"client_id\": \"CLIENT_ID \", \"client_secret\": \"CLIENT_SECRET\", \"refresh_token\": \"refresh_token\", \"grant_type\": \"refresh_token\"}"
Obtain access token with a refresh token
Obtain access token with a refresh token

CLIENT_ID and CLIENT_SECRET are the same as described before. REFRESH_TOKEN should be replaced with the value of the refresh_token field from the previous command’s response.

Step 4.) Choose a Planning Center API Endpoint URL

In this section, I’ll show you how to browse the Planning Center API documentation to find an API URL and endpoint that retrieves the specific information you need from your Planning Center account. If you already know your API URL, or you want to use the same example URL as us, just skip to Step 5.

First, open the Planning Center API documentation page: https://api.planningcenteronline.com/explorer

The colorful menu on the left contains a list of API categories, each with its own endpoints:

Planning Center API documentation page
Planning Center API documentation page

An example endpoint category is Services. This category contains many endpoints: attachment_types, email_templates, folders, media, people, report_templates, series, service_types, songs, tag_groups and teams

In this example, we will try the Folders endpoint:

https://api.planningcenteronline.com/services/v2/folders

The documentation for this endpoint contains the current URL, the URL parameters and the server response:

Planning Center API endpoint example
Planning Center API endpoint example

In the next section we will try calling this endpoint.

All API calls to the Planning Center API should be made using this base domain:

https://api.planningcenteronline.com/services/v2/

All endpoints should be appended to this URL.

Step 5.) Enter the Planning Center API Request into the Apipheny add-on

Okay, we’re in the home stretch. 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) as required by your Planning Center API endpoint. For this example, we are using the GET method.

API URL: In Step 4, I explained how you can find the Planning Center API endpoint that you need. Now copy the complete API URL into the Apipheny add-on, where it says API URL Path, followed by any GET parameters required for your query.

For this example, we are using the Email endpoint that returns a list of emails. The corresponding URL for this endpoint is:

https://api.planningcenteronline.com/people/v2/emails

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

Header 1
Key:
Accept
Value:
application/json

Header 2
Key:
Authorization
Value:
Bearer <your_token>

Enter these header keys and values into Apipheny. In the second header, <your_token> is the value of the token you obtained previously, in Step 3. There should be a literal space between Bearer and <your_token>. Do not include the brackets “<>” around your token.

This is what our request looked like when entered into Apipheny:

Planning Center API request entered into Apipheny
Planning Center API request entered into Apipheny

Step 6.) Run the Planning Center API request in your Google Sheet

The last step is to click the Run button at the bottom of the Apipheny add-on and then your Planning Center data will be imported into your Google Sheet, like so:

Planning Center data imported into Google Sheets

After making a successful request to the Planning Center API, try querying a different Planning Center API endpoint, or try using one of the more advanced features in the Apipheny add-on, such as:

  • Save and schedule your Planning Center API request
  • Make a POST request to the Planning Center API (if available)
  • Use the custom =APIPHENY() function to call the Planning Center API request inside your spreadsheet
  • Create an API request by referencing the value of a cell in the API URL with three curly braces eg. {{{Sheet1!A1}}}

Related Posts