Import data from Google Sheets

Question

Hi All,

A current JMP customer who is an ecologist is interested in downloading data stored in Google Sheets directly into JMP using Internet Open so they can bypass the step of downloading the data as csv and importing into JMP.  They are starting to collect data in the field using their phones with Google Forms which creates a Google Sheet. So far, I have found that the web address with the sheet gives an error when importing into JMP.  If you publish the sheet to the web, the web address works with internet open but as you update the sheet, the new data is not brought into JMP with that web link.

Does anyone have any experience working with Google Sheets or downloading data into JMP from Google Sheets?

Thanks!

Olivia

O_Lippincott · Answer

That works perfectly Craige! Thanks for your help Craige, Stan and Jeff!

Using Jeff's sample table this internet open now works on windows. The same link will work if you update the google sheet.

open("https://docs.google.com/spreadsheets/d/1K6rw3Qnehq_u6nmy4YfDrI1xiC5VtqsPZz1Rl3Oyi2o/export?format=csv");

What you need to change as outlined in the link Craige sent:

- Change sharing from private to those that have the link

-Publish to the web with automatic updates

-change the last part of the link from /pubhtml to /export?form at=csv

Craige_Hales · Answer

hey, try this (i have not tried it)

from CSV Auto Fetch using Google Drive Spreadsheet – Customer Feedback for AfterShip

. Make sure the option  Automatically republish when changes are made is checked

Jeff_Perkinson · Answer

Clearly, the best way to spur anyone into action is to say it can't be done.

The link I posted above is now working for me as well on the Mac. Rather than browsers, I think it may be OSs. I can't get it work on Windows.

I get an error dialog:

and this in the Log:

Caution: redirected to https://www.google.com/url?sa=p&q=https://www.google.com/accounts/ServiceLogin?service%3Dwise%26passive%3Dtrue%26go%3Dtrue%26continue%3Dhttps://docs.google.com/spreadsheets/d/1E6qJUbDmetPderx0Lsz9l2FL-lzNBEFwBnvss33ftXo/edit?usp%253Dsharing%2526pref%253D2%2526pli%253D1

Having now said that it can't be done on Windows, I expect someone to prove me wrong...again.

Maybe BrianCorcoran can point us in a good direction.

-Jeff