In Analyzing site content with GAS I showed how to analyze a Google Site and get the data as json, or file it on Google Drive. Next we want to do something with it. Here I’ll use the Rest to Excel library to get data into both Google Sheets and Excel. We’ll use the already created and hosted on gDrive file (although we could equally execute it from Excel or GAS and get love json using the same technique).
{ "data": [], "file": { "url": "https://docs.google.com/a/mcpher.com/file/d/0B92ExLh4POiZTFgwcWtXUG1qVU0/edit?usp=drivesdk", "name": "play.json", "id": "0B92ExLh4POiZTFgwcWtXUG1qVU0", "download": "https://docs.google.com/a/mcpher.com/uc?id=0B92ExLh4POiZTFgwcWtXUG1qVU0&export=download", "hosted": "https://googledrive.com/host/0B92ExLh4POiZTFgwcWtXUG1qVU0" } }
We’re going to use the hosted property to access the data – this gives us a rest library entry that looks like this (we’ll start with the excel version)
With .add("tagsitejson") .add "restType", erSingleQuery .add "url", "https://googledrive.com/host/" .add "results", "data" .add "treeSearch", True .add "ignore", vbNullString End With
Public Sub testTagSiteJson() generalQuery("tagsite", "tagsitejson", "0B92ExLh4POiZTFgwcWtXUG1qVU0").tearDown End Sub
w = cj.add("tagsitejson"); w.add ("restType", ERRESTTYPE.erSingleQuery); w.add ("url", "https://googledrive.com/host/"); w.add ("results", "data"); w.add ("treeSearch", true); w.add ("ignore");
function testTagSiteJson() { mcpher.generalQuery("tagsite", "tagsitejson", "0B92ExLh4POiZTFgwcWtXUG1qVU0"); }
Following the same steps as for excel, here is the result in a google sheet
bruce mcpherson is licensed under a Creative Commons Attribution-ShareAlike 4.0 International License. Based on a work at http://www.mcpher.com. Permissions beyond the scope of this license may be available at code use guidelines