VBA to link googlesheet with Excel pivot

rash120605

New Member
Joined
Jan 10, 2019
Messages
4
Hi Guys,

this is my first post and I have always found this forum helpful for my excel queries,
I need to know if there is any possibility to connect my excel pivot with Googlesheet?
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Hi Rash,
interesting question! I've started using PowerQuery recently and you should definitely be able to connect to a Google Sheet through that (using the last version of MS Office).

Starting here:
https://stackoverflow.com/questions/34938961/can-we-use-google-spreadsheet-as-a-backend-database

I came up with going for the XML of a sheet and created this (PowerQuery, in Excel go for "Data->Data from the web->use the link and go for the Advanced Editor in the PowerQuery overview.

There paste e.g.:

Code:
let
    Bron = Xml.Tables(Web.Contents("https://spreadsheets.google.com/feeds/list/1IHF0mSHs1HdYpIlIzYKG3O8SnAhKU_a6nEJSz04Togk/od6/public/values")),
    #"Type changed" = Table.TransformColumnTypes(Bron,{{"id", type text}, {"updated", type datetime}}),
    entry = #"Type changed"{0}[entry],
    #"Cols removed" = Table.RemoveColumns(entry,{"id", "updated", "category", "title", "content", "link"}),
    #"ExpandColumn" = Table.ExpandTableColumn(#"Cols removed", "http://schemas.google.com/spreadsheets/2006/extended", {"id", "name", "last", "age", "add", "salary"}, {"id", "name", "last", "age", "add", "salary"} )
in
    #"ExpandColumn"
That could be the source of your pivot and will update if your table in Google Sheets expands. The google sheet API should provide better alternatives (as this is a hacky one), but that's for you to dive into :).
Cheers,
Koen
 
Upvote 0
unable to extract using PowerQuery, as the data is not extracted, how ever I do not want the data to be downloaded, I need o create a dashboard which needs to be linked with my google sheet, google sheet is being updated by several team members at different locations.
 
Upvote 0
Hi Rash,
please elaborate, I don't get what you mean. What excel version are you using?
Koen
 
Upvote 0
Hi Rijnsent,

I am using Excel 2016, and I actually want to create a pivot table in excel file but that pivot needs to be linked with data available in google sheet,
I hope I am able to explain this time :)

regards,
Abdul Rasheed.
 
Upvote 0
Hi Abdul,

If you have Excel 2016 you have PowerQuery, it's part of the programme from 2013. If you check out the stackoverflow link I posted, it links to a google sheet: https://docs.google.com/spreadsheets/d/1IHF0mSHs1HdYpIlIzYKG3O8SnAhKU_a6nEJSz04Togk/edit
See the unique number (starting with 1IHF0...)? Your sheets will have their own unique number (open the sheet to see it in your browser URL). The example code I gave can link that example sheet to an Excel data source which you can than use as the source for your pivot table. If someone adds a row in Google sheets and you refresh your pivot table, you will have the last info in your pivot table.

So step by step:
-open an excel file
-go in the menu to Data->Data from the web
-use as URL: https://spreadsheets.google.com/fee...zYKG3O8SnAhKU_a6nEJSz04Togk/od6/public/values and press OK
-the PowerQuery editor will open and show a menu, go for Display/Show -> Advanced Editor
-Copy-paste the code I posted above and press OK
-A table should show in the editor, go in the menu for Start-> Close and Load -> Close and Load to Pivot table
-And there you go: a pivot table in Excel linked to a Google sheet, to refresh the data connection (to the Google sheet) e.g. right click on the pivot table and refresh

Hope that works,

Koen
 
Upvote 0
unable to extract using PowerQuery, as the data is not extracted, how ever I do not want the data to be downloaded, I need o create a dashboard which needs to be linked with my google sheet, google sheet is being updated by several team members at different locations.

Of course you are going to download the data. It must be, though it doesn't need to Loaded to a Table. It must at least be loaded to the Data Model, which is not controlled by the M-language text provided Rijnsent.
 
Upvote 0
thank you so much guys for all your support,

I found a macro which can download once data once from googlesheet to my excel and then I have created a pivot from the same extracted data in my excel, now whenever I refresh all my pivots the excel is updated from the google sheet:

Sub Basic_Web_Query()
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;https://docs.google.com/spreadsheets", Destination:=Range("$A$1")) 'enter complete google sheet sharing link'
.Name = "q?s=goog_2"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "1"
.WebPreFormattedTextToColumns = False
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,194
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top