1

urlread fails to open a spreadsheet that is available for anyone with the link. It opens from a browser, but urlread gives this error:

octave:1> a=urlread('https://docs.google.com/spreadsheets/d/.../edit?usp=sharing'); error: urlread: curl: Peer certificate cannot be authenticated with given CA certificates

is there any way to import a Google spreadsheet into an octave program?

nightcod3r
  • 752
  • 1
  • 7
  • 26
  • Octave on GNU/Linux or Windows? If the later, have you installed the CA certs for curl? – Andy Nov 09 '14 at 18:20
  • It was on Windows (crazy, right?), now I'm back on Linux, but still could not retrieve an array of numbers. – nightcod3r Nov 10 '14 at 16:59

1 Answers1

3

This seems to work, but you need a link to the Google spreadsheet that anyone can view which I assume you have because the end of the URL you posted contains "sharing".

octave:1> urlwrite("https://docs.google.com/spreadsheets/d/.../export?format=csv", "filename.csv")
octave:2> a = load("filename.csv")
a =

    1   23    4    5
    1    6    3    7
    1    6    6    7

Replace ... with the ID of your Google spreadsheet.

The key component here is /d/.../export?format=csv which specifies the download of the file as a csv. Google spreadsheets also supports several different filetypes if you don't want to use csv.

ngtvspc
  • 76
  • 5
  • Naphtha, thanks, this read the file, although what it wrote was a html file, instead of a csv format, that load could not read. Can you figure out what's wrong here? Also, is there any way to update a value in a Google spreadsheet from octave? I mean, like using .setValue() under the Javascript API. – nightcod3r Nov 08 '14 at 07:02
  • It would appear that you can use the [Google Sheets API](https://developers.google.com/google-apps/spreadsheets/#updating_a_list_row) in order to update spreadsheets. However that seems like it could be more trouble than it's worth depending on how badly you need to be able to edit the sheets using Octave. As for your issue with urlwrite() returning an html file. That can happen if you don't set the sharing of the document so that "anyone with the link can view" it. Just make sure to use the "Share" button in the top right hand corner of the Google Sheet and click "Get Sharable Link" – ngtvspc Nov 09 '14 at 07:51
  • 1
    It seems that others have been commented on refined versions of this: [New Google Spreadsheets publish limitation](http://stackoverflow.com/a/23702001/4179803). @Naphtha – nightcod3r Nov 10 '14 at 04:36
  • Now it retrieves the right csv file. Thanks! @Andy – nightcod3r Nov 10 '14 at 17:00
  • The thing is that it's always fetching the first sheet in the spreadsheet. Does Goole provide any way to share only a sheet, or to download individual or all the sheets? – nightcod3r Nov 10 '14 at 17:24
  • 1
    Well, while sheet=NAME does not raise an error, it always returns the first sheet. But gid=NUMBER **DOES** work, only that the sequence is not 0,1,2..., as anyone with experience in Google Apps might think, instead there's a concrete number for each sheet. If you look at the URL in the browser when you're displaying the sheet, at the end there's a #gid=NUMBER. That's the one! – nightcod3r Nov 10 '14 at 18:39