1

This used to work:

curl 'https://docs.google.com/spreadsheets/d/1xaGu5Cd0kRPKy0v-EaVxgfnUxaxWlgDhfhfgRQy6hrc/gviz/tq?tqx=out:csv&sheet=Inv_Summary&range=A:G'

The segment tqx=out:csv specified the format, sheet specfied the tab name, and range the columns to bring out.

Now if I do this, I get:

<HTML>
<HEAD>
<TITLE>Moved Temporarily</TITLE>
</HEAD>
<BODY BGCOLOR="#FFFFFF" TEXT="#000000">
<H1>Moved Temporarily</H1>
The document has moved <A HREF="https://accounts.google.com/ServiceLogin? service=wise&amp;passive=1209600&amp;continue=https://docs.google.com/spreadsheets/d/1_fZoLgNGB3fXZqgzCr1gtDnJ2Gowi4dFgovmjsSPHy8/gviz/tq?tqx%3Dout:csv%26sheet%3DInv_Summary%26range%3DA:G&amp;followup=https://docs.google.com/spreadsheets/d/1_fZoLgNGB3fXZqgzCr1gtDnJ2Gowi4dFgovmjsSPHy8/gviz/tq?tqx%3Dout:csv%26sheet%3DInv_Summary%26range%3DA:G&amp;ltmpl=sheets">here</A>.
</BODY>
</HTML>

That long response, broken to make it more easily parsible by people

<A HREF="https://accounts.google.com/ServiceLogin? 
service=wise&amp;passive=1209600&amp;

continue= 
https://docs.google.com/spreadsheets/d/1_fZoLgNGB3fXZqgzCr1gtDnJ2Gowi4dFgovmjsSPHy8/
gviz/tqtqx%3Dout:csv%26sheet%3DInv_Summary%26range%3DA:G&amp;

followup=
https://docs.google.com/spreadsheets/d/1_fZoLgNGB3fXZqgzCr1gtDnJ2Gowi4dFgovmjsSPHy8/
gviz/tq?tqx%3Dout:csv%26sheet%3DInv_Summary%26range%3DA:G&amp;
ltmpl=sheets">here</A>

You'll note that the followup URL is identical to the temp moved URL.

Now if I paste the original link after the curl statement into the address line in chrome, I get an immediate open a file to download, and it does the right thing.

Why isn't curl working to do this?

How do I get the old behaviour back?

Background: I keep a google sheet that tracks my tree farm inventory. The inventory is summarized on a pivot table. A script scrapes the pivot table data, and uses it to generate HTML pages for my website.

Sherwood Botsford
  • 1,889
  • 5
  • 20
  • 35

2 Answers2

2

Gotta love informative messages.

  • The spreadsheet in question was not publically readable.
  • When google checked the file, curl did not pass an OAuth token. Instead of saying something clever like, "This app did not present a valid token" it instead refers me to the web page version.
  • Since I was already authorized in the browser, the here link worked.

to solve this problem you can do one of two things:

A: Make the file publically readable -- anyone with the link can read it. Since this is basically just book keeping stuff in inventory control, I don't care who reads it.

B: Jump through the hoops to get OAuth working with Curl. A good starting point for that is here:

https://www.daimto.com/how-to-get-a-google-access-token-with-curl/

It's been answered before on SO

get google Oauth2 access token using ONLY curl

Sherwood Botsford
  • 1,889
  • 5
  • 20
  • 35
  • More proper thing to do compared to using wget. +1 – NightEye Jan 21 '22 at 23:50
  • At the risk of starting a conversation in comments, why would curl be "more proper" than wget? – Sherwood Botsford Jan 23 '22 at 16:57
  • more proper for your case as you would still have to retain and modify just a little on your existing code. That's what I believe thus I called it more proper than using wget that basically changes what you have. It's a personal thing. :D – NightEye Jan 23 '22 at 17:23
1

I remember an issue pertaining to output csv having a redirect for curl commands, can you try a wget with the same url? The issue comes and goes so I'll recommend a wget if it's acceptable.

Sample:

wget -O test.csv --no-check-certificate "https://docs.google.com/spreadsheets/d/1xaGu5Cd0kRPKy0v-EaVxgfnUxaxWlgDhfhfgRQy6hrc/gviz/tq?tqx=out:csv&sheet=Inv_Summary&range=A:G"

Output:

enter image description here

enter image description here

NightEye
  • 10,634
  • 2
  • 5
  • 24
  • Didn't make a difference. Are you doing this on the command line with curl? – Sherwood Botsford Jan 21 '22 at 23:18
  • @SherwoodBotsford, im trying it on an online curl emulator as im on the phone right now and asking a friend to provide a screenshot difference. If it doesn't work, I recall output csv is having a redirect issue in curl in the past and the way to overcome the issue is to use `wget` instead. Can you try `wget`? – NightEye Jan 21 '22 at 23:23
  • Included actual command and output for `wget` command @SherwoodBotsford – NightEye Jan 21 '22 at 23:37
  • Problem turned out to be permissions. See my answer below. Giving "anyone with link can view" permission returned the previous behaviour. – Sherwood Botsford Jan 21 '22 at 23:47
  • Hmm. Haven't encountered this one but it seems the proper approach to do.. Glad you were able to find it @SherwoodBotsford – NightEye Jan 21 '22 at 23:49