timeTracker.js

var sheet = SpreadsheetApp.getActiveSheet(),   
    rows = sheet.getDataRange(), 
    numRows = rows.getNumRows(),    
    values = rows.getValues(), 
    re = ' at',
    // Depending on layout and format of spreadsheet, alter these variables as necessary: 
    startTimeCol = 1,
    enteredExitedColumn = 0,
    hoursDurationColumn = 3,
    letterForHoursDurationColumn = "C",
    letterForTimestampColumn = "B",
    runningTotalColumn = 4, 
    letterForRunningTotalColumn = "D",
    hoursRemainingColumn = 5,
    weeklyHeadingTitles = ["Action", "Date and Time", "Hours", "Running Total", "Hours Remaining", "Notes", "", ""];

// Calculates daily hours spent at location only for last week by locating most recent weekly heading:
function calculateHours(){
  var beginningOfThisWeek = locateWeekBeginning();
  for (var i = beginningOfThisWeek; i < (numRows - 1); i++) {
    if (locateExitColumn(i)) {
      sheet.getRange((i+2), hoursDurationColumn).setFormula("=((" + letterForTimestampColumn + (i+2) + ") - (" + letterForTimestampColumn + (i+1) + "))");
    }
  }
};

// Helper method that confirms current row is an "exited" entry, so hour duration spent at work is only calculated after an exit. Used in multiple methods below. 
function locateExitColumn(currentIndex) {
  var currentEnterOrExit = values[currentIndex][enteredExitedColumn];
    if ((JSON.stringify(currentEnterOrExit).search("entered") !== -1) && (JSON.stringify(currentEnterOrExit+1).search("exited"))) {
      return true
    }
};

// Calculates duration spent at location for ALL entries. Included in custom menu options added in onOpen() callback below, in case something gets messed up. 
function calculateAllHours(){
    for (var i = 0; i < (numRows -1); i++) {
    if (locateExitColumn(i)) {
      sheet.getRange((i+2), hoursDurationColumn).setFormula("=((" + letterForTimestampColumn + (i+2) + ") - (" + letterForTimestampColumn + (i+1) + "))");
    }
  }
};

// Locates the beginning of the most recent week by looking for the weekly headings that should be added automatically every Monday.
// This is so calculations are only run on entries for past week, for speed purposes: 
function locateWeekBeginning(){
  for (var i = (numRows - 1); i > (numRows - 45); i--) {
    var currentActionCell = values[i][enteredExitedColumn];
    var currentDateTimeCell = values[i][startTimeCol];
    if (((JSON.stringify(currentActionCell).search("Action")) !== -1) && ((JSON.stringify(currentDateTimeCell).search("Date and Time")) !== -1)) {
      return (i + 2);   
    }
  }
};

function calculateTotals(){
  var beginningOfThisWeek = locateWeekBeginning();
  for (var i = beginningOfThisWeek; i < numRows; i++) {
    if (locateExitColumn(i)){
      sheet.getRange(i, runningTotalColumn).setFormula("=SUM(" + letterForHoursDurationColumn + beginningOfThisWeek + ":" + letterForHoursDurationColumn + i + ")");
    }
  }   
};

function calculateRemainingHours(){
  var beginningOfThisWeek = locateWeekBeginning();
  for (var i = beginningOfThisWeek; i < numRows; i++) {
    if (locateExitColumn(i)) {
      sheet.getRange(i, hoursRemainingColumn).setFormula("=((35/24) - " + letterForRunningTotalColumn + i + ")");
    }
  }   
};

// Adds headings (weeklyHeadingTitles array at top of file) to spreadsheet at the beginning of each week.
// Visit "Resources" > "Current Project Triggers" in Google Sheets Script Editor and set time-based trigger to run this method automatically every Monday or Sunday. 
// This is required, as other methods rely on weekly headings being present. 
function addWeeklyHeadings(){
  var nextAvailableRow = (numRows + 1); 
  for (var i = 0; i <= 7; i++){
    sheet.getRange(nextAvailableRow, (i+1)).setValue((weeklyHeadingTitles[i])).setFontWeight("bold").setBackground("#b3b3b3"); 
  }
};

// Removes the 'at' included in the timestamp by IFTTT so that date info can be proper datestamp, and caluculations can be run accordingly
// (I've tried, can't just tell IFTTT not to include 'at' :( 
function fixDateTime() {
  for (var i = 0; i < numRows; i++) {
    var startVal = values[i][startTimeCol];
    if (JSON.stringify(startVal).search(re) !== -1) {
      sheet.getRange(i+1, startTimeCol+1).setValue(JSON.parse(JSON.stringify(startVal).replace(re, '')));
    }
  }
};

// Callback that automatically runs when spreadhsheet is opened. Fixes date,calculates hours, adds custom menu option:  
function onOpen() {
  fixDateTime();
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  calculateHours();
  var menuOptions = [{name : "Calculate Hours",
                      functionName : "calculateHours"}, 
                     {name : "Add Headings", 
                      functionName : "addWeeklyHeadings"}, 
                     {name : "Recalculate All Hours",
                      functionName : "calculateAllHours"},
                    {name : "Fix Dates",
                      functionName : "fixDateTime"}, ];
  sheet.addMenu("Script Center Menu", menuOptions);
};