New Google Spreadsheets publish limitation

I am testing the new Google Spreadsheets as there is a new feature I really need: the 200 sheets limit has been lifted (more info here: https://support.google.com/drive/answer/3541068).

However, I can't publish a spreadsheet to CSV like you can in the old version. I go to 'File>Publish to the web' and there is no more options to publish 'all sheets' or certain sheets and you can't specify cell ranges to publish to CSV etc.

This limitation is not mentioned in the published 'Unsupported Features' documentation found at: https://support.google.com/drive/answer/3543688

Is there some other way this gets enabled or has it in fact been left out of the new version?

My use case is: we retrieve Bigquery results into the spreadsheets, we publish the sheets as a CSV automatically using the "publish automatically on update" feature which then produces the CSV URL which gets placed into charting tools that read the CSV URL to generate the visuals.

Does anyone know how to do this?

The new Google spreadsheets use a different URL (just copy your <KEY>):

• New sheet : https://docs.google.com/spreadsheets/d/<KEY>/pubhtml
• CSV file : https://docs.google.com/spreadsheets/d/<KEY>/export?gid=<GUID>&format=csv

The GUID of your spreadsheet relates to the tab number.

/!\ You have to share your document using the Anyone with the link setting.

Here is the solution, just write it like this:

https://docs.google.com/spreadsheets/d/<KEY>/export?format=csv&id=<KEY>

I know it's weird to write the KEY twice, but it works perfectly. A teammate from work discovered this by opening the excel file in Google Docs, then File -> Download as -> Comma separated values. Then, in the downloads section of the browser appears a link to the CSV file, like this: https://docs.google.com/spreadsheets/d/<KEY>/export?format=csv&id=<KEY>&gid=<SOME NUMBER> But it doesn't work in this format, what my friend did was remove "&gid=<SOME NUMBER>" and it worked! Hope it helps everyone.

If you enable "Anyone with the link sharing" for spreadsheet, here is a simple method to get range of cells or columns (or whatever your feel like) export in format of HTML, CSV, XML, JSON via the query:

https://docs.google.com/spreadsheet/tq?key=YOUR-KEY&gid=1&tq=select%20A,%20B&tqx=reqId:1;out:html;%20responseHandler:webQuery

• For tq variable read query language reference.
• For tqx variable read request format reference.

Downside to this is that your doc is still availble in full via the public link, but if you want to export/import data to say Excel this is a perfect way.

It's not going to help everyone, but I've made a PHP script to read the HTML into an array.

I've added converting back to a CSV at the end. Hopefully this will help some people who have access to PHP.

$html_link  = "https://docs.google.com/spreadsheets/d/XXXXXXXXXX/pubhtml";
$local_html = "sheets.html";

$file_contents = file_get_contents($html_link);
file_put_contents($local_html,$file_contents);

$dom        = new DOMDocument();  
$html       = @$dom->loadHTMLFile($local_html);  //Added a @ to hide warnings - you might remove this when testing
$dom->preserveWhiteSpace = false;   


$tables     = $dom->getElementsByTagName('table');   
$rows       = $tables->item(0)->getElementsByTagName('tr'); 
$cols       = $rows->item(0)->getElementsByTagName('td');  //You'll need to edit the (0) to reflect the row that your headers are in.

$row_headers = array();
foreach ($cols as $i => $node) {
    if($i > 0 ) $row_headers[] = $node->textContent;
}  

foreach ($rows as $i => $row){   
    if($i == 0 ) continue;
    $cols = $row->getElementsByTagName('td');   
    $row = array();
    foreach ($cols as $j => $node) {
        $row[$row_headers[$j]] = $node->textContent;
    }   
    $table[] = $row;
} 

//Convert to csv
$csv = "";
foreach($table as $row_index => $row_details){
    $comma      = false;
    foreach($row_details as $value){
        $value_quotes = str_replace('"', '""', $value);
        $csv .= ($comma ? "," : "") . ( strpos($value,",")===false ? $value_quotes : '"'.$value_quotes.'"'  );
        $comma = true;
    }
    $csv .= "\r\n";
}

//Save to a file and/or output 
file_put_contents("result.csv",$csv);
print $csv;

Here is another temporary, non-PHP workaround:

1. Go to an existing NEW google sheet
2. Go to "File -> New -> Spreadsheet"
3. Under "File -> Publish to the web..." now has the option to publish a csv version

I believe this is actually creating an old Google sheet but for my purposes (importing google sheet data from clients or myself into R for statistical analysis) it works until they hopefully update this feature.

I posted this in a Google Groups forum also, please find it here:

https://productforums.google.com/forum/#!topic/docs/An-nZtjaupU