Google Docs *Community Challenge!*

[Yggdrasill]

Hello

As you know the corona virus is spreading around. The information are being gathered publicly here

The tables have the same structure for each sheet - which is important !

When I connect to the file using the web connector in PBI Desktop the Navigator pops up with this option

There are currently 19 Tables found as there are 19 sheets in the actual document. There will be more sheets to come !

Here is the problem - How can I refresh the data and get all tables at each refresh ?

What I've tried so far is to look at the difference between two table and found out that the sheets have Id's but there's no way of knowing what the Id is. However the power query function Html.Table will create this code for you when you select one table from the navigator

    #"Extracted Table From Html" = Html.Table(Source, {{"Column1", "DIV[id='1368739505'] > DIV.ritz.grid-container:nth-child(1) > TABLE.waffle:nth-child(1) > TR > :nth-child(1), DIV[id='1368739505'] > DIV.ritz.grid-container:nth-child(1) > TABLE.waffle:nth-child(1) > * > TR > :nth-child(1)"}, {"Column2", "DIV[id='1368739505'] > DIV.ritz.grid-container:nth-child(1) > TABLE.waffle:nth-child(1) > TR > :nth-child(2), DIV[id='1368739505'] > DIV.ritz.grid-container:nth-child(1) > TABLE.waffle:nth-child(1) > * > TR > :nth-child(2)"}, {"Column3", "DIV[id='1368739505'] > DIV.ritz.grid-container:nth-child(1) > TABLE.waffle:nth-child(1) > TR > :nth-child(3), DIV[id='1368739505'] > DIV.ritz.grid-container:nth-child(1) > TABLE.waffle:nth-child(1) > * > TR > :nth-child(3)"}, {"Column4", "DIV[id='1368739505'] > DIV.ritz.grid-container:nth-child(1) > TABLE.waffle:nth-child(1) > TR > :nth-child(4), DIV[id='1368739505'] > DIV.ritz.grid-container:nth-child(1) > TABLE.waffle:nth-child(1) > * > TR > :nth-child(4)"}, {"Column5", "DIV[id='1368739505'] > DIV.ritz.grid-container:nth-child(1) > TABLE.waffle:nth-child(1) > TR > :nth-child(5), DIV[id='1368739505'] > DIV.ritz.grid-container:nth-child(1) > TABLE.waffle:nth-child(1) > * > TR > :nth-child(5)"}, {"Column6", "DIV[id='1368739505'] > DIV.ritz.grid-container:nth-child(1) > TABLE.waffle:nth-child(1) > TR > :nth-child(6), DIV[id='1368739505'] > DIV.ritz.grid-container:nth-child(1) > TABLE.waffle:nth-child(1) > * > TR > :nth-child(6)"}, {"Column7", "DIV[id='1368739505'] > DIV.ritz.grid-container:nth-child(1) > TABLE.waffle:nth-child(1) > TR > :nth-child(7), DIV[id='1368739505'] > DIV.ritz.grid-container:nth-child(1) > TABLE.waffle:nth-child(1) > * > TR > :nth-child(7)"}}, [RowSelector="DIV[id='1368739505'] > DIV.ritz.grid-container:nth-child(1) > TABLE.waffle:nth-child(1) > TR, DIV[id='1368739505'] > DIV.ritz.grid-container:nth-child(1) > TABLE.waffle:nth-child(1) > * > TR"]),
   

If I right click on the document it self on google docs and select "Inspect" and then go to console and paste this code there and press Enter (works on Chrome and new Edge browser)

var urls=$$('a');for(url in urls){console.log("%c#"+url+" - %c"+urls[url].innerHTML +" -- %c"+urls[url].href,"color:red;","color:green;","color:blue;");}

I get this result 

+

As you can see the command lists out the sheet names for me.

I know there's a way to get all the sheet names and probably the Sheet's ID and then query the document for each ID to get ALL tables from the document.

This is do-able if this was an excel file using the function File.Contents() but I'm kinda stuck on how to workaround for this problem. See here

Long story short: I can't get all tables from this file : https://docs.google.com/spreadsheets/d/1yZv9w9zRKwrGTaR-YzmAqMefw4wMlaXocejdxZaTs6w/htmlview?usp=sha... because there will be added more and more sheets as time passes by.

Can we do this ? 

[v-alq-msft · Accepted solution]

Hi, @Yggdrasill 

You may try to operate as follows.

1. Connect the url :

url 

2. Add a custom column

3. Delete the origin Data column

url 

Here is the codes in Advanced Editor.

let
    Source = Excel.Workbook(Web.Contents("https://docs.google.com/spreadsheets/d/1yZv9w9zRKwrGTaR-YzmAqMefw4wMlaXocejdxZaTs6w/export?format=xlsx&id=1yZv9w9zRKwrGTaR-YzmAqMefw4wMlaXocejdxZaTs6w"), null, true),
    #"Added Custom" = Table.AddColumn(Source, "DataTable", each Table.PromoteHeaders([Data])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Data"}),
    #"Expanded DataTable" = Table.ExpandTableColumn(#"Removed Columns", "DataTable", {"Province/State", "Country/Region", "Last Update", "Confirmed", "Deaths", "Recovered", "Suspected", "Demised", "Country", "Date last updated"}, {"DataTable.Province/State", "DataTable.Country/Region", "DataTable.Last Update", "DataTable.Confirmed", "DataTable.Deaths", "DataTable.Recovered", "DataTable.Suspected", "DataTable.Demised", "DataTable.Country", "DataTable.Date last updated"})
in

Best Regards

Allan

[Yggdrasill]

You absolute beauty !

May I ask, how did you get the final url ?

I knew about the /export?format=xlsx but where did you get this string from ?

id=1yZv9w9zRKwrGTaR-YzmAqMefw4wMlaXocejdxZaTs6w

EDIT: Nevermind, I see it now it is what appears after /d/  !

https://docs.google.com/spreadsheets/d/1yZv9w9zRKwrGTaR-YzmAqMefw4wMlaXocejdxZaTs6w/export?format=xlsx&id=1yZv9w9zRKwrGTaR-YzmAqMefw4wMlaXocejdxZaTs6w

 Thanks again !