Setting Up CSV Connector

This user guide is applicable for both CSV files and Google Sheet.

Go to the API Console: https://console.cloud.google.com/apis/dashboard

Create a new Project using the button on the top right and then filling out the following form:

Then Select Project

Select Explore and enable APIs

Once created it will re-direct you to a screen where you can configure the project. First, we want to turn on the Google Drive API for this project, navigate to the Enable API’s & Services menu on the left:

From here click on the Enable APIs and services link at the top of the page. This will take to a screen where you can select which API’s to enable for your project:

We will want to search for the Google Drive API:

Enable the Google Drive API:

Next we want to navigate to the Credentials menu item on the left, this is where we will configure the credentials that we will use to extract the data:

Select OAuth client ID

Next we use the Create Credentials button at the top to setup a new “OAuth client ID”, when we do it will take us to a screen like this:

Next we will “Configure consent screen”

Setup the contact that should be notified about changes to your app. This will be your internal team that manages your data integrations.

Next we create the OAuth client:

We will want to set the application up as a desktop app:

And this will generate for you a client ID and client secret:

Using the client id from this screen go to this url in your browser to generate an auth code, make sure to replace {{CLIENT_ID}} with the client id you have received above:

https://accounts.google.com/o/oauth2/v2/auth?client_id={{CLIENT_ID}}&scope=https%3A//www.googleapis.com/auth/drive.readonly&response_type=code&redirect_uri=http://localhost&access_type=offline&prompt=consent
 

This request will redirect you to a localhost site that doesn’t exist. From the resulting redirected URL we will copy out the auth code. The url will look something like this:

http://localhost/?code=###############oRrtYg&scope=https://www.googleapis.com/auth/drive.readonly

We can then use this code and use it to request the refresh token using postman or a similar tool to make a post request to the google authorization endpoint:

URL: https://oauth2.googleapis.com/token

Body of Post: 

{
    "client_id": "#####",
    "client_secret": "#####",
    "code": "###############",
    "grant_type": "authorization_code",
    "redirect_uri": "http://localhost"
}

And we will receive back an auth code as well as a refresh token, it’ll look something like this:
 

{
    "access_token": "####",
    "expires_in": 3599,
    "refresh_token": "####",
    "scope": "https://www.googleapis.com/auth/drive.readonly",
    "token_type": "Bearer"
}

Congratulations we now have the credentials prepared for us to be able to extract the data.

From there we can configure the CSV Connector data source, we’ll need to add the client id, client secret and refresh token we have generated above along with these details: 

File URI: https://www.googleapis.com

Authentication Type: OAuth Static Refresh Token

Access Token URL: https://oauth2.googleapis.com/token

Scope://www.googleapis.com/auth/drive

Example of the form filled out:

From here we add the files we want to extract to the connector. To do this we need to find the identifier for the file in google drive. The best way to do this is to navigate to the file and open the sharing menu and using the copy link button:

The link will look something like this: 

https://drive.google.com/file/d/#######/view?usp=sharing

We will want to grab out the section that has been hashed above between the /file/d and /view? parts of the URL. This is the File ID and we will use that to set up the file in the csv connector.

Below is an example of the configuration of a cities mapping file, the relative file url for a csv file on google drive should look like this, substituting the hashed section for the file id, sourced in the above step: /drive/v3/files/########?alt=media

We then use the Run button to kick off the file extraction using the newly configured data source:


 

Use this Url for the File Uri field: https://docs.google.com if you are setting up a data source for Google Sheet. Enter https://www.googleapis.com/auth/drive as a scope

Find the URL to that Google Sheet from the Copy Link button. 

That URL will look like this: https://docs.google.com/spreadsheets/d/#####/edit?usp=sharing. We would to grab the google sheet identifier, which is the hashed portion of that URL

Put in a relative URI that begins with /spreadsheets/d/ followed by the identifier of the file. Append /export?format=csv.

In this example, 1-9bZHiCR5rjfb9PIV_x290pAZSMNKXvvIwfP4rS7eso is the identifier of the file: 
 

/spreadsheets/d/1-9bZHiCR5rjfb9PIV_x290pAZSMNKXvvIwfP4rS7eso/export?format=csv.