Export to Google Spreadsheet with OAuthAuthentication

Update

If I were to do this today, I would probably use Google's SDK for Java and JavaLink, which would allow for a better solution. Some have asked me why I said that we have to involve the browser to do it in Mathematica, I was not clear on this point. The reason is that no ready made implementation of the required RSA SHA-256 encryption exists, so one has to implement that first if one wants to do it without using other languages or tools.

Preamble

From what I can tell, it's not possible to authorize with Google Data without involving the browser at some point. Among the different oAuth 2.0 flows that Google offers, I find the flow intended for devices the most suitable one for integration with Mathematica.

In essence, it works like this:

1. The user visits the API console and registers his application. Google provides the user with a "client ID" and a "client secret".
2. The application sends a request using the client ID. This request includes information about what kind of privileges the application needs.
3. Google returns a code. When the user visits a certain URL and enters the code he will be able to review the list of privileges that the application requests. He then has to click a button to authorize the application to retrieve such information as is granted by those privileges.
4. The application pings Google regularly to find out whether it has been authorized. At the moment that it's been authorized, Google returns a so called "access token" which can be used to send requests to the Google Data APIs.

Authorization with Mathematica

Since you cannot authorize once and be done with it (well... see "words of warning" about refresh tokens), but rather have to authorize every time you need to use the APIs, it's important that the process of authorization is not too cumbersome. I've implemented my solution as a package with a graphical user interface to make it as smooth as possible. Here's how it looks:

The workflow mirrors the list in the preamble. At first, only the top form will be shown. Upon sent request, the two bottom boxes will appear. As the user visits the verification URL and enters the user code, Google will authorize the application. The package pings Google at the interval recommended by Google, and when the application is authorized the bar stops moving and the text changes, as can be seen, to "We're now authorized."

At this point, we may acquire the access token like this:

GoogleOAuthAccessKey[]

access-key...

Installing the package

Download the package

Put it somewhere and use Get (aka <<):

<< "~/Documents/Mathematica/GOAuth.m"

Start the process using

GoogleOAuth[]

Words of warning

• The package does not attempt to deal with any errors that may arise. If it does not work, the best thing you can do is to restart the kernel and try again and make sure you have your credentials absolutely right.
• The time expiration on an access token appears to be, at the time of writing, one hour. If you need to use the API for prolonged periods of time, the best way to keep the session alive is not authorize manually over and over. Rather, you want to use refresh tokens. I didn't implement this, but those who have such needs can easily extend the package!

About the technology behind the curtain

I'd like to share some of the code, which is not related to the GUI, to shine some light on the process. For those who don't want to use the GUI and those who wants to understand how it works.

The first call to acquire the user code looks like this:

data = URLFetch[
   "https://accounts.google.com/o/oauth2/device/code",
   Method -> "POST",
   "Parameters" -> {
     "client_id" -> clientID,
     "scope" -> scope
     }
   ];
{deviceCode, expiresIn, interval, userCode, 
   verificationURL} = {"device_code", "expires_in", "interval", 
    "user_code", "verification_url"} /. ImportString[data, "JSON"];

As you can see, the data is returned as a JSON string. This goes for all Google Data API responses, so it's good to be aware of.

This request will check if we've been authorized:

tokens = URLFetch["https://accounts.google.com/o/oauth2/token",
   Method -> "POST",
   Parameters -> {
     "client_id" -> clientID,
     "client_secret" -> clientSecret,
     "code" -> deviceCode,
     "grant_type" -> "http://oauth.net/grant_type/device/1.0"
     }
   ];
{accessToken, tokenType, accessExpiresIn, idToken, 
   refreshToken} = {"access_token", "token_type", "expires_in", 
    "id_token", "refresh_token"} /. ImportString[tokens, "JSON"];

If we haven't been authorized, the response will contain none of that information. Instead, it will only contain the element "error". The package looks for this to determine whether we've been authorized or not. The package keeps making this request every five seconds, as is currently recommended by Google, until we've been authorized.

Scopes and an example query

This query will obtain information about the user account:

URLFetch["https://www.googleapis.com/oauth2/v1/userinfo?access_token=" <> accessToken]

Whether or not we're allowed to request this information depends on what scope/privileges we requested to begin with. A suitable scope for this request is

https://www.googleapis.com/auth/userinfo.email https://www.googleapis.com/auth/userinfo.profile

