12644_en-US

liliconcom

Import data from Google Sheets<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />

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

Re: Import data from Google Sheets

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

Re: Import data from Google Sheets

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

Re: Import data from Google Sheets

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

Re: Import data from Google Sheets

Jeff,

I ran the internet open and it returned the table using the Mac OS host and Safari.

Could GoogleDocs behave differently with the different flavors of the browsers?

Stan

Open(

  "https://docs.google.com/spreadsheets/d/1E6qJUbDmetPderx0Lsz9l2FL-lzNBEFwBnvss33ftXo/edit?usp=sharing",

  HTML Table( 2 )

);

Re: Import data from Google Sheets

Hi Olivia,

I spent some time looking at this and I don't see any simple, obvious way to import the sheet without downloading the CSV file. The page isn't a normal HTML page. It's all wrapped up in Javascript and stuff, so JMP doesn't see any HTML tables to import.

Here's the URL of one of my Google sheets that I was using to experiment with:

https://docs.google.com/spreadsheets/d/1E6qJUbDmetPderx0Lsz9l2FL-lzNBEFwBnvss33ftXo/edit?usp=sharing

Craige@JMP may have some ideas about whether it would be possible to use either of the APIs that Google provide to access it through JSL, but I imagine that's a daunting task.

-Jeff