Office Automation - Copy spreadsheet data

/**
 * See the documentation at https://developers.google.com/apps-script/storing_data_spreadsheets
 */


/*
 * Our main handler
 * The onEdit trigger is one of three built-in triggers
 * This function, if defined, is called automatically
 * @see https://developers.google.com/apps-script/understanding_triggers
 */
function onEdit(event) {
  var ss = event.source.getActiveSheet();
  var r = event.source.getActiveRange();
  r.setComment("Last modified: " + (new Date()));
  copyPhoneList();
  zebraStripe("Read Only");
  protectSheet('Read Only');
//   createOfficeSheets(); // don't do this every edit
}

// The onOpen function is executed automatically every time a Spreadsheet is opened
function onOpen(e) {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var menuEntries = [];
  // When the user clicks on "addMenuExample" then "Menu Entry 1", the function function1 is executed.
  menuEntries.push({name: "Generate Office Sheets", functionName: "createOfficeSheets"});
  ss.addMenu("Internal Tools", menuEntries);

  createOfficeSheets();
}


/*
 * A function to add protection to a sheet so that it's not
 * inadvertently modified
 */
function protectSheet(sheetName) {
  if (typeof(sheetName) == "undefined") {
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];
  } else {
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  }

  var permissions = sheet.getSheetProtection();
  var isProtected = permissions.isProtected();
  if (isProtected) {
    // Browser.msgBox('The sheet ' + sheetName + ' has been protected');
  } else {
    // Browser.msgBox('The perission check shows ' + permissions );
    permissions.addUser('greg.rundlett@gmail.com');
    permissions.addUser('editors@example.com');
    permissions.addUser('joe.boss@example.com');
    permissions.addUser('mary.manager@example.com');
    permissions.setProtected(true);
    sheet.setSheetProtection(permissions);
  }
}

/**
 * Our main function - responsible for copying data from our master data set to another copy that
 * is read-only (due to the sheet protection added to it).
 */
function copyPhoneList() {
  var hasFirstName = false;
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // var sheet = ss.getSheetByName("Master");
  // var data = sheet.getDataRange().getValues(); // the whole sheet

  var data = ss.getRangeByName("masterPhoneListData").getValues();  // the named range
  var range = ss.getRangeByName("masterPhoneListData");
  var numColumns = range.getEndColumn() - range.getColumn() + 1;
  //var numColumns = data[10].length; // how many columns are in each row
  var phoneList = new Array();


  // then we'll iterate through our source data and push recordDatas over to the target for each non-empty row
  for (i=0; i<data.length; i++) {       // we've already used a named data range to skip it's header
    hasFirstName = !isCellEmpty(data[i][4]);
    if (hasFirstName) {                 // if there is something in firstname, then get the record
      var record = new Array();
      for (j=0; j<numColumns; j++) {
        // we want to explicitly grab only columns a,b,c,d,e,g or 0,1,2,3,4,6
        switch(j) {
          case 0: // DID (Direct Inward Dial)
          case 1: // Ext
          case 2: // Office
          case 3: // First Name
          case 4: // Last Name
          case 6: // Cell Phone
            record.push(data[i][j]);
            break;

          default:
            break;

        }
      }

      // now add each record to the data we're compiling
      phoneList.push(record);
    }
  }

  // now push it all over to the target sheet
  var target = ss.getSheetByName("Read Only");
  target.clearContents(); // empty out our target sheet, preserving the formatting

  // first we'll create a heading row
  var heading = ['Direct', 'Ext', 'Office', 'First Name', 'Last Name', 'Cell Phone'];
  //target.appendRow(heading); // put the first heading row into our target sheet

  var headersRange = target.getRange(1, 1, 1, heading.length);
  headersRange.setValues([heading]);


  // we'd like to have sorted data, by First Name, and if those are equal, then by Last Name
  // JavaScript arrays can be sorted by using a callback function supplied as the argument to the sort() method call
  // http://www.sitepoint.com/sophisticated-sorting-in-javascript/
  phoneList.sort(function(a, b) {
    if(a[3].toLowerCase() === b[3].toLowerCase()) {
      var x = a[4].toLowerCase(), y = b[4].toLowerCase();
      return x < y ? -1 : x > y ? 1 : 0;
    } else {
      var x = a[3].toLowerCase(), y = b[3].toLowerCase();
      return x < y ? -1 : x > y ? 1 : 0;
    }
  });

  // All done, put the data into place
  target.getRange(2, 1, phoneList.length, phoneList[0].length).setValues(phoneList);
}



