Topline Tutorial

Introduction

Topline numbers are overview statistics about an entity. They are a way of summarising a few select important facts and presenting them in a visually appealing way on HDX. They can only be set up to appear on organisation or location pages at this time.

HDX

Within HDX an organisation or location may only have one set of topline figures which will appear on its front page eg.

https://data.humdata.org/organization/ocha-afghanistan

At the top of the above organisation page are various statistics like “Conflict induced IDPs” and “Disease outbreaks”.

https://data.humdata.org/group/bwa

On the top left of this location are some numbers such as “Population, total” and “GDP per capita, PPP”.

Topline numbers for organisations come from data held in the datastore, whereas for locations the data comes from CPS. They can be read through the HDX api eg. https://data.humdata.org/api/action/hdx_topline_num_for_group?id=yem

Google Spreadsheet

Toplines should be stored in a Google spreadsheet in the “Secured Files” folder on the HDX Google drive is used instead eg. for Fiji’s data: https://docs.google.com/spreadsheets/d/1ObwjZNS8y_mdjNXjhPLSqi-YGtQxkCDIW3Vg2kpwjgs

To use a Google spreadsheet as a topline:

1. Export it as a csv by using File -> Publish to the web

2. Choose  “Entire Document” and “Comma-separated values (.csv)” from the dropdowns

3. Click Publish

4. Copy the url which will be of the form: https://docs.google.com/spreadsheets/d/1ExKJOsFlZgVH-jvPc9DVwT3Gcusq7pLaxp7i2xnePDk/pub?output=csv

5. This url can then be used instead of a file import in a HDX resource

Topline Format

The spreadsheet (Google or csv) must have either 4 or 8 rows. The order in which the data is displayed is according to the row number.

The columns in the sheet are:

1. The code is not a generated unique identifier. It is just formed in some kind of logical way that is unlikely to conflict with other sheets eg. taking the acronym of the sheet’s title and adding a row number.

2. The value must just be an unformatted number ie. no separators

3. The units affects the display of value, for example a value of 3500000 with units of million will show as 35m

4. If there are insufficient rows (ie. not 4 or 8), one or more dummy rows must be put in eg. copy an existing row

Create Google Apps Triggers

1. Log into Google spreadsheets using the user: hdx.gapps.triggers@gmail.com from /wiki/spaces/HDX/pages/38551804. See Google Apps Triggers for more about permissions needed.
2. Go to the menu Tools -> Script Editor in the spreadsheet. This opens a new script editing window.
3. Name your script something descriptive eg. "OCHA Afghanistan Topline Resource and Datastore Triggers"
4. Copy the code below into the Code.gs tab, replacing the values of the variables in the top 3 lines (resource_id, api_key, server_type).
5. Then go to the Resources menu and select Current Project's Triggers. 
6. Click on the link which says "No triggers set up. Click here to add one now." and set it up as follows using the dropdown menus:  Run: doOnEdit    Events: From Spreadsheet | On edit
7. Click notifications and from the Execution failure notifications dropdowns, choose the hdx.gapps.triggers@gmail.com email you want to use and set when to notify as "immediately".
8. Create another trigger and set it up as follows using the dropdown menus:  Run: doOnTimer    Events: Time-Driven | Minutes timer | Every minute
9. Click notifications and from the Execution failure notifications dropdowns, choose the hdx.gapps.triggers@gmail.com email you want to use and set when to notify as "immediately".
10. A prompt may appear asking you to grant permissions to the user hdx.gapps.triggers@gmail.com which you should.

var resource_id = "XXX";
var api_key = "YYY";
var server_type = "PROD";
var schema = [
    {"id": "code",
    "type": "text"},
    {"id": "title",
    "type": "text"},
    {"id": "value",
    "type": "float"},
    {"id": "latest_date",
    "type": "timestamp"},
    {"id": "source",
    "type": "text"},
    {"id": "source_link",
    "type": "text"},
    {"id": "notes",
    "type": "text"},
    {"id": "explore",
    "type": "text"},
    {"id": "units",
    "type": "text"}
];
var primary_key = "code";

var property_name = "SHEET_WAS_EDITED";

