Page Comparison

You set up a trigger in a spreadsheet by going to the menu Tools -> Script Editor in the spreadsheet. This opens a new script editing window. Copy the code below into the Code.gs tab, replacing the values of the variables in the top 3 lines (datasetresource_name_or_id, api_key, prod_or_test which can be PROD or TEST depending upon the server you want to update). Then go to the Resources menu and select Current Project's Triggers. 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:

...

Note that the hdx.gapps.triggers@gmail.com user only needs read access to the spreadsheet for the purposes of the trigger. Write access is only needed for adding the code for the trigger to the Script Editor (which can be completed with a different user). If the hdx.gapps.triggers@gmail.com user only has read access, you will not be able to open the Script Editor from the spreadsheet, you will need the direct url (which you can get by opening from the spreadsheet with another user that does have write access).

Code Block

language	js
title	Resource and Datastore Triggers
collapse	true

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!");
}

Versions Compared

Old Version 4

New Version 5

Key