GameSalad / Google Sheets API

gscode.gs

/**
 * This script when Publish / Deployed as a web app,
 * will turn a Google sheet into an API that can communicate with a GameSalad game through it's Network Behaviors.
 * 
 * For this example, table column type and name information is stored in the first two rows.
 * Table row name information is in the first column.
 *
 * POST: Table data is stored in sheet in the document matching the table's tableId
 * GET:  The script will respond with a GameSalad Table pulling data from the sheet matching the 'tableId' parameter. 
 */
var SCRIPT_PROP = PropertiesService.getScriptProperties(); 
var DOC_ID = SCRIPT_PROP.getProperty("key");

function doGet (e) {
  return getTable(e) }

function doPost (e) {
  return updateTable(e) }

// Get table data from the google sheet.
function getTable (e){
  var tableId = e.parameter.tableId; // Get the table ID from the request.

  // Open the doc.
  var doc = SpreadsheetApp.openById(DOC_ID); // Open the doc attached to this script.
  var sheet = doc.getSheetByName(tableId); // Get the sheet/tab based on the table ID parameters.

  // This gets the full range of non blank cells in the sheet/tab.
  var range = sheet.getDataRange();
  var values = range.getValues();

  // Set up the table metadata which included the column data types as well row and column counts.
  var colTypes = values[0]   // We store column type info in the 1st row
  var colHeaders = values[1] // We store headers info in the 2nd row 
  var tableProps = [
    {"Name": "rowCount", "Value": values.length - 2 }, // Skip type and header info.
    {"Name": "columnCount", "Value": colTypes.length - 1 } // Skip row labels (1st col)
  ]

  // Set up the table data from colTypes / colHeaders by looping through our column header data.
  for (var i = 1; i < colHeaders.length; i++) {
    tableProps.push({"Name": [0, i, "name"].join("-"), "Value": colHeaders[i]})
    tableProps.push({"Name": [0, i, "type"].join("-"), "Value": colTypes[i]})
  }

  // GameSalad needs each row entry to be a pipe delimted string.
  var tableData = [];
  // We're looping through all of the rows and:
  for (var i = 2; i < values.length; i++) {
    var rowString = values[i].map(function(data) {
       if (typeof data === "string") {
         return data.replace("|", "\\|"); // Escape pipe in data so we don't split in the middle of a table cell later.
       }
       return data;
    }).join("|"); // Join all the cells with a pipe in between each cell.

    tableData.push({"Name": (i - 1).toString(), "Value": rowString}); // Now we add the row string into the return data.
  }

  // Put it all togther into a response object.
  // This is all the extra formatting required by GameSalad's parser.
  var tableResponse = {
    "Children": [
      {
        "Children":[],
        "Name": tableId + "_headers",
        "Properties": tableProps
      },
      {
        "Children":[],
        "Name": tableId,
        "Properties": tableData
      }
    ],
    "Name": "",
    "Properties":[]
  }

  // And send it back as a JSON string with a JSON content type.
  return ContentService.createTextOutput(
    JSON.stringify(tableResponse)
  ).setMimeType(
    ContentService.MimeType.JSON
  );
}

// Save table data to the google sheet.
function updateTable (e) {
  
  // Lock the table to avoid other changes happening at the same time.
  var lock = LockService.getPublicLock();
  lock.waitLock(500);
  
  try {

  // This is a very light security measure, to ensure the request aren't altered.
  // Since it's weak, you may want to consider commenting this out for performance,
  // but it does provide a measure of production if needed.
  var sha1Sig = e.parameter.sig;
  var tableSig = getContentSig(e.parameter.params);
  if (sha1Sig !== tableSig) {
    return ContentService.createTextOutput(
      JSON.stringify({
        "Status":"Failure"
      })
    ).setMimeType(
      ContentService.MimeType.JSON
    );
  }

  // Parse the table data json.
  var tableData = JSON.parse(e.parameter.params);

  // Grab the table ID and then a reference to the google sheet.
  // We're basically getting or creating a sheet with the matching table ID.
  var tableId = tableData["Children"][1]["Name"]
  var doc = SpreadsheetApp.openById(DOC_ID);
  var sheet = doc.getSheetByName(tableId);
  if (sheet === null) {
    sheet = doc.insertSheet();
    sheet.setName(tableId);
  }

  // Grab some info about the table being sent:
  var tableProps = tableData["Children"][0]["Properties"]
  var rowCount = tableProps.filter(
    function (prop) { return prop["Name"] === "rowCount" })["Value"]

  var columnCount = tableProps.filter(
    function (prop) { return prop["Name"] === "columnCount"})["Value"]

  var colNames = tableProps.filter(
    function (prop) { return prop["Name"].match(/\-name$/) }
  ).map(
    function (prop) { return prop["Value"] }
  )
    
  var colDataTypes = tableProps.filter(
    function (prop) { return prop["Name"].match(/\-type$/) }
  ).map(
    function (prop) { return prop["Value"] }
  )

  // sheet.clear() //Run this if you might get fewer rows than are already in the sheet.activate()

  // Set the headers.
  colDataTypes.forEach(function (dataType, colIdx) { 
    sheet.getRange(1, 2 + colIdx).setValue(dataType) // Data type info we need that later.
    sheet.getRange(2, 2 + colIdx).setValue(colNames[colIdx]) // Column names.
  })
 
  // Grab the row data.
  var rowMeta = tableData["Children"][1]["Properties"]
  var rowData = rowMeta.map(function (row) {
    var rowString = row["Value"]
    // Some jiggery pokery to get around the lack of lookbehind support.
    // Reverse the string.
    var reversed = rowString.split("").reverse().join("")
    // Split by doing a look ahead to avoid escaped pipes.
    var columns = reversed.split(/\|(?!\\)/)
    // Reverse the array, reverse the strings in the array. Replace escaped pipes.
    return columns.reverse().map(function(data) {
      return data.split("").reverse().join("").replace("\\|", "|")
    })
  })
  
  // Set data from the table into the sheet.
  // If your sheet isn't for syncing, you could also use sheet.appendRow instead of doing it per cell.
  // Iterate over each row
  rowData.forEach(function(row, rowIdx) {
    // Get the correct cell and set the value.
    row.forEach(function(cellData, colIdx) {
      sheet.getRange(rowIdx + 3, colIdx + 1).setValue(cellData)
    })
  })
  
  // Return the success response.
  return ContentService.createTextOutput(
    JSON.stringify({
      "Status":"Success"
    })
  ).setMimeType(
    ContentService.MimeType.JSON
  );
  
  } catch (e) {
    return ContentService.createTextOutput(
      JSON.stringify({
        "Status":"Failure",
        "Error": e
      })
    ).setMimeType(
      ContentService.MimeType.JSON
    );
  } finally {
    lock.releaseLock();
  }
}

