How to Import Live Data From Google Sheets to Excel Worksheet

Import google sheets data into excel

Now most of the people are using Google sheets because of its collaboration and synchronisation feature. Sometimes we have to use Google sheet data in Excel worksheet because there are lots of apps that do not support Google sheets so if we want to put Google sheets data into Excel worksheet then this article is going to be helpful for you.

In this article I am going to tell you how to import live data from Google sheets and put it into an Excel worksheet. This data is going to refresh automatically according to the entered time or you can refresh it manually.

STEP - 1 : Go to your google sheet that you want to import in Excel.

STEP - 2 : Click on share and change the sharing option to anyone on the internet with this link can view. ( Your data is not going to be public unless you share the link.)

STEP - 3 : Copy shareable link of the sheet . Its looks like this -
Copied Link- https://docs.google.com/spreadsheets/d/1Vpk38FJ-KivrwUZDPdtpWpk-ZFVmnuxDy3iJ13WEDRY/edit?usp=sharing
Here red highlighted text is your sheet id .

Sharing Google Sheets to Public

STEP - 4 : Copy you sheet id and replace it with below formate :
Repaced Link - https://docs.google.com/spreadsheets/d/1Vpk38FJ-KivrwUZDPdtpWpk-ZFVmnuxDy3iJ13WEDRY/export?format=xlsx

STEP - 5 : Now Copy replaced link.

STEP - 6 : Now open your excel worksheet where you want to import google sheet data.

STEP - 7 : Go to DATA tab > New Query >From Other Sources > From Web. Paste your copied link and click on OK. After this all sheets show in the left pane and you can see the preview of these sheets in the right pane by clicking on sheet name.

Importing Data From Web In Excel Sheet

STEP - 8 : Click on the sheet that you want to import and click on LOAD. If you want to import more than one sheet you can select multiple sheets.

Importing URL to Google Sheets
Google Sheets To Excel

STEP - 9 : That's it your google sheet data loaded into your Excel worksheet as a table.

Refresh Data Automatically or Manually

Whenever you make changes in google sheets you have to refresh your data or you can set it as refresh data automatically every three minutes, ten minutes or whatever you want.

Option Of Connection Properties In Excel

To do that go to the DATA tab and click on the down arrow in refresh all field and click on Connection Properties. A new tab will open where you can set your data refreshness settings.

Connection Properties In Excel

No comments:

Post a Comment

Pages