Post to Redmine (Google Sheets) JS

posttimesheets.js

/*
 * Post timesheet from Google Docs to Redmine
 *
 * Copyright (c) 2014 David Juhasz
 *
 * This program is free software; you can redistribute it and/or
 * modify it under the terms of the GNU General Public License
 * as published by the Free Software Foundation; either version 2
 * of the License, or (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * Author: David Juhasz <[email protected]>
 *
 */

/**
 * Creates a menu entry in the Google Docs UI when the document is opened.
 *
 * @param {object} e The event parameter for a simple onOpen trigger. To
 *     determine which authorization mode (ScriptApp.AuthMode) the trigger is
 *     running in, inspect e.authMode.
 */
function onOpen(e) {
  var ss = SpreadsheetApp.getActive();
  var items = [
    {name: 'Post timesheet', functionName: 'postTimesheet'},
  ];
  ss.addMenu('Scripts', items);
}

function postTimesheet() {
  timesheet = new Timesheet;
  // Parse timesheet
  timesheet.parse();
  if (timesheet.rows.length == 0) {
    Logger.log('No valid time entries found');
    return;
  }

  // Combine duplicate entries for the same day
  timesheet.rollup();

  // Combine description values for same day entries
  timesheet.deduplicate()

  // Send timesheet to Redmine
  var ret = timesheet.send();

  function Activity(actName) {

    // Lookup table
    this.activityIds = {
      'Administration' : '16',
      'Analysis/Design': '8',
      'Deployment'     : '12',
      'Development'    : '9',
      'Documentation'  : '10',
      'QA/Testing'     : '13',
      'Support'        : '14',
      'Training'       : '22',
      'Travel'         : '24',
      'Operations'     : '23'
    }

    this.name = actName;
    this.id = this.activityIds[actName];

    return this;
  }

  function Row(ssRow) {
    this.issueNum = ssRow[0].toString();
    this.desc = ssRow[1];
    this.start = ssRow[2];
    this.end = ssRow[3];
    this.hours = parseFloat(ssRow[4]);
    this.date = ssRow[5];
    // Lookup activityId
    activity = new Activity(ssRow[6]);
    this.activityId = activity.id;
    // Convert date to ISO string
    if (this.date.toString() !== '') {
      this.date = new Date(ssRow[5]);
      this.date = this.date.toISOString();
      // Truncate time
      var t = this.date.indexOf('T');
      if (t > -1) {
        this.date = this.date.substr(0,t);
      }
    }
    this.isValid = function() {
      if (this.issueNum.toString() === '' || this.hours.toString() === ''
          || this.date.toString() === ''
          || this.activityId.toString() === '') {
        return false;
      }
      return true;
    }
    return this;
  }

  function Timesheet() {
    this.dates = {};
    this.issueNums = {};
    this.rows = [];
    this.timeEntries = [];
    // Add valid timesheet entries to internal array
    this.parse = function() {
      var sheet = SpreadsheetApp.getActiveSheet();
      var data = sheet.getDataRange().getValues();
      // Ignore header row (start at row 2)
      for (var i = 1; i < data.length; i++) {
        var row = new Row(data[i]);
        if (row.isValid()) {
          this.rows.push(row);
        }
      }
    }

    // Combine duplicate entries of same issue number in same day
    this.rollup = function() {
      for (var i = 0; i < this.rows.length; i++) {
        var removeRows = [];
        for (var j = i + 1; j < this.rows.length; j++) {
          // If issue # and date match
          if (this.rows[i].issueNum == this.rows[j].issueNum
              && this.rows[i].date == this.rows[j].date) {
            // Add hours together
            this.rows[i].hours += this.rows[j].hours;
            // Concatenate descriptions if not the same
            if (this.rows[i].desc != this.rows[j].desc) {
              this.rows[i].desc += '; ' + this.rows[j].desc;
            }
            // Mark duplicate rows for removal, but don't do it now to
            // avoid array index problems
            removeRows.push(j);
          }
        }
        // Remove duplicate rows backwards, to avoid issues with changing
        // indexes
        for (var j = removeRows.length - 1; j >= 0; j--) {
          this.rows.splice(removeRows[j], 1);
        }
      }
    }

    // Cribbed from https://stackoverflow.com/a/14438954
    this.onlyUnique = function(value, index, self) {
      return self.indexOf(value) === index;
    }

    // Deduplicate description entries per task per day
    this.deduplicate = function() {
      for (var i = 0; i < this.rows.length; i++) {
        before = this.rows[i].desc.length;
        descriptionArr = this.rows[i].desc.split('; ');
        var unique = descriptionArr.filter( this.onlyUnique );
        this.rows[i].desc = unique.join("; ");
        info = (
          this.rows[i].desc + ": Length before: " + before + " After: "
          + this.rows[i].desc.length);
        Logger.log(info);
      }
    }

    // Call redmine method to post time entries
    this.send = function() {
      for (var i = 0; i < this.rows.length; i++) {
        redmine = new Redmine;
        toPost = this.rows[i];
        var resp = redmine.addTimeEntry(this.rows[i]);
        try {
          var data = JSON.parse(resp);
        } catch(e) {
          Logger.log('Couldn\'t parse response: ' + resp);
          return;
        }
        try {
          this.rows[i].id = data['time_entry']['id'];
        }
        catch(err) {
          Logger.log('Posted: ' + JSON.stringify(toPost));
          var postDate = new Date(toPost['start']);
          var minutes = postDate.getMinutes();
          var hours = postDate.getHours();
          timePosted = hours + ":" + minutes;
          details = (
              "Issue: " + toPost['issueNum'] +
              " Time: " + timePosted + " Date: " + toPost['date']);
          errString = "Error: " + data['errors'];
          SpreadsheetApp.getActiveSpreadsheet().toast(
              details, errString, -1);
           return;
        }
      }
    }
  }
}