Efficiently extract data using eHour's API

Do you wish to extract all tracked time from eHour using the API and do you want to do this regularly? Querying for all data, every time is an option but wastes a lot of bandwidth and CPU cycles. This article outlines how you can retrieve a delta of changed entries since your last API call.

Let us first get a quick overview of the structure of eHour:

1. A client is the parent object.
2. A client has multiple projects.
3. A project has multiple optional project tasks.
4. A user can be assigned to a project or project task.
5. Time is tracked on the assignment.

Time is tracked in minutes. Each entry has an "entry date": the day for which time is tracked. The last modification date is stored as the "update date".

The process

1. Map your internal users to eHour users by fetching all clients, projects, tasks, users and assignments. 
2. Form a baseline by retrieving all timesheet entries from eHour. 
3. From then on, provide the timestamp of your last retrieval and retrieve any changed timesheet entries since that timestamp.

1. Get the assignments

Use the assignments endpoint to retrieve all the active assignments in one go: https://ehourapp.com/api/swagger-ui.html#!/assignment/getAssignmentsUsingGET

This endpoint returns a list of these objects:

  {
    "assignmentId": "ASG1610",
    "start": "2020-10-24",
    "end": "2021-11-15",
    "active": true,
    "projectId": "PRJ322",
    "projectCode": "FYW",
    "projectName": "Flyweight",
    "projectActive": true,
    "clientId": "CLT50",
    "clientCode": "SPX",
    "clientName": "SpaceX",
    "clientActive": true,
    "userId": "USR947",
    "email": "engineer@example.com"
  }

The more users and projects you have, the longer this list is. Ideally, you store the assignment ID locally and call this endpoint only once a week or so - depending on how often your userbase and projects change.

To map projects and clients to our locally stored clients and projects: store your internal identifier in the code field. Of course, you can map on the project name and client name but this is prone to typos.

2. Get the initial dataset

Now that you know how users and projects map to which assignment, it's time to get all tracked time using the reporting endpoint:  https://ehourapp.com/api/swagger-ui.html#!/report/reportUsingGET

The minimum columns that you need are ASSIGNMENT_ID, HOURS_HOURS, HOURS_COMMENT. Set the aggregation to DAILY and provide a start and end date. The Request URL - with an example date set - is:

https://ehourapp.com/api/v1/report?start=2020-11-1&end=2020-1-1&page=1&aggregation=DAILY&columns=ASSIGNMENT_ID%2CHOURS_HOURS%2CHOURS_COMMENT

The endpoint returns a JSON with 3 objects: a columns, meta and data object:

{
  "columns": [
    "daily",
    "assignment-id",
    "hours-hours",
    "hours-comment"
  ],
  "meta": {
    "timestamp": 1607427886
  },
  "data": [
    [
      "12/11/2020",
      "ASG11137",
      2,
      "14:41 - 14:42: Called Jon\n14:42 - 14:43: Called Jon again"
    ]
  ],
}

The data object holds a list of all rows queried. The columns object indicates which field in the returned row is what. 

Take note that the column name for returning tracked time is misnamed. It's called hours-hours, but it actually returns minutes - this will be corrected in a revision of the API.

The timestamp in the meta-object is the timestamp of the current time. Store this locally for querying the delta in the next step

3. Updating 

If you want to refresh the data you have extracted from eHour, use the timestamp from your previous call to query for any added/changed/deleted entries since. Use the same start and end date as your previous call and use the timestamp from the previous call as the since parameter. 

https://ehourapp.com/api/v1/report?start=2020-11-1&end=2020-1-1&page=1&aggregation=DAILY&columns=ASSIGNMENT_ID%2CHOURS_HOURS%2CHOURS_COMMENT&since=1607427886

The response will return a new timestamp, which you can use for the next call.

If a user deleted his tracked time, it would return as 0 minutes with an empty comment.