Google spreadsheet gviz query is concatenating first two rows into header

Question

I'm trying to get my Google spreadsheet to export correctly to CSV or HTML, selecting columns A and D. However, when I do this, it's concatenating the first rows into a single header. Here is the code:

https://docs.google.com/spreadsheets/d/1aPYE6_umcG5I_3saNdkoBdPTilWtJOBJuzZDhOuZB0o/gviz/tq?tq=select%20A%2CD&tqx=out:html

It's not giving me what I want.

The first row should read, "Date" and "Cumulative Return".

The second row should read "date" and "number".

Then the following rows should be numbers.

Instead, it's concatenating the first two rows into a single header.

I need it in a particular format to import into Visualizer, a Wordpress plugin that does charts.

It's correct if I export the entire public sheet as html or csv, like with the following:

https://docs.google.com/spreadsheets/d/1aPYE6_umcG5I_3saNdkoBdPTilWtJOBJuzZDhOuZB0o/pubhtml

But I need to select only columns A and D and export it correctly to CSV as that is what the Visualizer plugin requires.

How do I get it to do the correct output?

Answer 1

I've run into the same problem with the gviz-export links https://docs.google.com/spreadsheets/d/{key}/gviz/tq?tqx=out:csv&sheet={sheet_name}

Not only do they seem to concatenate empty rows, but it also removes some cell content (for example in my case "RGB(1,0,0)" format string were are removed).

Fortunately I found there's a simpler alternative URL format: https://docs.google.com/spreadsheets/d/{key}/export?format=csv

It doesn't give you all the flexibility of the gviz api, but it seems to return a csv with all the rows and cells unmodified.

If anyone out there knows how to get a similar unmodified output with the gviz api that would be great to hear about, but I haven't been able to that it anywhere.