function httpRequest(action, payload)
{
    if (server_type == "PROD") {
        var server = "https://data.humdata.org";
    } else if (server_type == "FEATURE") {
        var server = "https://feature-data.humdata.org";
    } else if (server_type == "DEMO") {
        var server = "https://demo-data.humdata.org";
    } else {
        var server = "https://test-data.humdata.org";
    }
    var url = server + "/api/3/action/" + action;

    var headers = {
        "X-CKAN-API-Key": api_key,
        "Authorization": "Basic ZGF0YXByb2plY3Q6aHVtZGF0YQ=="
    };
    if (typeof payload == "string") {
        var payload = {
            "id": payload
        };
    }
    var params = {
        "method"    : "POST",
        "contentType": "application/json",
        "headers": headers,
        "payload" : JSON.stringify(payload),
        "muteHttpExceptions": true
    };
    var result = UrlFetchApp.fetch(url, params);
    if (result.getResponseCode() == 200) {
        var obj = JSON.parse(result.getContentText());
        if (obj.success)
            return obj.result;
    } else {
      error = result.getContentText();
      Logger.log(result.getContentText());
      throw new Error(error);
    }
    return null;
}

function updateResourceDate() {
    var result = httpRequest("resource_patch", resource_id);
}

function getValues() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheets()[0];  
    // This represents ALL the data
    var range = sheet.getDataRange();
    var values = range.getValues();
    for (var j = 0; j < values[0].length; j++) {
        if (values[0][j] != schema[j]["id"]) {
            throw new Error("Schema and sheet header do not match!");
        }
    }
    var rowset = [];
    for (var i = 1; i < values.length; i++) {
        var row = {};
        for (var j = 0; j < values[i].length; j++) {
            row[schema[j]["id"]] = values[i][j];
        }
        rowset.push(row);
    }
    return rowset;
}

function updateDatastore() {
    var values = getValues();
    var data = {"resource_id": resource_id, "force": true};
    var method = "upsert";
    if (primary_key === null) {
        var result = httpRequest("datastore_delete", data);
        var method = "insert";
    }
    data["fields"] = schema
    data["primary_key"] = primary_key
    var result = httpRequest("datastore_create", data);
    var data = {"resource_id": resource_id,
            "force": true,
            "method": method,
            "records": values};
    var result = httpRequest("datastore_upsert", data);
}

function doOnEdit() {
    Logger.log("Running Installable Trigger that detects edits!");
    var documentProperties = PropertiesService.getDocumentProperties();
    documentProperties.setProperty(property_name, true);
    Logger.log("Edit Trigger Completed!");
}

function doOnTimer() {
    Logger.log("Running Installable Trigger that runs periodically!");
    var documentProperties = PropertiesService.getDocumentProperties();
    if (property_name in documentProperties.getProperties()) {
        documentProperties.deleteProperty(property_name);
        var resource = httpRequest("resource_show", resource_id);
        if (resource) {
            Logger.log("Touching resource!");
            updateResourceDate();
            Logger.log("Updating datastore!")
            updateDatastore();
        }
    }
    Logger.log("Timer Trigger Completed!");
}

Setup of Topline in HDX

To set up a topline like requires that there be a dataset (by convention named something like “OCHA Afghanistan Topline Figures”) containing the appropriate data in the right format. An example of such a dataset can be found here:  https://data.humdata.org/dataset/ocha-afghanistan-topline-figures.

Specifically the resource that is a csv entitled topline_figures contains the fields that are used.

To add a topline to an existing organisation, you must go to the appropriate section in the organisation’s setup. To do that:

1. You edit the organisation (Admin -> Edit in GUI or direct link eg. https://data.humdata.org/organization/edit/ocha-afghanistan)

2. Select “Use custom organization page”

3. You scroll down to “TOPLINE NUMBERS”

4. The “Resource ID” can be found from previewing the correct resource (eg. the csv) and taking the text after “...resource/” in the url eg. “ca6a0891-8395-4d58-9168-6c44e17e0193”

Deprecated - Scrapers

Now all disabled. use Google spreadsheet and triggers.

Future

In future, we can consider having a single Google spreadsheet with many sets of topline numbers (although how it would be updated by multiple organisations is not clear).

It should also be investigated if we can dispense with the datastore and have HDX read sheets directly.