Get cell content from a Google doc spreadsheet using LuaTeX

Question

I know that LuaTeX can access network resource (at least ConTeXt can). Would it be possible to get cell values in a macro, from a Google Docs spreadsheet, dynamically at compilation time, with LuaLaTeX?

I created a document sample with hopefully no authentication issue.

I found this project that could help but it does not seem to be really active.

Answer 1

If you insist ... here you go.

Prerequisites

Google offers its services over HTTPS only. This is an overall good thing to be sure, however it increases the necessary effort considerably since Luatex does not come with SSL support builtin. Thus we have to add an external dependency. For my solution I choose the Luasec library because it integrates nicely with Lua 5.2. Luasec is a wrapper for OpenSSL, so you will need that one too. (It’s ubiquitous, so it probably comes with your OS.)

Working with Google

The real trouble begins when we attempt to retrieve the CSV version of the spreadsheet. At first glance this seems like an easy task: call ssl.https.request() on the URL and be done with it. Not so with Google: we have to hop through multiple redirects before their servers even let us come near the data. Which is very unfortunate, considering that Luasec does not handle redirects. Thus we have to create the requests ourselves in order to reach the final URI. For this reason roughly half of the code deals with HTTP. If you want to extend the implementation you can skip those parts entirely.

Processing the Data

Once we have the spreadsheet as CSV things are getting much simpler. We extract the content with the generic CSV parser that comes with the lualibs and format it into LaTeX markup.

The function urihandler() is exported into the the namespace packagedata.spreadsheet. User-level macros are wrappers around that:

\documentclass {scrartcl}

\usepackage         {luatexbase}
\RequireLuaModule   {lualibs}
\usepackage         {luaotfload} %% recommended, in that order!
\RequireLuaModule   {spreadsheet}

\makeatletter

\protected \def \googlespreadsheet {%
  \@ifnextchar[\googlespreadsheetopt
               {\googlespreadsheetopt[]}%
}

\def \googlespreadsheetopt [#1]#2{%
  \edef \currentspreadsheetoptions {#1}%
  \directlua {
    packagedata.spreadsheet.urihandler ([[\currentspreadsheetoptions]],
                                        [[\detokenize {#2}]])
  }%
}

\makeatother

\begin {document}

  \input knuth

  \begin {table} [h]
    \googlespreadsheet [center,dump]
      {https://docs.google.com/spreadsheet/ccc?key=0Amykmqr4Of-MdEVIUUcyYld3WTJhZnJHRkgwSF9CaUE&usp=sharing}
  \end {table}

  \input knuth

\end {document}

Result:

Options

The example defines a macro \googlespreadsheet which takes the the URI of the spreadsheet as a mandatory argument. The optional first argument is a comma-delimited list of options. The following items are supported:

• force: re-download the dataset, bypassing the cache (handy for when the spreadsheet changes),
• center, left, right: alignment of all columns, default: left;
• dump: write the generated Latex code to the terminal (useful for debugging).

Thus,

\googlespreadsheet [center] {https://example.com/csv}

will result in all cells being centered; and

\googlespreadsheet [dump,force] {https://example.com/csv}

will trigger an update of the data and display the markup on stdout.

Dependency on Luaotfload

Note that it is advisable to load spreadsheet.lua after luaotfload (or fontspec). It will work without loading luaotfload but the package itself must be present in the texmf so we can access the caching routines.

Final Remarks

Since I don’t use Google Docs, the code is tested only with your example link. While it appears to work, the HTTP part is a kludge and might break any minute due to a change on Google’s side. The problem could be mitigated by using a different SSl library with builting redirect handling. However, I have sampled several of the known crypto libaries for Lua and except for Luasec they either do not work with version 5.2 or lack the necessary functionality (TLS).

Another possibility might be using the spreadsheets API. While this approach promises a long-term reliable solution, frankly I am just too lazy to familiarize myself with yet another bunch of API methods considering that the CSV dump is sufficient.

Answer 2

One of the good things about ConTeXt is that all commands that accept filenames also work with remote file (see this blog post that gives some details). However, this does not work with the HTTP redirect that Google uses. If you use any other service that directly serves the file, then no extra code is needed. The example below uses github to serve the file:

\usemodule[database]

\defineseparatedlist[CSV]
  [separator=comma,
   before=\bTABLE, after=\eTABLE,
   first=\bTR, last=\eTR,
   left=\bTD, right=\eTD]

\starttext

Raw file \crlf
\typefile{https://gist.github.com/adityam/15b673e8a73836018fba/raw/1e2b54e139c8871f05867db1c2ffd1729e3e4129/test.csv}

Parsed file \crlf
\processdatabasefile[CSV][https://gist.github.com/adityam/15b673e8a73836018fba/raw/1e2b54e139c8871f05867db1c2ffd1729e3e4129/test.csv]

\stoptext

gives