duration_and_laps.gs

/**
 * Duration and Lap Calculations
 * Matthew Leingang (https://github.com/leingang)
 * 2018-09-15
 * 
 * I want to be able to enter the number of laps and get the duration,
 * or the duration and get the number of laps.  To go one way only
 * requires a formula, but to go both ways requires a script.
 */

/**
 * @OnlyCurrentDoc Limits the script to only accessing the current sheet.
 */

/**
 * Constants
 * 
 * These are the column indices of various fields.  
 * Note A=1, B=2, etc.
 */
DURATION_COLUMN = 8;
PACE_MIN_COLUMN = 3;
PACE_SEC_COLUMN = 4;
LAPS_COLUMN = 6;

/**
 * Duration of a segment in minutes
 * 
 * @param nlaps number of laps run
 * @param pace  pace in minutes per lap
 */
function duration(nlaps, pace) {
  return pace * nlaps;
}

/**
 * Length of a segment in laps
 * 
 * @param dur  duration in minutes
 * @param pace pace in minutes per lap
 */
function laps(dur, pace) {
  return duration/pace; 
}

/**
 * Get the header cell for a range
 * 
 * @param r range
 **/
function range_header(r) {
  var col = r.getColumn();
  var sheet = r.getSheet();
  return sheet.getRange(1,col);
}

/**
 * Determine if a range refers to a number of laps
 * 
 * @param range range
 **/
function range_is_laps(range) {
  // test if the header row of the range is equal to "reps" 
  if (range_header(range).getValue() == 'reps') {
      Logger.log('Range ' + range.getA1Notation() + ' is a lap count');
      return true;
  }
  else {
      Logger.log('Range ' + range.getA1Notation() + ' is not a lap count');
      return false;
  }
}

/**
 * Determine if a range refers to a time duration
 * 
 * @param range range
 **/
function range_is_duration(range) {
  // test if the header row of the range is equal to "duration (min)" 
  if (range_header(range).getValue() == 'duration (min)') {
      Logger.log('Range ' + range.getA1Notation() + ' is a duration');
      return true;
  }
  else {
      Logger.log('Range ' + range.getA1Notation() + ' is not a duration');
      return false;
  }
}

/**
 * Get the pace value from a row, in minutes per lap
 *
 * @param r range
 */
function pace(r) {
  // TODO: make this update automatically by searching for the header text
  // right now, too lazy
  var nrow = r.getRow();
  var sheet = r.getSheet();
  var pace_min = sheet.getRange(nrow,PACE_MIN_COLUMN).getValue();
  var pace_sec = sheet.getRange(nrow,PACE_SEC_COLUMN).getValue();
  var answer = pace_min + pace_sec/60.0
  Logger.log('The pace associated to row ' + nrow + ' is ' + answer);
  return answer;
}

/**
 * Get the cell of a row that corresponds to the duration
 * 
 * @param r range
 */
function row_duration(r) {
  return r.getSheet().getRange(r.getRow(),DURATION_COLUMN);
}

/**
 * Get the cell of a row that corresponds to the laps/reps
 * 
 * @param r range
 */
function row_laps(r) {
  return r.getSheet().getRange(r.getRow(),LAPS_COLUMN);
}

/**
 * Trigger 
 * @param e event 
 */  
function onEdit(e) {
  var range = e.range;
  if range.getSheet().getName() == 'Time Calculator') {
    Logger.log('Range ' + range.getA1Notation() +  ' was edited');
    if (range_is_laps(range)) {
      var laps = range.getValue();
      Logger.log('lap count is ' + laps);
      var p = pace(range);
      var dur = laps * p;
      Logger.log('duration is ' + dur);
      row_duration(range).setValue(dur);
    }
    if (range_is_duration(range)) {
      var dur = range.getValue();
      var p = pace(range);
      row_laps(range).setValue(dur/p);
    }
  }
}