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.

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

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 entryThis 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");

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