/**
 * Functionality based on the Google example code to populate a bunch of sheets based on
 * the "office" value in our master data set (The example breaks down a list of employees by department)
 */


// This is where the data used will be retrieved from:
var DATA_SPREADSHEET_ID = "0AqwQ0jPilu4IdGNqMzNXSy15ZWQ3c0ZFMkZja2RuS2c";

function createOfficeSheets() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];

  var dataSs = SpreadsheetApp.openById(DATA_SPREADSHEET_ID);
  // var dataSheet = dataSs.getSheets()[0];
  var dataSheet = ss.getSheetByName("Read Only");

  // Fetch all the data
  var data = getRowsData(dataSheet);

  // This is the data we want to display
  var columnNames = ['Direct', 'Ext', 'Office', 'First Name', 'Last Name', 'Cell Phone'];

  // Index data by office name
  var dataByOffice = {};
  var offices = [];
  for (var i = 0; i < data.length; ++i) {
    var rowData = data[i];
    if (!dataByOffice[rowData.office]) {
      dataByOffice[rowData.office] = [];
      offices.push(rowData.office);
    }
    dataByOffice[rowData.office].push(rowData);
  }

  offices.sort();
  var headerBackgroundColor = dataSheet.getRange(1, 1).getBackgroundColor();
  for (var i = 0; i < offices.length; ++i) {
    var sheet = ss.getSheetByName(offices[i]) ||
      ss.insertSheet(offices[i], ss.getSheets().length);
    sheet.clear();
    var headersRange = sheet.getRange(1, 1, 1, columnNames.length);
    headersRange.setValues([columnNames]);
    headersRange.setBackgroundColor(headerBackgroundColor);
    setRowsData(sheet, dataByOffice[offices[i]]);
  }
}

/*************************************************************************************
 * Code reused from Reading Spreadsheet Data using JavaScript Objects               **
 * https://developers.google.com/apps-script/storing_data_spreadsheets#reading-code **
 *************************************************************************************/


// setRowsData fills in one row of data per object defined in the objects Array.
// For every Column, it checks if data objects define a value for it.
// Arguments:
//   - sheet: the Sheet Object where the data will be written
//   - objects: an Array of Objects, each of which contains data for a row
//   - optHeadersRange: a Range of cells where the column headers are defined. This
//     defaults to the entire first row in sheet.
//   - optFirstDataRowIndex: index of the first row where data should be written. This
//     defaults to the row immediately below the headers.
function setRowsData(sheet, objects, optHeadersRange, optFirstDataRowIndex) {
  var headersRange = optHeadersRange || sheet.getRange(1, 1, 1, sheet.getMaxColumns());
  var firstDataRowIndex = optFirstDataRowIndex || headersRange.getRowIndex() + 1;
  var headers = normalizeHeaders(headersRange.getValues()[0]);

  var data = [];
  for (var i = 0; i < objects.length; ++i) {
    var values = []
    for (j = 0; j < headers.length; ++j) {
      var header = headers[j];
      values.push(header.length > 0 && objects[i][header] ? objects[i][header] : "");
    }
    data.push(values);
  }

  var destinationRange = sheet.getRange(firstDataRowIndex, headersRange.getColumnIndex(),
                                        objects.length, headers.length);
  destinationRange.setValues(data);
}



