Fulcrum Data Share CartoDB Webhook

fulcrum-data-share-cartodb-webhook.js

function doPost(e){
  return handleResponse(e);
}

function handleResponse(e) {
  var jsonString = e.postData.getDataAsString();
  var payload = JSON.parse(jsonString);
  var formId = "my-form-id";
  var shareToken = "my-data-share-token";
  var cartodbTable = "my-cartodb-table";
  var cartodbUser = "my-cartodb-user";
  var cartodbAPIkey = "my-cartodb-api-key";
  
  // Make sure it's the form we want
  if (payload.data.form_id === formId) {
    // Fetch info from data share
    var geojson = UrlFetchApp.fetch("https://web.fulcrumapp.com/shares/" + shareToken + ".geojson?fulcrum_id=" + payload.data.id);
    var features = JSON.parse(geojson).features;
    var fields = [], values = [], updates = [];
    // Push feature properties to fields and values arrays
    for (var i = 0; i < features.length; i++) {
      for (var property in features[i].properties) {
        if (property != 'marker-color') {
          fields.push(property);
          if (features[i].properties[property] === null) {
            features[i].properties[property] = "";
          }
          values.push(features[i].properties[property]);
        }
      }
    }
    // Build SQL statement based on the type of webhook record event
    if (payload.type === "record.create") {
      var sql = "INSERT INTO " + cartodbTable + " (the_geom, " + fields.join(", ") + ") VALUES (ST_SetSRID(ST_MakePoint(" + payload.data.longitude + "," + payload.data.latitude + "),4326), " + "$$" + values.join("$$, $$") + "$$)";
    } else if (payload.type === "record.update") {
      for (var i = 0; i < fields.length; i++) {
        if (fields[i] != "fulcrum_id" || fields[i] != "marker-color") {
          updates.push(fields[i] + "=$$" + values[i] + "$$");
        }
      }
      var sql = "UPDATE " + cartodbTable + " SET the_geom = ST_SetSRID(ST_MakePoint(" + payload.data.longitude + "," + payload.data.latitude + "),4326), " + updates.join(", ") + " WHERE fulcrum_id = $$" + payload.data.id + "$$";
    } else if (payload.type === "record.delete") {
      var sql = "DELETE FROM " + cartodbTable + " WHERE fulcrum_id = $$" + payload.data.id + "$$";
    }
    // Update CartoDB table
    updateCartoDB(sql);
  }
  
  function updateCartoDB(sql) {
    var url = "https://" + cartodbUser + ".cartodb.com/api/v2/sql?q=" + sql + "&api_key=" + cartodbAPIkey;
    var options = {
      "method": "POST",
      "contentType": "application/json"
    };
    var response = UrlFetchApp.fetch(url, options);
  }
}