Integrating Google Drive with JMP using HTTP Request

4 Kudos

The hardest part of integrating with Google Drive is creating a web application using the Google Console.

However, since the scope required for accessing Google Drive is more sensitive than say, JMP’s Google Sheet integration, integrating with Google Drive is not currently part of JMP as of JMP 17.

That being said, I ran into a JMP user during the recent JMP Discovery that was collecting data and storing it on Google Drive. The data format was csv for various reasons, but mainly due to Google Sheet limitations. He said his life (and his coworkers') would be easier if he could get the csv data into JMP on a regular basis so it could be analyzed. He knew he could download the csv file then import it into JMP, but he was really looking for “the easy button”.

So off I went to go through my notes on the Google Console and how to register a web app. You can readily get a developer one (like I did), but Google will rate limit you on the traffic that it has unless you go through their verification process.

Which I highly recommend, even if it is only an internal app.

So after [insert your number of hours with the Google Console], you will have the necessary pieces to login into Google Drive with JMP.

redirect_url
client_id
client_secret

All of which you used when registering your app when you selected OAuth2 authorization.

Remember, since you are using your app, you’ll want to make sure you’ve enabled the Google Drive APIs.

The other important piece is scope. Scope controls the permissions a user allows when connecting to the registered web app. It is completely dependent on the Google APIs you’re wanting to use. For instance, the attached script uses:

Openid and Profile are “standard” when authenticating and authorizing, while the remaining scopes are required to read and download data from your google drive. If you’re still reading, then the good news is that the JMP Scripting Index has an example of authenticating with Google.

It is the second example in the Get Authorization Header entry, which I started with when I wrote the attached script. In the script you can see a one-to-one correspondence of redirect_url, client_id, client_secret, etc with the attributes you have in your registered web app.

In the attached script, I added an additional housekeeping item. I wanted the user experience to be, authorize once, and then save the refresh_token locally so I won’t be prompted to login to Google again. When saved locally, it is not encrypted so you’ll want to save it in a private location. The attached script uses

$USER_APPDATA/GoogleDriveTokens.json

What this does is load the refresh_token from disk (if it’s available) gets an access_token from Google with the refresh_token, and then you’re authenticated and authorized. The last thing the script does is save it back out to disk. The New OAuth2() JSL object will keep getting tokens if the current ones have expired every time oauth2 << Get Auth Header() is used. In this case, Get Auth Header is called every time a request is made to Google Drive.

The general flow after authentication and authorization has taken place is that the endpoint

https://www.googleapis.com/drive/v3/files

Is used to enumerate over the files found on Google Drive. By default, only 100 are returned, so in the script I increase it to 1000 (the maximum).

It is worth noting that JMP 17 has

request << Send("show progress download");

This will show a progress bar for long HTTP Request operations.

The return from the REST call is JSON, which is turned into an Associative Array with ParseJON. If you have trouble visualizing the JSON data structure (which I do at times) I copy/paste the JSON into the JSON Editor extension for Chrome.

As the script processes the returned files, it keeps the metadata for

.csv
.csv.gz

The important part of the metadata is the fileid. Google uses a fileid in subsequent file access to Google Drive. In our case, we want to download the .csv and .csv.gz files and open them in JMP. So the script loops over the file_ids from the previous step and downloads the data.

The key for getting the file content is:

QueryString(["alt" => "media"])

It tells the Google Drive to send the contents of the file back to JMP. The script checks the mimeType of the file from the file metadata, but could also check the mimeType from HTTP Request.

In any case, the mimeType:

application/gzip

needs to be decompressed to get the csv contents while the mimeType

text/csv

is the contents of a csv file itself

Finally, once the contents have been obtained (and decompressed if needed)

dt = Open(blob, text); //no need to save to the file system before opening

will handle opening the data without paging to the system

Hopefully this script will be enough to give you a head start on integrating with Google Drive. I found it intriguing and I appreciate the customer asking the question.

BTW If you get stuck on the Google Console, you can ask the question here. I (or someone else) might can help out.