// getRowsData iterates row by row in the input range and returns an array of objects.
// Each object contains all the data for a given row, indexed by its normalized column name.
// Arguments:
//   - sheet: the sheet object that contains the data to be processed
//   - range: the exact range of cells where the data is stored
//       This argument is optional and it defaults to all the cells except those in the first row
//       or all the cells below columnHeadersRowIndex (if defined).
//   - columnHeadersRowIndex: specifies the row number where the column names are stored.
//       This argument is optional and it defaults to the row immediately above range;
// Returns an Array of objects.
function getRowsData(sheet, range, columnHeadersRowIndex) {
  var headersIndex = columnHeadersRowIndex || range ? range.getRowIndex() - 1 : 1;
  var dataRange = range ||
    sheet.getRange(headersIndex + 1, 1, sheet.getMaxRows() - headersIndex, sheet.getMaxColumns());
  var numColumns = dataRange.getEndColumn() - dataRange.getColumn() + 1;
  var headersRange = sheet.getRange(headersIndex, dataRange.getColumn(), 1, numColumns);
  var headers = headersRange.getValues()[0];
  return getObjects(dataRange.getValues(), normalizeHeaders(headers));
}

// For every row of data in data, generates an object that contains the data. Names of
// object fields are defined in keys.
// Arguments:
//   - data: JavaScript 2d array
//   - keys: Array of Strings that define the property names for the objects to create
function getObjects(data, keys) {
  var objects = [];
  for (var i = 0; i < data.length; ++i) {
    var object = {};
    var hasData = false;
    for (var j = 0; j < data[i].length; ++j) {
      var cellData = data[i][j];
      if (isCellEmpty(cellData)) {
        continue;
      }
      object[keys[j]] = cellData;
      hasData = true;
    }
    if (hasData) {
      objects.push(object);
    }
  }
  return objects;
}

// Returns an Array of normalized Strings.
// Empty Strings are returned for all Strings that could not be successfully normalized.
// Arguments:
//   - headers: Array of Strings to normalize
function normalizeHeaders(headers) {
  var keys = [];
  for (var i = 0; i < headers.length; ++i) {
    keys.push(normalizeHeader(headers[i]));
  }
  return keys;
}

// Normalizes a string, by removing all alphanumeric characters and using mixed case
// to separate words. The output will always start with a lower case letter.
// This function is designed to produce JavaScript object property names.
// Arguments:
//   - header: string to normalize
// Examples:
//   "First Name" -> "firstName"
//   "Market Cap (millions) -> "marketCapMillions
//   "1 number at the beginning is ignored" -> "numberAtTheBeginningIsIgnored"
function normalizeHeader(header) {
  var key = "";
  var upperCase = false;
  for (var i = 0; i < header.length; ++i) {
    var letter = header[i];
    if (letter == " " && key.length > 0) {
      upperCase = true;
      continue;
    }
    if (!isAlnum(letter)) {
      continue;
    }
    if (key.length == 0 && isDigit(letter)) {
      continue; // first character must be a letter
    }
    if (upperCase) {
      upperCase = false;
      key += letter.toUpperCase();
    } else {
      key += letter.toLowerCase();
    }
  }
  return key;
}

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

// Returns true if the character char is alphabetical, false otherwise.
function isAlnum(char) {
  return char >= 'A' && char <= 'Z' ||
    char >= 'a' && char <= 'z' ||
    isDigit(char);
}

// Returns true if the character char is a digit, false otherwise.
function isDigit(char) {
  return char >= '0' && char <= '9';
}

// The code below will get the number of rows in sheet 0
function getRowsCount() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];
  Browser.msgBox(sheet.getMaxRows());
}


// A function to alternately stripe a spreadsheet
// for better legibility
function zebraStripe(sheetName) {
  var i, thisRow;

  if (typeof(sheetName) == "undefined") {
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];
  } else {
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  }

  var lastRow = sheet.getLastRow();
  var lastCol = sheet.getLastColumn();
  var aEven = "#e0f1f5";
  var aOdd = "white";

  for (i = 1; i <= lastRow; i++) {
    thisRow = sheet.getRange(i, 1, 1, lastCol);
    thisRow.setBackgroundColor((i % 2 == 0)?aEven:aOdd);
  }
}