Take the 2-minute tour ×
Mathematica Stack Exchange is a question and answer site for users of Mathematica. It's 100% free, no registration required.

Context

I'm trying to setup a connection between Mathematica and Google Docs. Specifically, I would like to be able to export data to a Google Spreadsheet (Note: if the spreadsheet is public it's very easy to import data to Mathematica, so the problem here is only how to export data to a Google Spreadsheet).

I have little knowledge of Java, but as far as I could read in many posts and documentations (for instance, here, here, here, here, here, here), there are several Java commands and classes involved in the process. I believe there are basically two steps involved in this problem:

1) Get the OAuth authentication from Google (you can get familiar with OAuth here);

2) Export to Google Spreadsheet using GoogleData libraries (found here).

I know there is a commercial package doing exactly that (link), but I would like to be able to control everything from Mathematica.

Attempt

As said, I have little knowledge of Java. So this is not a serious attempt to solve this problem, but only a concatenation of ideas (hoping someone could actually help me in the process).

I believe the first step to achieve the Mathematica-Google Docs connection is to modify the Google Account security definitions (admin.google.com/AdminHome) to allow API access and also generate an OAuth-key.

So far, so good. From now on, I'm completely lost. I believe the next step would be:

1) Create the OAuth token using the OAuth-key from Google. Something like this:

token = HTTPClient`OAuthAuthentication[
"ConsumerKey" -> "mywebaddress.com", 
"ConsumerSecret" -> "OAuth-key from Google", 
"RequestEndpoint" -> "https://accounts.google.com/OAuthGetRequestToken",
"AuthorizeEndpoint" -> "https://accounts.google.com/o/oauth2/auth", 
"AccessEndpoint" -> "https://accounts.google.com/o/oauth2/token"];

Do I need anything else inside the token?)

2) URLFetch the data with the correct methods

A list of methods to manipulate a Google spreadsheet can be found here. However, I'm not able to put them in the correct syntax inside Mathematica. Something like this:

url="https://spreadsheets.google.com/feeds/cells/{spreadsheet_key}/{active_sheet}/
  private/full/{active_cell}/sheet.setColumnWidth(1, 200)"
URLFetch[url,"OAuthAuthentication" -> token]

So, when I try to use the code above I get the following error messages:

HTTPClientOAuthSignURL::nopt: The option OAuthAuthentication is missing in HTTPClientOAuthPrivateOAuthURLFetchList[https://spreadsheets.google.com/feeds/cells/{sheet_key}/{sheet_sheet}/private/full/{sheet_cell}/sheet.setColumnWidth(1, 200),{CredentialsProvider->None,<<1>>,<<1>>,OAuthAuthentication-><<30>>[<<30>>[<<30>>[<<30>>[<<1>>]]]]}]. General::stop: Further output of HTTPClient`OAuthSignURL::nopt will be suppressed during this calculation.

I'm clearly doing lots of wrong things, however I believe someone in MMA.SE could help me to achieve this connection between Mathematica and Google Docs.

share|improve this question
2  
Nice bounty. I'm curious to see how to implement it too. It has a lot of nice corporate applications. –  Murta Aug 30 '13 at 2:19
    
My answer focus on authorization. To manipulate spreadsheets, use URLFetch, the key my package gives you and the URLs that you can find on this page if you select "protocol" for the samples: developers.google.com/google-apps/spreadsheets –  Pickett Aug 30 '13 at 10:25
    
Actually, your question indicates many misunderstandings. In the beginning you link to the Java SDK for example. If you want to do this with Mathematica exclusively, must use the URLs, not some-other-language-SDK. And you cannot put Google App scripts inside the URLs as in your example later on, you have to use the URLs as they are formatted on the page I gave a link to. –  Pickett Aug 30 '13 at 10:35
    
A quick and dirty hack is to do it with a command line tool that is than called from MMA. GoogleCL is an example. google docs upload my_contacts.csv # Upload a CSV file, automatically converted to a spreadsheet. –  Ajasja Aug 30 '13 at 11:24
    
@Anon +1 It's simply amazing what you've done! The authentication process works perfectly! However, before I accept your answer, I have two other questions: 1) Is it possible to avoid the "manual" part of the process (through the browser)? I mean, I believe Google must have a way to allow this through the device itself... maybe an URLFetch with a POST method with the generated user code. What do you think? 2) Once authorized by Google, how would be the syntax to send a POST method through URLFetch? –  Rod Aug 30 '13 at 11:30
show 1 more comment

2 Answers

up vote 15 down vote accepted
+250

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:

screenshot

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.