Google Script for generating leadership status checkins. See https://github.com/CivicTechTO/civictechto-scripts/issues/2

code.gs

/**
 * Source: http://mashe.hawksey.info/2014/07/google-sheets-as-a-database-insert-with-apps-script-using-postget-methods-with-ajax-example/
 *
 * Usage
 *  1. Publish > Deploy as web app 
 *    - enter Project Version name and click 'Save New Version' 
 *    - set security level and enable service (most likely execute as 'me' and access 'anyone, even anonymously') 
 *
 *  2. Copy the 'Current web app URL'.
 *
 *  3. Insert column names on your destination sheet matching the parameter names of the data you are passing in (exactly matching case)
 *
 *  4. Fiddle more with this script
 */

var SHEET_NAME = "Co-organizers: Check-ins";

var doc = SpreadsheetApp.getActiveSpreadsheet();
var sheet = doc.getSheetByName(SHEET_NAME);
var sheetUrl = doc.getUrl()+"#gid="+sheet.getSheetId();

function testGet(){
  e = {parameter:{}};
  e.parameter.action = "POST";
  e.parameter.name = "foo";
  e.parameter.comment = "somecomment";
  e.parameter.slack_username = "mhellstern";
  e.parameter.slack_id = "U08B1JGBB";
  e.parameter.status = "alum";
  Logger.log("running testGet");
  doGet(e);
}

function doGet(e){
  if (typeof e.parameter.get_meta !== 'undefined') {
    return ContentService
          .createTextOutput(JSON.stringify({"result": "success", "data": {"sheet_url": sheetUrl}}))
          .setMimeType(ContentService.MimeType.JSON);
  }
  addrow(SHEET_NAME, e.parameter);      
  return ContentService
        .createTextOutput(JSON.stringify({"result": "success", "data": e}))
        .setMimeType(ContentService.MimeType.JSON);
}

function doPost(e){
  /**
   * Dont use this. doPost cannot give an answer. weird!
   * See: http://stackoverflow.com/questions/20028646/http-post-and-google-apps-script-file-upload )
   */
}

// Source: https://stackoverflow.com/a/10073788/504018
function pad(n, width, char) {
  char = char || '0';
  n = n + '';
  return n.length >= width ? n : new Array(width - n.length + 1).join(char) + n;
}

function addrow(sheetname, cells, headerRow) {
  /** 
   * This lock prevents concurrent access overwritting data.
   * See: http://googleappsdeveloper.blogspot.co.uk/2011/10/concurrency-and-google-apps-script.html
   *
   * We want a public lock, one that locks for all invocations.
   */
  var lock = LockService.getPublicLock();
  lock.waitLock(30000);  // Wait 30 seconds before conceding defeat.
  try {      
    // We'll assume header is in row 1 but you can override with headerRow in addRow
    var headRow = headerRow || 1; // TODO: Not actually implemented yet...!
    var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
    var nextRow = sheet.getLastRow() + 1; // Get next empty row.
    var row = []; 

    // Loop through the header columns.
    for (i in headers) {
      switch (headers[i].toLowerCase()) {
        case "timestamp":
          row.push(new Date());
          break;
        case "date":
          var now = new Date();
          // Generate date in YYYY-MM-DD format.
          var month = pad(now.getMonth()+1, 2)
          var date = pad(now.getDate(), 2)
          row.push([now.getFullYear(), month, date].join("-"));
          break;
        default:
          // If column not set via parameter, use empty string.
          if (typeof cells[headers[i]] == 'undefined') {
            row.push('');
          } else {
            row.push(cells[headers[i]]);
          }
      }
    }
    // More efficient to set values as [][] array than individually.
    sheet.getRange(nextRow, 1, 1, row.length).setValues([row]);
    // Return JSON success results.
    return ContentService
          .createTextOutput(JSON.stringify({"result":"success", "row": nextRow}))
          .setMimeType(ContentService.MimeType.JSON);
  } catch(e){
    return ContentService
          .createTextOutput(JSON.stringify({"result":"error", "error": e}))
          .setMimeType(ContentService.MimeType.JSON);
  } finally {
    lock.releaseLock();
  }
}