3

I am trying to import data into an Excel spreadsheet from a published Google sheet. I have published the Google sheet and copied the URL.

When I try and use that URL in my Excel spreadsheet, either with VB code or by standard Data Get External data From Web it asks me to log-in to Google. How can I get the data without having to log-in.

Community
  • 1
  • 1
Mike Eburne
  • 351
  • 2
  • 7
  • 21
  • 3
    What are the privacy settings on your Google Sheet? Have you set it to be publicly accessible from the web? – danmullen Jan 31 '14 at 13:55
  • Second @danmullen's opinion. Is it public? If possible, how does your code access it? Via the public "shared" link, or maybe the link you provided is from the "inside" while you were logged in? – WGS Jan 31 '14 at 14:31
  • The privacy settings are "Anyone with the link can view". Should not require login. – Mike Eburne Feb 03 '14 at 15:19
  • Second question: This is exactly what I do: – Mike Eburne Feb 03 '14 at 15:20
  • Opps sorry pressed CR too early... this is what I do... 1) Set shared permisssion to 'Anyone with the link can view"... 2) Go into the Google Sheet and publish it... 3) Copy the published URL (from the box at the bottom) and use this in my VB code or, for that matter, use it with standard Excel get external data from web function. – Mike Eburne Feb 03 '14 at 15:23

2 Answers2

5

You can select in Google Sheets to publish as a CSV file

Then in excel use Data -> import FROM TEXT (not from web)

Paste in the link to the google sheets csv file

This should read and recognise the file, you will need to configure headers, and the Comma as the separator, format etc

Then finally one it has been imported if you go to Data -> connections you can change refresh times etc

Tony
  • 51
  • 1
  • 2
  • I didn't get this at first, but it's a brilliant solution! Get a CSV download link like https://docs.google.com/spreadsheets/d/GSHEET_ID/export?format=csv&id=GSHEET_ID&gid=12341234 , then put it as a file name. I had to turn off "prompt for file name on refresh" and fix up the name after importing. And it [won't handle newlines](https://stackoverflow.com/q/2668678/1026), but it's still useful. – Nickolay Mar 15 '19 at 17:12
0

If you are willing to use an add-in, you can have Excel and Google Sheets stay in synch.

Data Everywhere makes add-ins for Google Sheets and Excel that allows you to sync data between Google Sheets and Excel. You just highlight the data you want to synchronize, and both your Excel spreadsheets and your Google Sheets spreadsheets will always be in sync.

You can get it at Data Everywhere, or from the Google Sheets Add-in store at https://chrome.google.com/webstore/detail/data-everywhere/foenaaepondggfpfonagpmdaggmpdeel?hl=en-US

Unfortunately, this add-on no longer works.

Maharkus
  • 2,841
  • 21
  • 35
Andy Pruitt
  • 1