// When we first start this script, figure out the ID of the attached document.
function setup() {
    var doc = SpreadsheetApp.getActiveSpreadsheet();
    SCRIPT_PROP.setProperty("key", doc.getId());
}

// This function calcualtes a SHA1 siganture from the table data. This can be used to ensure the table data
// has not been changed by someone in between the server and the game client. SHA1 is pretty weak and
// ideally we'd salt or sign the signature. Too much to get into here, but go google it.
function getContentSig(tableData) {
  var sha1Bytes = Utilities.computeDigest(Utilities.DigestAlgorithm.SHA_1, tableData)
  return sha1Bytes.reduce(function(str,chr){
    chr = (chr < 0 ? chr + 256 : chr).toString(16);
    return str + (chr.length==1?'0':'') + chr;
  },'');
}

// You can sort of ignore this. This gives us map and filter functions for arrays, 
// which are convienient and help make the code more readable.
// This is part of modern javascript, but is not quite in the version of javascript google is using, yet.

// Production steps of ECMA-262, Edition 5, 15.4.4.19
// Reference: http://es5.github.io/#x15.4.4.19
if (!Array.prototype.map) {

  Array.prototype.map = function(callback/*, thisArg*/) {

    var T, A, k;

    if (this == null) {
      throw new TypeError('this is null or not defined');
    }

    // 1. Let O be the result of calling ToObject passing the |this| 
    //    value as the argument.
    var O = Object(this);

    // 2. Let lenValue be the result of calling the Get internal 
    //    method of O with the argument "length".
    // 3. Let len be ToUint32(lenValue).
    var len = O.length >>> 0;

    // 4. If IsCallable(callback) is false, throw a TypeError exception.
    // See: http://es5.github.com/#x9.11
    if (typeof callback !== 'function') {
      throw new TypeError(callback + ' is not a function');
    }

    // 5. If thisArg was supplied, let T be thisArg; else let T be undefined.
    if (arguments.length > 1) {
      T = arguments[1];
    }

    // 6. Let A be a new array created as if by the expression new Array(len) 
    //    where Array is the standard built-in constructor with that name and 
    //    len is the value of len.
    A = new Array(len);

    // 7. Let k be 0
    k = 0;

    // 8. Repeat, while k < len
    while (k < len) {

      var kValue, mappedValue;

      // a. Let Pk be ToString(k).
      //   This is implicit for LHS operands of the in operator
      // b. Let kPresent be the result of calling the HasProperty internal 
      //    method of O with argument Pk.
      //   This step can be combined with c
      // c. If kPresent is true, then
      if (k in O) {

        // i. Let kValue be the result of calling the Get internal 
        //    method of O with argument Pk.
        kValue = O[k];

        // ii. Let mappedValue be the result of calling the Call internal 
        //     method of callback with T as the this value and argument 
        //     list containing kValue, k, and O.
        mappedValue = callback.call(T, kValue, k, O);

        // iii. Call the DefineOwnProperty internal method of A with arguments
        // Pk, Property Descriptor
        // { Value: mappedValue,
        //   Writable: true,
        //   Enumerable: true,
        //   Configurable: true },
        // and false.

        // In browsers that support Object.defineProperty, use the following:
        // Object.defineProperty(A, k, {
        //   value: mappedValue,
        //   writable: true,
        //   enumerable: true,
        //   configurable: true
        // });

        // For best browser support, use the following:
        A[k] = mappedValue;
      }
      // d. Increase k by 1.
      k++;
    }

    // 9. return A
    return A;
  };
}

if (!Array.prototype.filter){
  Array.prototype.filter = function(func, thisArg) {
    'use strict';
    if ( ! ((typeof func === 'Function' || typeof func === 'function') && this) )
        throw new TypeError();
   
    var len = this.length >>> 0,
        res = new Array(len), // preallocate array
        t = this, c = 0, i = -1;

    var kValue;
    if (thisArg === undefined){
      while (++i !== len){
        // checks to see if the key was set
        if (i in this){
          kValue = t[i]; // in case t is changed in callback
          if (func(t[i], i, t)){
            res[c++] = kValue;
          }
        }
      }
    }
    else{
      while (++i !== len){
        // checks to see if the key was set
        if (i in this){
          kValue = t[i];
          if (func.call(thisArg, t[i], i, t)){
            res[c++] = kValue;
          }
        }
      }
    }
   
    res.length = c; // shrink down array to proper size
    return res;
  };
}