3

Can excel lookup values from a spreadsheet in google docs? I would like my current excel forms to be able to lookup data from a spread sheet that used to be held on our server but now is a google docs spreadsheet. I have office 07. Thanks for any help.

Troy
  • 31
  • 1
  • 1
  • 2
  • 2
    It can be done provided the Google spreadsheet is shared to the web, see this website for the method, http://ramblings.mcpher.com/Home/excelquirks/get-data-from-google-docs. The author describes how to get the Google spreadsheet to return a text string containing cell contents by using an Excel macro. Parsing the returned string would get the cell contents and looks to be the hard part. I don't know if this could be made to work on a private Google docs spreadsheet. – W_Whalley Mar 23 '12 at 17:09

3 Answers3

2

The referenced method on ramblings.mcpher.com/Home/excelquirks/get-data-from-google-docs will work on any google spreadsheet you have access to. The decoding of the returned Google Wire formatted response is all taken care of by the Excel-jSon deserializer.

user400514
  • 121
  • 1
0

You should be able to do this using Excel's web queries.

user3618431
  • 113
  • 5
Lèse majesté
  • 3,239
  • 1
  • 19
  • 23
0

You don't need to share with the web, If you have google drive then it can be private and still accessed.

  1. Save your file to the google drive (this means a link is available on your local hard drive)
  2. When you click on the file in the local drive it will open using a browser with a link like https://docs.google.com/spreadsheet/blah blah blah
  3. In excel use [data]-[from the web] and the link above

There are limitations:

  • it brings in some guff around it but if you import to one sheet and have the second with only the data it should be ok
  • It doesn't seem to work with more than 100 records, it gives a response basically saying the record is too big for the browser
Andrea
  • 1,516
  • 4
  • 17
  • 19
David Howard
  • 1