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.


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:

  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.

  • 2
    $\begingroup$ Nice bounty. I'm curious to see how to implement it too. It has a lot of nice corporate applications. $\endgroup$
    – Murta
    Commented Aug 30, 2013 at 2:19
  • $\begingroup$ 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 $\endgroup$
    – C. E.
    Commented Aug 30, 2013 at 10:25
  • $\begingroup$ 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. $\endgroup$
    – C. E.
    Commented Aug 30, 2013 at 10:35
  • $\begingroup$ 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. $\endgroup$
    – Ajasja
    Commented Aug 30, 2013 at 11:24
  • 1
    $\begingroup$ spreadsheets is a brand new R package that does a lot of cool integration between R and Google SpreadSheet. The code is open, so, maybe some ideas can be implemented in Mathematica. You need to generate the authorisation only once, then it is cached in R. $\endgroup$
    – Murta
    Commented Jun 27, 2015 at 20:15

3 Answers 3



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.


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:



Installing the package

Download the package

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

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

Start the process using


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[
   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.

  • $\begingroup$ “From what I can tell, it's not possible to authorize with Google Data without involving the browser at some point.” — It is possible with Service Accounts. developers.google.com/accounts/docs/OAuth2ServiceAccount I actually have a somewhat clean MMA interface for that (with manual key signing) but it's not clean enough to be released. $\endgroup$
    – akater
    Commented Feb 10, 2015 at 5:58
  • $\begingroup$ @Akater I realized that after a while but I remember that I couldn't get the RSA SHA-256 algorithm to work in Mathematica, how did you do that? If you can help me with it, perhaps I can do the rest. I've been thinking about providing a solution using JLink just like Andreas does in his package. $\endgroup$
    – C. E.
    Commented Feb 10, 2015 at 6:35
  • $\begingroup$ External call to openssl. $\endgroup$
    – akater
    Commented Feb 10, 2015 at 6:37
  • $\begingroup$ However, I also had to tinker with the PEM key that Google issued — again, with openssl. IIRC, I reexported it with this call: openssl.org/docs/apps/pkcs12.html Seems like it was necessary, maybe what Google sends to you is not classified as PEM at all; I can't tell. $\endgroup$
    – akater
    Commented Feb 10, 2015 at 6:47
  • $\begingroup$ Hope I didn't miss anything: pastebin.com/gxr0tcPp (expires in a week) $\endgroup$
    – akater
    Commented Feb 10, 2015 at 6:59

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:


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:

google drive token

Copy that in to the window:

google drive window

Once you do that you get your object:


And you can use it in the standard way:


{"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


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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