Fetch data in MS Excel from Google Sheet

Question:

( by Chris Gob Shite )

I have a google form which is capturing data that is saved in a Google Sheet

I want to merge that data into an official form in MS Word

MS Word will only merge with an Excel sheet and not a Google Sheet

Currently just copy/pasting everytime there is a new entry

Just wondering

Any Sheets gurus know how to Create a dynamic link from an Excel Sheet to a data in a Google Sheet?

Solution:

Following are steps that will allow you to get the data from Google Sheets to MS Excel:

In your Google Sheets, click "File" > "Publish to the web...".

And then select the sheet "Form Responses 1" (from which you want to fetch the data), and then click "Publish".

When you click Publish, you will get the URL of your published Google Sheet, Copy it... We will use this link in MS Excel.

Open Excel 2007 (I am not sure whether this works in earlier versions). Click the "DATA" Tab on the top. There is a "Get External Data" section, click "from web"...

Paste that URL Link in the address bar of the dialog box that opened when you clicked get data from the web.

If you receive any error message like "So you want to continue running scripts on this page".. then Click "Yes".

Click the check box (in yellow color) that appears in the upper left of your website (in the Excel Dialog Box)... and click Import.

It may ask "Where do you want to put the data?", Input whatever option suits you.

And note: In MS Excel, if you need to refresh the data in every minute or every 30 minutes then you can set the time duration:

Click "Properties" in Data tab, and then Click on the check box "Refresh every" and set the time. Also click "Refresh data when opening the file", if you need it.

You are done...!!

Enjoy auto Importing of data from Google Sheets to Microsoft Excel.

NOTE:

When you close MS (after saving it) and open it again, you might notice "Security Warning"

Click on "Options", and then you can "Enable this content" and click "OK"..

But if you don't want to do it every time you open your Excel then you can click on "Open the Trust Centre".

Click on "External Content" and then choose "Enable all Data Connections" and "Enable automatic update for all Workbook Links".

Finally you are done.

I also take up private and confidential projects:

http://igoogledrive.blogspot.com/p/paid-projects.html

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

Kishan.

iGoogleDrive