Create an API endpoint from a CSV on Google Drive

Introduction

In this tutorial, you’ll learn how to create a Flex.io API endpoint that returns the contents of a CSV file in Google Drive as a JSON data feed.

Specifically, you’ll learn how to:

  • Connect to Google Drive
  • Read a CSV file in Google Drive
  • Convert the CSV file to JSON format
  • Create and call an API endpoint that returns the resulting JSON

When you’re done, if you have a CSV in Google Drive:

"firstname", "lastname", "city", "state", "zip"
"Lynn", "Kuhl", "Greenville"
"Lola", "Rivera", "Concord"

You’ll be able to access it as JSON:

[
{ "firstname": "Lynn", "lastname": "Kuhl", "city": "Greenville" },
{ "firstname": "Lola", "lastname": "Rivera", "city": "Concord" },
{ "firstname": "Tracy", "lastname": "Rogers", "city": "Andover" }
]

Using an API call:

curl -X POST 'https://api.flex.io/v1/me/pipes/google-drive-csv-to-api/run'
--header "Authorization: Bearer {token}"
'

Setup

To complete this tutorial, you’ll need the following:

  • A Flex.io API key
  • A Google Drive account
  • A CSV file on Google Drive that you’d like to convert to JSON
  • A simple way to make an HTTP request to test your API endpoint

To get a Flex.io API key, sign up for Flex.io, or if you already have an account, sign in. When you sign in, you’ll see your API listed in the “API Keys” section on the dashboard where you’re directed after you sign in.

You’ll also need a Google Drive account with the CSV you want to access via an API. If you need a sample CSV to work with, go ahead and download some samples from our Data GitHub repo.

Finally, you’ll need a tool that allows you to make HTTP requests. We’ll show you how using CURL.

Build it

Next, you’re ready to build the CSV-to-JSON data feed. Following are the steps to build it manually; alternatively you can use this template to set up the data feed for you.

When you’re done, you’ll have a pipe defined in the Flex.io webservice that looks like:

Create the connection

  1. Sign in to Flex.io.
  2. Open the Connections list by selecting “Connections” from the nav menu.
  3. In the Connections list, create a new connection by clicking the “New Connection” button.
  4. In the “New Connection” dialog, select Google Drive.
  5. Authenticate the connection by signing in to Google Drive.
  6. On the connection configuration screen, name your connection “Google Drive” and give your connection an alias “google-drive-connection” on the connection configuration screen.
  7. Save the connection.

Create the pipe

  1. Open the Pipes list by selecting “Pipes” from the nav menu.
  2. In the Pipes list, create a new pipe by clicking the “New Pipe” button.
  3. In the pipe properties section, name your pipe “Google Drive CSV to API” and give your pipe an alias “google-drive-csv-to-api”.
  4. In the pipe configuration section, add the following code to access your CSV on Google Drive and convert it to JSON; make sure to replace {google-drive-path-to-csv} below with the name of your CSV file (for example “/source/my-folder/contacts.csv”):

Run the pipe

  1. In the pipe configuration section, click the “Run” button.
  2. The pipe should take about a second or so to run, and then you should see JSON in the output area similar to the following:
[
{ "firstname": "Lynn", "lastname": "Kuhl", "city": "Greenville" },
{ "firstname": "Lola", "lastname": "Rivera", "city": "Concord" },
{ "firstname": "Tracy", "lastname": "Rogers", "city": "Andover" }
]

Deployment

Once you’ve built the pipe, you’re ready to use it:

  1. Go to your command prompt and run the following CURL command with the ‘google-drive-csv-to-api’ alias you gave to the pipe:
curl -X POST 'https://api.flex.io/v1/me/pipes/google-drive-csv-to-api/run'
--header "Authorization: Bearer {token}"
'
  1. You should see JSON corresponding to your CSV on Google Drive.

In general, you can call pipes with an alias of your choice by filling in {pipe-alias} in the following:

curl -X POST 'https://api.flex.io/v1/me/pipes/{pipe-alias}/run'
--header "Authorization: Bearer {token}"
'

And of course, you can invoke the API endpoint in any server or client code that allows you to make an HTTP request.

Going further

This tutorial shows you how to create an API endpoint that returns the contents of a CSV file in Google Drive in JSON. Following a similar pattern, you can create API endpoints from other cloud storage, such as Google Sheets, Dropbox, Box, Amazon S3, as well as from other sources like SFTP.

Here are some templates to get you started:

Create an API endpoint from a CSV on Amazon S3
Create an API endpoint from a CSV on Box
Create an API endpoint from a CSV on Dropbox
Create an API endpoint from a CSV on SFTP