VBA to link googlesheet with Excel pivot

[rash120605]

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?

 

[Rijnsent]

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.:

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

 

[rash120605]

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.

 

[Rijnsent]

Hi Rash,
please elaborate, I don't get what you mean. What excel version are you using?
Koen

 

[rash120605]

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.

 

[Rijnsent]

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

 

[SpillerBD]

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.

 

[rash120605]

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