I will not delve into the specifics of scopes. I will not attempt to implement the Google Data Spreadsheet API, either. That way, this answer is agnostic as to which Google Data API is to be used. This package and this answer provides the first step of how to authorize with Google Data using Mathematica, for all the rest the official Google documentation should be used.

It has recently, long after I wrote this answer, come to my attention that using the "device flow" as this method does is currently not allowed for all Google APIs. So if it doesn't work for you with the specific API you had in mind, try to search on Google to see if other have also had problem while using the device flow for authorization.

GoogleDataLink from Lauschke Consulting does exactly that. With GDL you can seemlessly retrieve/query, upload, update, filter, etc. (in short: exchange) data between M and Google Spreadsheets. You can exchange data on a cell level ("atomic" expressions in M) as well as entire (rectangular) cell ranges as M 2-dim tables. You can query/filter and retrieve using an SQL-like syntax as well as update cell ranges in an SQL-like manner with a simple function in M.

GDL also contains data exchange functions for Google Calendar. At this time GDL contains the features for Google Spreadsheets and Google Calendar from Google Data, and more from Google Data will be added in the future.

GDL also contains the interactive symbol browser and the interactive expression browser.

Here is the User Guide document. The Google Spreadsheets features are explained here and the Google Calendar features are explained here.

GDL is used at scientific research firms, operations analysis consultancies, and hedge funds internationally due to the ability to combine

• spreadsheet-based tabular data management (data entry as well as results presentation)
• M-based data manipulation/computation
• instant updates in the visible UI (spreadsheet)
• spreadsheet/document sharing among several users

in an interactive, yet decentralized/remote manner. Customers appreciate the ability to collaborate on a document while having a decentralized office structure with multiple locations and having a M consultant work for them anywhere on the planet.

Disclosure: I am the owner of Lauschke Consulting, and GoogleDataLink is a commercial product (not freeware or open source). USD 99, EUR 74.

You can also do this through the ServiceConnect framework. I detailed how to build one of these connections here and how to do it for Google Drive here

To start, here's how you can get the paclet that implements the connection:

PacletInstall["ServiceConnection_GoogleDrive",
 "Site"->"http://www.wolframcloud.com/objects/b3m2a1.paclets/PacletServer"
 ]

The flow is shown in more detail here but we'll do a quick rundown.

You'll need to make a sample app of your own and the connection will ask for its keys. I store them in a key-chain mechanism I wrote for Mathematica. A partial description of how it works is here. If you have BTools on your machine the connection will use the keychain.

First you simply connect in the standard way:

$so = ServiceConnect["GoogleDrive"]

It asks you to authenticate, you click "Sign in to GoogleDrive", it takes you to the standard Google Drive landing page. You click on the account you want to use, authorize it, and it sends you to a local redirect URI to get your access token:

Copy that in to the window:

Once you do that you get your object:

And you can use it in the standard way:

$so["Requests"]

{"Authentication", "CopyFile", "CreatePermissions", "DeleteFile", \
"DeletePermissions", "DownloadFile", "DownloadLink", "EmptyTrash", \
"ExportFile", "FileInfo", "GenerateFileIDs", "ID", "Information", \
"ListFiles", "ListPermissions", "Name", "PermissionsInfo", \
"PublishFile", "RawRequests", "RequestData", "RequestParameters", \
"UpdateFile", "UpdateFileMetadata", "UpdatePermissions", \
"UploadFile", "UploadFileMetadata", "WatchFile"}

I simply implemented most of those requests, I don't know what they all do. But we can look at what parameters they take:

$so["RequestParameters", "Request" -> "UploadFile"]

<|"Parameters" -> {"callback", "key", "access_token", "fields", "key",
    "prettyPrint", "quotaUser", "userIp", "uploadType", 
   "ignoreDefaultVisibility", "keepRevisionForever", "ocrLanguage", 
   "supportsTeamDrives", "useContentAsIndexableText", "id", 
   "appProperties", "contentHints.thumbnail.image", 
   "contentHints.thumbnail.mimeType", "description", "mimeType", 
   "modifiedTime", "name", "parents", "properties", "starred", 
   "viewedByMeTime", "viewersCanCopyContent", "writersCanShare", 
   "MIMEType", "BodyContent"}, 
 "Required" -> {"uploadType", "BodyContent"}|>

Hope this helps