NOTE: workaround is included. I thought it should be documented for the next person.

Background:

I have a pretty simple table in a Google Sheets document.

I am exporting the contents of the sheet via the download url: https://docs.google.com/spreadsheets/d/{documentId}/gviz/tq?tqx=out:csv

Here is a copy of the raw text bytes downloaded from that url:

"id","start#0.key","start#0.value","start#1.key","start#1.value"
"Alpha","HelpLookup","2002","TitleLookup",""
"Beta","Colour","","Background","2003"

Issue:

Notice:

• the 'start#1.value' cell for the second row (Alpha) is an empty string, not 'H.D.1'.
• the 'start#0.value' cell for the third row (Beta) is an empty string, not 'Red'.

It appears that because the 'Beta' value for this cell is a Number and the Formatting for the column is 'Automatic' any string values are being removed during the export.

Resolution (not ideal)

When I change the columns' Format to "Number | Plain Text" I am able to get all the data from the CSV export.

Here is the url-downloaded data:

"id","start#0.key","start#0.value","start#1.key","start#1.value"
"Alpha","HelpLookup","2002","TitleLookup","H.D.1"
"Beta","Colour","Red","Background","2003"

No other changes were made to the Google Sheets document - only changing the format of the 'start#1.value' column to "Number - Plain text".

Expectation:

Seeing as I am explicitly asking for 'csv' output, see the 'tqx=out:csv' in the url's query string parameters, that I would get all the text from my spreadsheet.

CodePudding user response：

I had the same experience as you. In that case, I could retrieve the correct CSV data by changing the endpoint.

From the situation that you can export the Spreadsheet as the CSV using https://docs.google.com/spreadsheets/d/{documentId}/gviz/tq?tqx=out:csv, I thought that your Spreadsheet is publicly shared. If my understanding is correct, how about changing the endpoint as follows?

From:

https://docs.google.com/spreadsheets/d/{documentId}/gviz/tq?tqx=out:csv

To:

https://docs.google.com/spreadsheets/d/{spreadsheetId}/export?format=csv

Or, if you want to retrieve the specific sheet, please use gid as follows.

https://docs.google.com/spreadsheets/d/{spreadsheetId}/export?format=csv&gid={sheetId}

CodePudding user response：

Thanks @Tanaike. Now I can go an see if the rest of the code can "deal with" the missing "'s :)

The format of the export is slightly different that the 'gviz' version. It doesn't have the "'s around all the fields. I was curious about 'spaces' or 'commas' in the fields so I did a small test.

id,start#0.key,start#0.value,start#1.key,start#1.value
Alpha,HelpLookup,2002,"Title,Lookup",H.D.1
Beta,Colour,Red,Back ground,2003

I have changed 'TitleLookup' string, for Alpha, to include a comma. Then the 'Background' key, for Beta, to include a space to see what happens.