Site data to sheets

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

Type of analysis.

There are a few types of analysis we could do with the data, but we'll start with a very simple site profile - meaning a list of pages, urls and their parents. 

The data created in Analyzing site content with GAS is hosted on gdrive, and we picked up the hosting name when it was created

Here's the link to the live data described below ( returned when we created it).

{
    "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

Creating the receiving sheet.

I've created a sheet called tagsite, and given the names to columns I want to populate that match their names in the data.  Here's a snip of what will be created when i run it.

excel rest library site gas analysis population


Populate data

As with most Rest to Excel library examples, this is pretty much a one liner - referencing the library entry we created earlier and the sheet with the columns that need populated.

Public Sub testTagSiteJson()
    generalQuery("tagsite", "tagsitejson", "0B92ExLh4POiZTFgwcWtXUG1qVU0").tearDown
End Sub

You'll find this implemented in the cDataSet.xlsm workbook.

Google Apps Script Version

The GAS version uses the functions created in GAS Rest Library migration, so the code is more or less the same as the VBA version. 

Library entry
This is implemented in the mcpher shared library
  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
This is implemented in the restlibrary examples Google Spreadsheet.
function testTagSiteJson() {
    mcpher.generalQuery("tagsite", "tagsitejson", 
        "0B92ExLh4POiZTFgwcWtXUG1qVU0");
}

Following the same steps as for excel, here is the result in a google sheet


Next Steps


Ultimately though, we want to do some more interesting analysis with d3.js, so we need to dig into the data a little more.
For help and more information join our forum,follow the blog or follow me on twitter .

For more stuff see my book - Going Gas.  All formats are available now from O'Reilly,Amazon and all good bookshops. You can also read a preview on O'Reilly.




Comments