Search code examples
jsonpowerquerym

PowerQuery (M) to drill into Google Sheets API v.3 and extract sheet name and link url

So I am programming against v.3 of Google Sheets API (I know I need to move to v.4 at some point but I am shadowing a given code base.) I had some code to parse the JSON and drill in step wise in code but I'm trying also to learn M language for PowerQuery, so I would like some help again with how to write this PowerQuery.

I have tidied up the JSON to its minimum , here it is 

{ ""feed"": {""entry"": [ 
  {     ""title"": { ""$t"": ""1 Med"" },     ""link"": [ { ""href"": 
""https//removed1...."" } ]   }, 
  {     ""title"": { ""$t"": ""2 Dent"" },     ""link"": [ { ""href"": 
""https//removed2...."" } ]   }, 
  {     ""title"": { ""$t"": ""3 Vet"" },     ""link"": [  { ""href"": 
""https//removed3...."" }]   }
] } }

The JSON mimics (on a skeletal basis) what comes out of Google Sheets API v.3. So I know I need to drill down through the feed and entry nodes to get to the list of sheets. The sheetname is at feed.entry[x].title.$t whilst the url for the sheet data is at feed.entry[x].link[y].href where x increments and y is 0.

I would like a query to return in this case a 3 row 2 column matrix, with the sheetname in the left hand column and the href in the right hand column. Thus

'1 Med'      'https//removed1'
'2 Dent'     'https//removed2'
'3 Vet'      'https//removed3'

So far I have got to this below but I am stuck now.

let
    Source = Json.Document("{ ""feed"": {""entry"": [
  {     ""title"": { ""$t"": ""1 Med"" },     ""link"": [ { ""href"": ""https//removed1...."" } ]   },
  {     ""title"": { ""$t"": ""2 Dent"" },     ""link"": [ { ""href"": ""https//removed2...."" } ]   },
  {     ""title"": { ""$t"": ""3 Vet"" },     ""link"": [  { ""href"": ""https//removed3...."" }]   }
] } }"),
    feed = Source[feed],
    entry = feed[entry],
    #"Converted to Table" = Table.FromList(entry, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"title"}, {"title"})
in
    #"Expanded Column1"

Given enough examples I'll get the hang of this in a cookbook sort of way it's just I'm stuck. Thanks.

Solution

  • I'm super-curious how you will overcome the authentication challenge. It looked like "mission impossible" to me ...

    Anyway I played with your script in the GUI, removed the last step, added a few more and I think this is what you are after:

    let
    Source = Json.Document("{ ""feed"": {""entry"": [
  {     ""title"": { ""$t"": ""1 Med"" },     ""link"": [ { ""href"": ""https//removed1...."" } ]   },
  {     ""title"": { ""$t"": ""2 Dent"" },     ""link"": [ { ""href"": ""https//removed2...."" } ]   },
  {     ""title"": { ""$t"": ""3 Vet"" },     ""link"": [  { ""href"": ""https//removed3...."" }]   }
] } }"),
    feed = Source[feed],
    entry = feed[entry],
    #"Converted to Table" = Table.FromList(entry, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column2" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"title", "link"}, {"title", "link"}),
    #"Expanded title1" = Table.ExpandRecordColumn(#"Expanded Column2", "title", {"$t"}, {"$t"}),
    #"Expanded link" = Table.ExpandListColumn(#"Expanded title1", "link"),
    #"Expanded link1" = Table.ExpandRecordColumn(#"Expanded link", "link", {"href"}, {"href"})
in
    #"Expanded link1"