Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Use a Google Spreadsheet as your JSON backend

coderwall.com

Post reply on HN
Print view View original

31–40 of 69 posts

Re: Use a Google Spreadsheet as your JSON backend

#31

Post by MediaSquirrel »

AmericanOP wrote: post #25
nirvanatikku wrote: post #9

..FWIW, for those of you who haven't been enlightened with the power that Google Apps Script[1] offers yet, be sure to check it out: http://script.google.com . Layered on top of Spreadsheets, this pair takes prototyping to a whole new level. [1] https://developers.google.com/apps-script/

I've been hunting for a dev to connect Google spreadsheets with our company's CRM. If someone is interested in a project, email is in my profile. I've gotten very lucky meeting smart people through HN before, so why not try again!

You probably don't need a dev. Try Zapier.com

Re: Use a Google Spreadsheet as your JSON backend

#32

Post by justincormack »

Yes I have done this. Google make it particularly difficult to get the URL of the spreadsheet. And that od6? Thats if you have multiple tabs, they have random identifiers. Its almost impossible to work out what they will be. Its like they are on the web but not of the web.

But it is an easy interface for unskilled users to add data to say a graph on a website, have done that for clients and they have been very happy.

Re: Use a Google Spreadsheet as your JSON backend

#33

Post by dragonwriter »

surreal wrote: post #30
dragonwriter wrote: post #29

Earlier quoted context omitted.

> But I stand by the fact that this seems like hacking an undocumented feature: "Copy the key=... part [...] and put it into this URL: [...]", No, the key value for use in the API being the same that is displayed in the browser URL when working with the sheet is a documented feature: https://developers.google.com/google-apps/spreadsheets/#retr...

I didn't dispute that. The key itself is fine. I'm just worried about using it for JSON retrieval! That part is undocumented as far as I can see, and therefore subject to change without notice

Huh. I thought that the Spreadsheet API was documented as an XML/JSON API, but it looks like only the XML is directly documented so ... poking around a bit more

The alt=json thing is a documented common feature of Google Data APIs, and the current Google Spreadsheets API is the target of the link in the list of Google Data APIs titled "Google Spreadsheets Data API". OTOH, the current v3 Spreadsheets API no longer has "Data" in the name, and there is a note on the Data APIs documentation that some Google Data APIs have been replaced with newer APIs that aren't Google Data APIs.

So, its at best an ambiguously-documented feature.

Re: Use a Google Spreadsheet as your JSON backend

#34

Post by tjohns »

surreal wrote: post #16

Good for prototyping, thanks. I'd be wary of using this in a live system though: relying on Google's public APIs/services is risky enough (Checkout is one example, Reader, etc) let alone an undocumented feature like this which could change/disappear suddenly. Edit: it has been pointed out that the criticism of their documented/public APIs may be unjustified. The issue here is that this particular feature is undocumen…

This feature is documented: https://developers.google.com/gdata/samples/spreadsheet_samp...

In fact, it's a core feature of GData: https://developers.google.com/gdata/docs/json#Request

That's not to say the API will never change, but if it does, it will be documented here: https://developers.google.com/google-apps/spreadsheets/

(Disclaimer: I used to be the Google Developer Relations engineer responsible for Spreadsheets, but that was ~4 years ago.)

Re: Use a Google Spreadsheet as your JSON backend

#35

Post by tjohns »

justincormack wrote: post #32

Yes I have done this. Google make it particularly difficult to get the URL of the spreadsheet. And that od6? Thats if you have multiple tabs, they have random identifiers. Its almost impossible to work out what they will be. Its like they are on the web but not of the web. But it is an easy interface for unskilled users to add data to say a graph on a website, have done that for clients and they have been very happy.

The tab identifiers are actually listed in the Spreadsheets API, inside the "worksheets" feed: https://developers.google.com/google-apps/spreadsheets/#work...

As a historical note, these identifiers used to be part of the URL, long ago. The newer version of the Spreadsheets frontend doesn't use them, but they're still used by the Spreadsheets API.

Re: Use a Google Spreadsheet as your JSON backend

#36

Post by tgasson »

jzwinck wrote: post #18
tgasson wrote: post #13

This is great for prototypes but there's an edge case that breaks it's usefulness in live sites. Sometimes google will make already logged in users reauthenticate. It will redirect to the authentication page and you'll get a bunch of HTML rather than json returned, and the user won't know why it's not working.

The URL has "public" in it; are you sure Google requires authentication at all for this?

Yes I have run into this issue myself. It is a public resource, if you're not logged in at all its fine or if you're recently logged in its fine.

It's the same reauth you sometimes get when visiting a public blogger site. It is an edge case, but quite unpredictable and hard to track down from client reports.

Re: Use a Google Spreadsheet as your JSON backend

#37

Post by rattray »

MediaSquirrel wrote: post #31
AmericanOP wrote: post #25

Earlier quoted context omitted.

I've been hunting for a dev to connect Google spreadsheets with our company's CRM. If someone is interested in a project, email is in my profile. I've gotten very lucky meeting smart people through HN before, so why not try again!

You probably don't need a dev. Try Zapier.com

I've done a number of things with Apps Script exactly because I couldn't do them with Zapier (or IFTTT, for that matter).

Re: Use a Google Spreadsheet as your JSON backend

#38

Post by minikomi »

For what it's worth, sheets also has publish as CSV which is super useful for, say, building d3 graphs (d3 consumes csv like a champ).

I'm using it a lot lately as I have to create static sites with a bunch of different translations. I have the translators edit a set template, which is aggregated into a single sheet. Then, a (racket.. could be python or anything) script reads from the published csv and outputs all the translated pages. Super useful.

Post reply on HN