Add docs on how to pull data into Google Docs

[chrismetcalf]

• Example (see A1 cell): https://docs.google.com/a/socrata.com/spreadsheets/d/1afaaV-T6kzmoGbqVtWCqSQiIkIWlHKp0n9SkS6Kz17I/edit#gid=0
• Function used: =ImportData("https://soda.demo.socrata.com/api/views/4tka-6guv/rows.csv?accessType=DOWNLOAD")
• Docs: https://support.google.com/docs/answer/3093335?hl=en

[marks]

seems as easy as =ImportData("https://soda.demo.socrata.com/api/views/4tka-6guv/rows.csv?accessType=DOWNLOAD" -- should I try to write up a blog post with narrative and animated GIF?

[adamajm]

Is there a workaround for private datasets?

[marks]

@adamajm you should be able to pass your username and password in the URL in HTTP Basic Auth fashion

Example here: http://stackoverflow.com/questions/2716990/http-basic-authentication-credentials-passed-in-url-and-encryption

[gmichaud]

I tried using HTTP Basic auth using https://user:[email protected]/path/ without luck - is it supported by Google Docs?

[marks]

@gmichaud @adamajm it looks like Google Sheets' importData(...) does not support HTTP Basic Auth. Luckily, others have needed this and documented how to do it at https://www.redfin.com/devblog/2012/04/when_importdata_isnt_good_enough_retrieving_csv_files_behind_basic_auth_with_a_google_apps_script.html

LMK if you have any questions!

[chrismetcalf]

I think @blockspring might support authenticated access, and it works very well with Google Docs. Let me see if I can get them to jump in and comment

[pkpp1233]

Yup -- all access to Socrata from Google Sheets is using an API token. And you don't need to store that token in the sheet either. Here's a quick video showing how: https://www.youtube.com/watch?v=4zRkNwDpdww.

We plug into Tableau too so you can use the same method (and UI) there: https://www.youtube.com/watch?v=2b1825yyfLY.

[chrismetcalf]

@pkpp1233 Our app tokens just identify the application, they don't actually provide any authentication. Is there a way to also pass HTTP Basic or OAuth 2.0 credentials along? https://dev.socrata.com/docs/authentication.html

[pkpp1233]

@chrismetcalf Awesome. We didn't know ya'll had OAuth2. Far easier for users than getting a token (and sounds like more secure). Will update.

[chrismetcalf]

@pkpp1233 I'll ping you on Slack and lets chat about how to make that work

[pkpp1233]

@chrismetcalf - we're seeing this error almost every other time we test authenticating with OAuth2:

{
 "code" : "invalid_request",
 "error" : true,
 "message" : "Authorization code invalid"
}

[Nickintosh]

Is there any update on this? When I attempt to use Blockspring, I'm still getting invalid token or dataset not available when I attempt to access private data sets.