timetrackingpart2.js

var spreadsheet = SpreadsheetApp.getActiveSpreadsheet(),
    sheet = SpreadsheetApp.getActiveSheet(),
    rows = sheet.getDataRange(),
    numRows = rows.getNumRows(),
    values = rows.getValues(),
    endDate = new Date(),
    endDateCell = sheet.getRange("J2"),
    archiveCounter = sheet.getRange("K2").getValue(),
    archiveFolder = '',
    emailCell = sheet.getRange("L2").getValue(),
    splitVal = ';',
    subject = ''
    body = '',
    firstDataRow = 3,
    startTimeCol = 3,
    endTimeCol = 4,
    re = ' at';
 
/**
 * Retrieves all the rows in the active spreadsheet that contain data and converts Date/Time if necessary
 */
function fixDateTime() {
  for (var i = 0; i < numRows; i++) {
    var startVal = values[i][startTimeCol],
        endVal = values[i][endTimeCol];
    
    if (JSON.stringify(startVal).search(re) !== -1) {
      sheet.getRange(i+1, startTimeCol+1).setValue(JSON.parse(JSON.stringify(startVal).replace(re, '')));
    }
    
    if (JSON.stringify(endVal).search(re) !== -1) {
      sheet.getRange(i+1, endTimeCol+1).setValue(JSON.parse(JSON.stringify(endVal).replace(re, '')));
    }
  }
};

/**
 * Checks archive date before creating another document with data for archive. Clears out data
 * after archive and resets date to current date.
 */
function archiveData() {
  if (archiveCounter < 0) {
    endDate.setDate(endDate.getDate()-1);
    endDateCell.setValue(Utilities.formatDate(endDate, "GMT", "MM/dd/yyyy"));
    copyAndMove(endDate);
    sheet.deleteRows(firstDataRow, (sheet.getMaxRows() - firstDataRow + 1));
  }
};

/**
 * Copies current spreadsheet, appends end date to file name, and stores in target folder for archive
 */
function copyAndMove(endDate) {
  var fileName = spreadsheet.getName() + " " + Utilities.formatDate(endDate, "GMT", "yyyy-MM-dd");
  var folder = DocsList.getFolder(archiveFolder),
      fileId = spreadsheet.copy(fileName).getId(),
      file = DocsList.getFileById(fileId);
  file.rename(fileName);
  file.addToFolder(folder);
  file.removeFromFolder(DocsList.getRootFolder());
  emailURL(file.getUrl());
};

/**
 * Emails link to the most recent archived spreadsheet to all those listed in the email field
 */
function emailURL(url) {
  var emails = emailCell.split(splitVal);
  emails.forEach(function (email) {
    MailApp.sendEmail(
      email,
      subject,
      body + ' ' + url
    );
  });
};

/**
 * Clears all data rows
 */
function deleteDataRows() {
  sheet.deleteRows(firstDataRow, (sheet.getMaxRows() - firstDataRow + 1));
}

/**
 * Adds a custom menu to the active spreadsheet, containing a single menu item
 * for invoking the fixDateTime() function specified above.
 * The onOpen() function, when defined, is automatically invoked whenever the
 * spreadsheet is opened.
 * For more information on using the Spreadsheet API, see
 * https://developers.google.com/apps-script/service_spreadsheet
 */
function onOpen() {
  fixDateTime();
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [{
    name : "Fix Date/Time",
    functionName : "fixDateTime"
  }, {
    name: "Clear Data",
    functionName : "deleteDataRows"
  }];
  spreadsheet.addMenu("Script Center Menu", entries);
};