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

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.

share|improve this question
1  
For one you can append &output=csv to the URL and then throw a CSV parser against it. Yes, it is definitely possible. –  phg Sep 4 '13 at 9:14
    
@phg Good to know. But I still need some help to code it ;) –  cjorssen Sep 4 '13 at 9:21
    
datatool or pgfplotstable can parse directly the csv output if you can manage to point to the online source. –  percusse Sep 4 '13 at 14:14

2 Answers 2

up vote 8 down vote accepted
+50

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:

Demo image

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.

share|improve this answer
    
Wow. What a pity I need to way until monday to test it at work. Thanks a lot. –  cjorssen Sep 6 '13 at 20:27

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

enter image description here

share|improve this answer
    
Very interesting answer. I wasn’t aware of Context using curl for retrieving files on secure hosts. Looks like it also inherits the troubles curl has with Google redirects, though (you absolutely need to get the cookies right). Btw. wget fetches the files easily and I modeled my redirection handler after it. –  phg Sep 7 '13 at 9:25
    
I think that now context fetches https directly. The blog post is a few years old. –  Aditya Sep 7 '13 at 13:20
    
What do you mean by directly? It cannot be done with Luatex alone since there is no SSL support in Luasocket. According to data-sch.lua only the http scheme is handled by that library. For https URIs Context falls back to external curl. –  phg Sep 7 '13 at 15:39
    
Sorry, I thought that at some stage ConTeXt had moved from using curl to handle https instead of using a luasocket library. I did not know that luasocket does not provide support for https. –  Aditya Sep 7 '13 at 15:46

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.