ubersheet.js

/**
 * A spreadsheet reading library that assumes that the first row is headers
 * and provides a simple way to access, modify and append rows.
 */
var UberSheet = function(sheet) {
  // Constants
  this.HEADER_ROW_INDEX = 1;
  this.HEADER_ROW_OFFSET = 1;
  this.START_COLUMN = 1;

  this._sheet = sheet;
  this._sheetId = sheet.getName();
  this._numberOfColumns = sheet.getMaxColumns();
  this._numberOfRows = sheet.getMaxRows();

  // initialize.
  // this should be x+1
  var startRow = this.HEADER_ROW_INDEX + this.HEADER_ROW_OFFSET;
  var numberOfRowsToRead = this._numberOfRows - this.HEADER_ROW_INDEX;
  this._dataRange = sheet.getRange(startRow,
      this.START_COLUMN, numberOfRowsToRead, this._numberOfColumns);
  /* this._headersRange = sheet.getRange(this.HEADER_ROW_INDEX,
      this._dataRange.getColumn(), 1, this._numberOfColumns); */
  this._headersRange = sheet.getRange(this.HEADER_ROW_INDEX, 1, 1,
      this._numberOfColumns);

  this.headers = this._getHeaders();
};

/**
 * Fetch and normalize headers from the spreadsheet.
 */
UberSheet.prototype._getHeaders = function() {
  var headers = this._headersRange.getValues()[0];
  return this._normalizeHeaders(headers);
};

UberSheet.prototype._normalizeHeaders = function(headers) {
  var keys = [];
  for (var i = 0; i < headers.length; ++i) {
    if(!isCellEmpty(headers[i])) {
      keys.push(headers[i]);
    }
  }
  return keys;
};

UberSheet.prototype.readAllData = function() {
  var data = this._dataRange.getValues();

  var objects = [];

  for (var i = 0; i < data.length; ++i) {
    var object = new UberRow();

    object.__readonly_raw_ = data[i];
    object.__rowIndex_ = this.HEADER_ROW_INDEX + i + 1;
    object.__headers = this.headers;
    object.__sheetId = this._sheetId;

    var hasData = false;
    for (var j = 0; j < data[i].length; ++j) {
      var cellData = data[i][j];
      if (!isCellEmpty(cellData)) {
        hasData = true;
      }
      object[this.headers[j]] = cellData;
    }

    if (hasData) {
      objects.push(object);
    }
  }
  return objects;
};

UberSheet.prototype.putRow = function(row) {
  var values = [];
  if (row.__sheetId != undefined &&
     row.__sheetId != this._sheetId) {
    throw new Error('This row does not belong to this sheet');
  }

  for (var h in this.headers) {
    var header = this.headers[h];

    if (row[header] == undefined) {
      values.push('');
    // If the header is non-empty and the object value is 0...
    } else if (header.length > 0 && row[header] === 0) {
      values.push(0);
    }
    // If the header is empty or the object value is empty...
    else if (!header.length > 0 || row[header] == '') {
      values.push('');
    }
    else {
      values.push(row[header]);
    }
  }

  Logger.log("[Ubersheet] Writing row @ " + row.__rowIndex_);
  if (row.__rowIndex_) {
    // Found row index.
    this._sheet.getRange(row.__rowIndex_, 1, 1, this.headers.length)
      .setValues([values]);
  } else {
    // new row
    this._sheet.appendRow(values);
  }
};

var UberRow = function() {
  // The raw values, set at read time only.
  var __readonly_raw_ = undefined;
  // For rows previously read, the row index.
  var __rowIndex_ = undefined;
  // Sheet headers.
  var __headers = undefined;
  // the sheet this row was read from, if applicable.
  var __sheetId = undefined;
};

/*
 * Given a column letter 'A', 'B', 'AA', 'AZ', returns the value in that cell.
 * Can only be used on previously read fields.
 */
UberRow.prototype.getValueForColumnLetter = function(columnName) {
  var columnName = columnName.toUpperCase();
  var alph = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
  if (columnName.length == 2) {
    var tens = (alph.indexOf(columnName[0]) + 1) * 26;
    var ones = alph.indexOf(columnName[1]) + 1;
    return tens + ones;
  }
  return this.__readonly_raw_[alph.indexOf(columnName)];
};

/*
 * Return a simple array containing an array of values.
 * Works with previously read, and new rows.
 */
UberRow.prototype.getValues = function() {
  var values = [];
  for (var h in this.__headers) {
    var header = this.__headers[h];
    values.push(this[header]);
  }
  return values;
};

// Returns true if the cell where cellData was read from is empty.
// Arguments:
//   - cellData: string
function isCellEmpty(cellData) {
  return typeof(cellData) == "string" && cellData == "";
}