Import Stripe Data to Airtable

In this guide, we'll look at how to import data from the Stripe API in Airtable. The Stripe API provides access to many different types of data, but here we'll focus on importing customers, subscriptions and invoices. You can use the exact same approach to import other Stripe data into Airtable.

Install Data Fetcher

Screenshot 2021-02-17 at 16.10.03.png

Get Stripe API key

Screenshot 2021-02-16 at 15.05.06.png

There are two types of key, real and test. They are used in the same way when making API calls but the test key will return test data. It's a good idea to set up your API calls using the test key, then switch to the real one when you're happy with everything. To switch between the two in the Stripe dashboard, simply click the toggle next to 'View test data'/ 'Viewing test data' in the left-hand sidebar.

Create Stripe API Request

  • On the home screen of the Data Fetcher app, click 'Create request'.

Screenshot 2021-02-17 at 16.12.14.png

  • On the create request screen, enter the request name 'List Stripe Customers'.

Based on the Stripe API documentation, the base URL for all API requests is 'https://api.stripe.com/v1'. The endpoint for fetching a list of customers is '/customers'. By default Stripe will return 10 customers at once, but we can increase this to 100 this by adding a 'limit' parameter of 100.

  • Enter the full URL on the create request screen:

https://api.stripe.com/v1/customers?limit=100

  • Add a header with the name 'Authorization' and a value of 'Bearer YOUR_STRIPE_API_KEY'.
  • Create a table in your base called 'Stripe Customers' to import the Stripe customer data into.
  • Set the output table to this table by clicking 'Use current'.
  • Set the output view to 'Grid view' by clicking 'Use current'.
Screenshot 2021-02-17 at 16.21.57.png

Import Stripe Customers to Airtable

  • Click 'Run'. You'll see a loading message while the request runs, then the response field mapping modal will open.
  • Choose which fields from the Stripe API list customers response to keep and how they should map to the fields in your output table. You will only need to do this the first time you run the request. Create a single-line text field in your Table called 'Id' and use this for the 'data.id' field. Map 'data.name' to 'Name' and keep any other fields you want to.
Screenshot 2021-02-17 at 17.59.37.png
  • Click 'Confirm' to close the modal and create any necessary fields in the table. The request will run again and update Airtable with your Stripe customer data.

Screenshot 2021-02-19 at 08.47.08.png
  • Click 'Advanced' to open the Advanced settings and under 'Update based on Field', choose the 'Id' field. The app will use this field to ensure the customers in your base match those in Stripe. For example, if a Stripe customer is deleted it will delete it in Airtable too.

Note: Stripe uses Unix timestamp fields for date fields (e.g. 'data.created') that have numeric values. You can convert them to date format by adding a formula field with this formula, where FieldName is the numeric field name:

DATEADD('1/1/1970',{FieldName},'seconds')

Pagination

Let's set up pagination so that we always import all the customers from Stripe. Looking at the Stripe API reference we can see Stripe uses cursor-based pagination with a 'starting_after' parameter.

  • In the Advanced settings under 'Pagination' select 'Cursor'.
  • Turn on 'Fetch all pages'.
  • In the 'Cursor parameter' input, enter 'starting_after'.
  • In the 'Cursor field' dropdown, choose the 'Id' field.

Finally, click 'Save' to save your request.

Import Stripe Subscriptions to Airtable

Assuming you followed the steps above to import customers, we can duplicate that request and modify it to import Stripe subscriptions to Airtable.

  • Click 'Back' to go back to the home screen.
  • Hover over the 'List Stripe Customers' request and click the '...' icon.
  • Click 'Duplicate'. A new request will be created called 'List Stripe Customers copy'.
  • Click on 'List Stripe Customers copy' to go to the create request screen. Because we duplicated our existing request we don't need to set up the pagination, headers, 'Update Based On Field' again. We just need to update the name, URL, output table and response field mapping.
  • Change the name to 'List Stripe Subscriptions'.
  • Change the URL to:

https://api.stripe.com/v1/subscriptions?limit=100

  • Create a new table called 'Stripe Subscriptions' and set the output table to this.
  • Click 'Run'. You'll see a loading message while the request runs, then the response field mapping modal will open.
  • Choose which fields from the Stripe API list subscriptions response to keep and how they should map to the fields in your output table. Change the 'Name' field in your table to 'Id' and use this for the 'data.id' field.
  • Create a field in your table called 'Customer' linked to the 'Stripe Customers' table, then for the response field 'data.customer' set the 'Data type' to 'Link' and choose the 'Customer' field. Data Fetcher will now create and update links to between records between the two tables when this request runs!
  • Map 'data.items.data.1.price.recurring.interval' to a table field called 'Interval' and 'data.items.data.1.price.unit_amount' to a table field called 'Amount (cents).
  • Create a formula field called 'Monthly Amount' with the formula:

{Amount (cents)}/IF({Interval}='year',1200,100)

  • Click 'Confirm' to close the modal and create any necessary fields in the table. The request will run again and update Airtable with your Stripe subscriptions data.
Screenshot 2021-02-20 at 09.15.04.png

Import Stripe Invoices to Airtable

Assuming you followed the steps above, we can again duplicate and modify an existing request to import Stripe invoices to Airtable.

  • Click 'Back' to go back to the home screen.
  • Hover over the 'List Stripe Customers' request and click the '...' icon.
  • Click 'Duplicate'. A new request will be created called 'List Stripe Customers copy'.
  • Click on 'List Stripe Customers copy' to go to the create request screen.
  • Change the name to 'List Stripe Invoices'.
  • Change the URL to:

https://api.stripe.com/v1/invoices?limit=100

  • Create a new table called 'Stripe Subscriptions' and set the output table to this.
  • Click 'Run'. You'll see a loading message while the request runs, then the response field mapping modal will open.
  • Choose which fields from the Stripe API list invoices response to keep and how they should map to the fields in your output table. Change the 'Name' field in your table to 'Id' and use this for the 'data.id' field.
  • Create a field in your table called 'Customer' linked to the 'Stripe Customers' table, then for the response field 'data.customer' set the 'Data type' to 'Link' and choose the 'Customer' field. Do the same for Subscription.
  • Map 'data.amount_due' to an 'Amount Due (cents)' field, 'data.paid' to a 'Paid' field, 'data.created' to a 'Created' and 'data.hosted_invoice_url' to 'URL' field. You can use the same formula we used for customers to convert the 'Created' number field to date field.

DATEADD('1/1/1970',{Created},'seconds')

  • Click 'Confirm' to close the modal and create any necessary fields in the table. The request will run again and update Airtable with your Stripe invoices data.
Screenshot 2021-02-20 at 09.59.31.png

Import Other Stripe Data to Airtable

Follow the steps above with a different URL to import other types of Stripe Data to Airtable. You can see all the available endpoints in the Stripe API docs.

https://api.stripe.com/v1/balance

https://api.stripe.com/v1/charges

https://api.stripe.com/v1/payouts

https://api.stripe.com/v1/prices

https://api.stripe.com/v1/products

https://api.stripe.com/v1/refunds