consulting_forecast.js

/* A script to update the consulting forecast with information pulled from Siebel
 *
 * Vivian Brown
 * 11/6/2012
 */

// Google spreadsheet service:
// https://developers.google.com/apps-script/service_spreadsheet

/**
 * Run an update of the forecast
 */
function updateForecast() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  
  // The control sheet contains some info about the location of input data.
  var current_quarter_location = "current";
  var next_quarter_location = "next";
  
  ss.toast("Updating forecast for the current quarter.");
  updateQuarter(ss.getSheetByName(current_quarter_location).getDataRange().getValues(), "CURRENT", ss);
  ss.toast("Updating forecast for the next quarter.");
  updateQuarter(ss.getSheetByName(next_quarter_location).getDataRange().getValues(), "NEXT", ss);
  ss.toast("Updates complete.");
}

/**
 * Update the forecast for a single quarter.
 * This function loads all the data from Siebel into a dictionary 
 * and then calls updateRange for each analyst.
 *
 * @inputRows: the data from Siebel as a 2d array
 * @quarter: "CURRENT" or "NEXT"
 * @ss: the spreadsheet document
 */
function updateQuarter(inputRows, quarter, ss) {
  var analyst_index = 7;

  // Load the data into a dictionary
  // Key: analyst's name
  // Value: 2D array containing their Siebel ouput
  var hash = {};
  for (var i = 1; i < inputRows.length; i++){
    var row = inputRows[i];
    var analyst = row[analyst_index].toUpperCase().replace(/ /g,'');
    if (hash[analyst]) {
      hash[analyst].push(row);
    } else {
      hash[analyst] = [row];
    }
  }
  
  // Build an empty row, to use as a spacer.
  // (Maintaing two empty rows at the end of each named range
  // makes it easier to insert new rows into the range)
  var emptyRow = [];
  for (var i = 0; i < inputRows[0].length; i++) {
    emptyRow.push("");
  }
  
  // For each analyst, update their sheet
  // Analyst names are pulled from the names of their sheets
  var sheets = ss.getSheets();
  for (var i = 1; i < sheets.length - 3; i++){ //Skip the three non-analyst sheets on the end 
    var data = [];
    var analyst = sheets[i].getName().toUpperCase().replace(/ /g,'');
    if (analyst in hash) { data = hash[analyst]; }
    data.push(emptyRow); // Keep two empty rows on the end so we can insert into the range
    data.push(emptyRow);
    updateRange(analyst + "_" + quarter, data, ss);
  }
}

/**
 * Updates a single range (one analyst, one quarter)
 * Entries from Siebel that weren't in last week's forecast are styled "bold".
 *
 * @rangeName: the name of the range to update - format: ANALYSTNAME_QUARTER
 * @curr: the new data to insert (2d array)
 * @ss: the spreadsheet document
 */
function updateRange(rangeName, newVals, ss){
  var oldRange = ss.getRangeByName(rangeName);
  if (!oldRange) { 
    Browser.msgBox("Did not find range " + rangeName);
    return false; // Make sure the range exists
  }
  
  oldRange.setFontWeight("normal");
  
  // Key: a row of data from last week's forecast
  // Value: true (need dictionary to behave like a set)
  // We'll use this to check which entries are new, and which
  // are duplicates from last week.
  var oldVals = oldRange.getValues();
  var oldSet = {};
  for (var i = 0; i < oldVals.length; i++){
    oldSet[oldVals[i]] = true;
  }
  
  // Create a one-column 2d array of styles
  // This will be used to mark new rows bold
  var styles = []
  for (var i = 0; i < newVals.length; i++){
    if (oldSet[newVals[i]]) {
      styles.push(["normal"]);
    } else {
      styles.push(["bold"]);
    }
  }
  
  // Resize the range to fit the new data
  var diff = newVals.length - oldVals.length;
  if (diff > 0) {
    oldRange.getSheet().insertRowsAfter(oldRange.getRow(), diff);
  } else if (diff < 0) {
    oldRange.getSheet().deleteRows(oldRange.getRow(), diff * -1);
  }
  
  // Insert the new data and style it
  oldRange = ss.getRangeByName(rangeName); // We've resized the range, so we need to get it again
  oldRange.setValues(newVals);
  oldRange.offset(0, 1, newVals.length, 1).setFontWeights(styles);
}

/**
 * Creates a new analyst sheet
 * TODO: Also add the analyst to the summary sheet
 */
function addAnalyst() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var name = Browser.inputBox("Please enter analyst name as it appears in Salesforce (First name and last name");
  
  if (name != "cancel" && name.length > 0) {
    ss.setActiveSheet(ss.getSheetByName("template"));
    var newSheet = ss.duplicateActiveSheet().setName(name);
    ss.setNamedRange(name.toUpperCase().replace(' ','') + "_CURRENT", newSheet.getRange("A2:H3"));
    ss.setNamedRange(name.toUpperCase().replace(' ','') + "_NEXT", newSheet.getRange("A36:H37"));
  }
}

/**
 * Adds a custom menu to the active spreadsheet.
 */
function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [
    {name : "Update Forecast",
    functionName : "updateForecast"},
    {name : "Add Analyst",
    functionName : "addAnalyst"},
                ];
  sheet.addMenu("Consulting Forecast", entries);
};