Teamworks Data Sync API for Academics 2.0

Document Scope and Audience

This document is intended for API integrators who are looking to provide a bridge between a college or university Register system (such as Banner or Peoplesoft) and Teamworks Academics 2.0. Readers should be familiar with HTTP, CSV formats and scripting (although scripting language is unimportant).

Overview of Importing Data for Academics 2.0

Teamworks Data Sync APIs provide bulk "upsert" or "sync" functionality for Academics 2.0. These APIs allow the administrator to post data in bulk using CSV templates or JSON data to Teamworks. This documentation describes the Data Sync APIs in detail for Academics 2.0.

Caveat when sending JSON data

The API does not currently accept requests of content-type: application/json. If you prefer sending JSON over a CSV, it must be sent as a url-encoded string in a content-type: x-www-form-urlencoded POST request.

Course and Enrollment Data Sync

The Course and Enrollment data sync step provides Student enrollment, Course, Term and Course Appointments for Teamworks Academics 2.0. This is a separate API from the academics 1.0 API and is used very differently.

Caveats when migrating from Academics 1.0

Whereas Academics 1.0 APIs are RESTful and accept only a single record at a time, Academics 2.0 APIs accept record sets as input. Sending a single record per call of the sync API will not work as expected.

Academics 2.0 APIs require several more fields in the course data sync than the academics 1.0 integration requires. Additionally, Academics 2.0 APIs use flat-records for input. Headings required for the various formats listed below.

Academics 1.0 requires a purge of existing data before new records can be accepted. Academics 2.0 APIs reconcile data against existing data using the designated customer key. This means that no "flush" of Teamworks data is needed (nor available) before sending new data.

Academics 1.0 requires an API token. Your Academics 2.0 API token is separate. It is a regular OAuth token, which must be obtained as a part of the API flow.

Ensuring a successful sync

Before calling the Course and Enrollment Data Sync:

  • Make sure that all student athletes are present in Teamworks. If student athletes are not currently in Teamworks, please work with your athletic department to update rosters and accounts within Teamworks.
  • Make sure that all student athletes in Teamworks have a school ID defined in their profile.
  • Make sure all dates are in MM/DD/YYYY format. You must have a four digit year.
  • Times should be set in your organization's default timezone. If you are unsure of your organization's default timezone, check Teamworks settings.
  • Make sure you are aware of your API user's person_id and know how to obtain an OAuth Token from Teamworks. This is NOT your Academics 1.0 API Token.

Syncing records via POST

By default, the Teamworks Data Sync APIs operate in "upsert" fashion. This means that records in the input data are inserted if they're not present, and updated if they are. Records that are already in the system and are not contained in the data will NOT be deleted or affected in any way. In order to remove a record, you will need to include a dropped date in the dropped date field.

As an alternative, if you would like to send only the current enrollments and exclude previously dropped enrollments, you can set the "drop_not_passed_enrollments" flag to "true". This will cause enrollment records that are present in Teamworks but NOT present in the input data to be deleted. Note: when this flag is set to true, you do not need to include dropped dates.

Endpoint URL

https://api.teamworksapp.com/api/v1/academic_success/sync/enrollment

Input Arguments

POSTs including CSV files should use the standard HTTP input type multipart/form-data (all web calls that include file posts must be in this format according to the HTTP standard).

POSTs including JSON strings should use the HTTP input type x-www-form-urlencoded. Click here for more information on JSON support.

An important characteristic of the Data Sync APIs is that they are asynchronous (except when run in dry-run mode). Instead of a successful response indicating completion of the data sync activity, a successful response indicates that the data was accepted and that a data sync activity has been scheduled for sometime in the immediate future. The actual progress of a sync depends on the overall number of sync jobs Teamworks is processing at the time. During times of high loads a sync may take 1-2 hours before it is scheduled.

Once the status indicates that the sync has started, we expect a typical job of 5000 students to take 5-10 minutes, although longer times are possible. If your job has started and has been in-progress for more than 2 hours, contact Teamworks technical support for more help. Include the Job ID that you were given as part of your support ticket.

Required HTTP Headers
  • Authorization: Bearer <OAuth Token>
  • X-TW-PersonId: <Teamworks support supplied person ID>
Arguments
  • dry_run: (boolean, defaults to True). If this is true, no actual sync will be performed, however validation and a trial run will occur. The response type will either show validation errors or the records that would have been inserted or updated (for upsert mode) or deleted (if using sync mode).
  • input_data: (required multi-part encoded file if CSV. A url encoded JSON string if JSON). The actual input data to sync. It needs to follow the format exactly as designated in Input Data Format
  • input_type: (required enum). The supported values are CSV and JSON.
  • drop_not_passed_enrollments: (boolean, defaults to False). If any enrollments in the system are not included in your payload, they will be marked as "dropped" and removed from the student’s profile if this is set to True.
Example cURL Requests
CSV:

First, authenticate with the API to receive a bearer token. This token will be used in the subsequent enrollment sync request.

curl --location --request POST 'https://api.teamworksapp.com/auth/oauth2/token' \
--header 'Content-Type: application/x-www-form-urlencoded' \
--data-urlencode 'username=<your_username>' \
--data-urlencode 'password=<your_password>' \
--data-urlencode 'client_id=<your_client_id>' \
--data-urlencode 'client_secret=<your_client_secret>' \
--data-urlencode 'grant_type=password'

Now, call the enrollment sync with the bearer token received from the above request, and the person_id for the API user that was assigned to your organization.

curl -L -R POST "https://api.teamworksapp.com/api/v1/academic_success/sync/enrollment" \
-H "Authorization: Bearer {{access_token}}" \ # Generated OAuth2 token
-H "X-TW-PersonId: {{teamworks_person_id}}" \ # ID associated with your Teamworks user
-H "Content-Type: multipart/form-data" \
-F "input_type=CSV" \
-F "input_data=@enrollment_upload.csv" \ # Path to CSV file. Include '@' before path.
-F "dry_run=true"
JSON:
curl -L -R POST 'https://api.teamworksapp.com/api/v1/academic_success/sync/enrollment' \
-H 'Authorization: Bearer {{access_token}}' \ # Generated OAuth2 token
-H 'X-TW-PersonId: {{teamworks_person_id}}' \ # ID associated with your Teamworks user
-H 'Content-Type: application/x-www-form-urlencoded' \
--data-urlencode 'input_type=JSON' \
--data-urlencode 'input_data=[{...}]' \ # JSON array of objects.
--data-urlencode 'dry_run=true'

Response Data

For a response code of 200, you will receive a JSON string containing a Status object. See below for a detailed description of the status object.

{
    "id": 137,
    "job_type": "CourseDataSync",
    "target_org_id": 23, // your teamworks org id
    "person_id": 23456, // the creator's teamworks user id
    "submitted_date": "2019-07-11T15:44:23.208700",  // date the job was created in UTC
    "last_update": "2019-07-11T15:44:23.208700", // date the job was last updated in UTC
    "submitted_date": "2019-09-16T20:21:35.697105+00:00",
    "last_update": "2019-09-16T20:22:35.697105+00:00",
    "info": { ... } // a status object (more detail below)
}

This job id is what you need to fetch your job status via GET.

Other possible response codes are:

  • 500: A server error has occurred. Contact Teamworks support with the contents of the exception
  • 400: Essential keys are missing from the data.
  • 422: One or more required parameters contains invalid data. See the exception for details
  • 403: The user is not allowed to perform the sync. Check that your API user and PersonID are correct
  • 401: The OAuth token is invalid or does not correspond to the PersonID. Check that your API user and PersonID are correct.

Fetching sync status via GET

Once a job has been submitted, you can query Teamworks for status of the job. This helps determine whether the job has completed, and if there were errors in the input data that need to be corrected.

Endpoint URL

https://api.teamworksapp.com/api/v1/academic_success/sync/enrollment/status/[status_id]

where [status_id] is the value of the "job_id" key in the JSON data returned by a POST.

Status Object Response Data

Example:

{
  "id": 123, // the status_id
  "job_type": "CourseDataSync",
  "target_org_id": 23, // teamworks org id
  "person_id": 25556, // the submitter's teamworks user id
  "submitted_date": "2019-07-11T15:44:23.208700",  // UTC
  "last_update": "2019-07-11T15:44:23.208700", // UTC
  "info": {
    "extra": {
      "code": 400,
      "data": {"duplicates": ["team"]},
      "message": "File has duplicate column names. Here are your columns: ['team' 'team.1']",
      "classname": "BadRequest"
    },
    "status": "failed",
    "message": "Job failed. See \"extra\" key for more info.",
    "child_task_ids": [],
    "input_src_name": "default",
    "percentage_completed": 0.0
  }
}
Meanings of the keys:
  • "job_type": The type of sync being performed. For student enrollments, it is always "CourseDataSync"
  • "person_id": The person_id of the API user who scheduled the sync
  • "id": The id of the status object, so you can check it again.
  • "submitted_date": The timestamp that the job was submitted to Teamworks.
  • "updated_date": The timestamp the job was last updated by Teamworks.
  • "info.percentage_completed": An integer number between 0 and 100. 100 means that the job has completed successfully.
  • "info.status": "pending", "failed", or "completed" -- The current status of the job
  • "info.message": The last log message from the API
  • "info.child_task_ids": Internal, should be ignored. Will be removed soon
  • "info.extra": In the event that the job has failed, this will contain information about why the job failed, if we were able to determine a reason.
Error Conditions and Error Data Schema

Teamworks Data Sync APIs will try to catch as many errors in the first pass of validation as it can, to prevent a user from having to submit a dataset once for each error in the data. This is not perfect, though, and certain errors can mask other errors. Implementors should use the dry_run=true option until all validation errors are gone.

All validation errors will be contained in the following structure in the data attribute of the status response:

{
    "code": 400,
    "data": {"duplicates": ["team"]},
    "message": "File has duplicate column names. Here are your columns: ['team']",
    "classname": "BadRequest"
}

The classname of the exception can be:

  • MissingStudentsException: data will contain the student ids of missing students.
  • MissingColumnsException: data will contain names of missing columns
  • DisallowedNullsException: data will contain column names that contain nulls
  • UnprocessableEntity: a generic validation exception. See message for details.
  • BadDateRange: end time or end date is before start time / start date
  • MissingKeys: customer key contains nulls / blanks
  • PersonNotInOrg: a student declared in the data is missing from Teamworks.

For other errors, see the "message" property of the response for info. Depending on the error, there may be data in the "data" property as well, but it is not guaranteed. For errors other than these, the "code" will be "500".

Input Data Format

Contact your customer success rep for the current course data template for Academics 2.0

To insert student enrollments properly there must be one row per unique combination of:

  • Student
  • Class Code
  • Class Section Code
  • Start Time
  • End Time
  • Class Building/Room OR Professor

In other words, for "split sections", put the different class start/end times on different rows of the spreadsheet.

Formatting data
  • Dates must be formatted as "MM/DD/YYYY"
  • Times can be in am/pm format with a space between the time and the AM/PM indicator or Military Time, e.g. "10:00 AM" or "13:00".
  • If your student IDs have leading zeros, and you are uploading a CSV, you must make sure that those leading zeros are preserved.
  • No commas separating thousands in numbers
  • Column/Data names are case-sensitive and must be exactly as specified, including the "*".
  • Any column with a * must have data in every row.
  • If you are editing your file in Excel, nulls must always be represented by a blank cell. " ", "-", "NULL", "0" and any other value will be interpreted literally.
  • True/False are always represented as capital Y/N. A blank is not considered "false" but "unset".
Expected headings

"*" Starred headings must include the star in the heading, and are required to have an entry.

All columns must be present in the data, but no order is required.

All column names must be reproduced exactly.

  • School ID*: (Text). The student's university ID
  • Student First Name: (Text) Student first name (optional)
  • Student Last Name: (Text) Student last name (optional)
  • Subject Code*: (Text). The subject code (e.g MAT, BIO, CHEM, etc)
  • Subject Name: (Text). The human readable name of the subject (e.g. Math, Biology, Chemistry, etc)
  • Class Code*: (Text). The university's course code (e.g. MAT-101, BIO-101, CHEM-101, etc). Class description and credits attempted will be tied to the class code and therefore cannot be unique.
  • Class Section Code*: (Text). The code issued for this course section for the given term (Everyone's different, but it must be unique per section, per term. Should not be "MAT-101-1". That case would just be "1"). Time, location, days and professor information will be tied to the class section code and therefore cannot be unique.
  • Class Description*: (Text). A human readable description of the class (e.g. "Algebra 1", "College Biology", "College Chemistry")
  • Credits Attempted: (Decimal). Number of credits the student attempted on this course section.
  • Grade: (Grade Code). The student's grade in this course section. This code must already exist in the Teamworks system. Be sure to supply your letter grades and weights during your college or university's onboarding process. Note:We do not currently support a grade of 'W'. If a student withdraws from a class, you should treat it as if it were dropped.
  • Score: (Decimal). The student's raw numeric score in this course section.
  • Dropped Date: (ISO-8601 Date). Denotes this enrollment as dropped. This ensures that appointments no longer exist for this student for the class. This field can be ignored if you choose to have drop_not_passed_enrollments set to True
  • Class Building/Room: (Text).
  • Monday?*: (Y/N). Whether or not the class meets on Monday at the given Start Time and End Time. If a course meets at two different times, supply one row per start and end time.
  • Tuesday?*: (Y/N). Whether or not the class meets on Tuesday at the given Start Time and End Time.
  • Wednesday?*: (Y/N). Whether or not the class meets on Wednesday at the given Start Time and End Time.
  • Thursday?*: (Y/N). Whether or not the class meets on Thursday at the given Start Time and End Time.
  • Friday?*: (Y/N). Whether or not the class meets on Friday at the given Start Time and End Time.
  • Saturday?*: (Y/N). Whether or not the class meets on Saturday at the given Start Time and End Time.
  • Sunday?*: (Y/N). Whether or not the class meets on Sunday at the given Start Time and End Time.
  • Start Time: (ISO-8601 Time). The beginning of the class
  • End Time: (ISO-8601 Time). The end of the class
  • Term ID*: (Text). The college or university's unique name for this term and academic year. (e.g. "Spring 2018 10wk")
  • Term Start Date*: (ISO-8601 Date). This is the day the course section starts in this term
    The date that appointment occurrences should start for the course. Can be the same date as the beginning of the term, even if classes don't start until the next valid weekday. (i.e. if a term starts on a Monday, but class doesn't meet until Thursday, it's fine to have the start date be the Monday). The term's start/end will be derived by the earliest and latest dates in this column. Note: For courses that start late in the term, such as a 4 or 6 week course, use the start date of the course in this field.
  • Term End Date*: (ISO-8601 Date). This is the day the course section ends in this term
    The date that appointment occurrences should end for the row's course section. Can be the same date as the end of the term, even if classes have already finished. (i.e. if a term ends on a Friday, but the last class met on Thursday, it's fine to have the end date be the Friday). Note: For courses that end early in the term, such as a 4 or 6 week course, use the end date of the course in this field.
  • Professor First Name: (Text). The professor's first name.
  • Professor Last Name: (Text). The professor's last name.
  • Professor Email: (Text). The professor's email address. Must have this to ensure professor surveys and travel letters go out.
  • Professor Phone: (Text). The professor's phone number.
  • Professor Office: (Text). The professor's office location.

Complete Python Example

This example covers a few common error conditions and the result of correcting those errors as you go along. First thing we'll do is a dry run of loading our data. We will need reasonably recent versions of oauthlib and requests_oauthlib

from oauthlib.oauth2 import LegacyApplicationClient
from requests_oauthlib import OAuth2Session
import os

The client_id and client_secret are generated by Teamworks and can be provided to you via Teamworks support. Username and password are the username and password of the API user for your organization.

oauth = OAuth2Session(
    client=LegacyApplicationClient(client_id=client_id)
)
token = oauth.fetch_token(
    token_url=f'https://api.teamworksapp.com/auth/oauth2/token',
    username='academics_two',
    password='Test1234',
    client_id=client_id,
    client_secret=client_secret
)

The previous line should fail immediately if credentials are invalid, but just to be sure, let's look at our token:

token['access_token']
'VsuMNdewx4ELOjs9q1FFfLtQWKsCkt'

We're good. Let's do a dry-run! To do that, we use our OAuth 2 authenticated requests client to POST to https://api.teamworksapp.com/api/v1/academic_success/sync/enrollment with an open filestream containing our CSV data. The header we pass is Teamworks specific. It is your admin user's "Person ID" which you will receive from Teamworks support.

null= None
import json

rsp = oauth.post(
    'https://api.teamworksapp.com/api/v1/academic_success/sync/enrollment',
    data={
        "input_type": 'JSON',
        "input_data": json.dumps([{
                "School ID*"          : "112636575",
                "Subject Code*"       : "MAT",
                "Subject Name"        : "Math",
                "Class Code*"         : "MAT-101",
                "Class Section Code*" : null,
                "Class Description*"  : "Alegbra 1",
                "Credits Attempted"   : "3",
                "Grade"               : null,
                "Score"               : null,
                "Dropped Date"        : null,
                "Class Building/Room" : "Adams Hall 101",
                "Monday?*"            : "Y",
                "Tuesday?*"           : "N",
                "Wednesday?*"         : "Y",
                "Thursday?*"          : "N",
                "Friday?*"            : "Y",
                "Saturday?*"          : "N",
                "Sunday?*"            : "N",
                "Start Time"          : "10:30 AM",
                "End Time"            : "11:30 AM",
                "Term ID*"            : "201910",
                "Term Start Date*"    : "08/19/2019",
                "Term End Date*"      : "12/15/2019",
                "Professor First Name": "Smith",
                "Professor Last Name" : "John",
                "Professor Email"     : "John.Smith@ou.edu",
                "Professor Phone"     : null,
                "Professor Office"    : null
        }])
    },
    headers={'X-TW-PersonId': '56761'}
)

NOTE: The json.dumps(...) example above is encoding a JSON string and adding it as a value on the request. This example is not a content-type: application/JSON request. Click here for more information on JSON support.

rsp.status_code
200
rsp.json()
{'id': 137,
 'info': {'child_task_ids': [],
          'extra': {'classname': 'DisallowedNullsException',
                    'code': 422,
                    'message': 'DisallowedNulls: One or more non-nullable '
                               'input columns contain nulls. (Class Section '
                               'Code*)'},
          'input_src_name': 'default',
          'message': 'Job failed. See "extra" key for more info.',
          'percentage_completed': 100.0,
          'status': 'failed',
          'warnings': []},
 'job_type': 'CourseDataSync',
 'last_update': '2019-09-17T11:45:25.046059+00:00',
 'person_id': 56761,
 'submitted_date': '2019-09-17T11:45:24.900410+00:00',
 'target_org_id': 3171}

Okay so our status code is 200, but the actual json body of the response shows an exception. In general unless the user is unauthorized or unauthenticated, the status code will be a 200. The only other real possibility is a 500, which indicates that something has gone wrong on Teamworks' end. Other status codes indicating different kinds of problems with the data or the call are indicated in the status "info" object under "info.extra.code". The precise error object type is in "info.extra.classname" and the error message is "info.extra.message"

Here we POSTed a CSV file with columns that had incorrect header names. We will correct the CSV to use the exact header names that the error message provided (case-sensitive), and try again:

rsp = oauth.post(
    'https://api.teamworksapp.com/api/v1/academic_success/sync/enrollment',
    files={'input_data': open('~/Purdue Class Schedules Summer 2019.csv')},
    data={'input_type': 'CSV', 'dry_run': True},
    headers={'X-TW-PersonId': '56761'}
)
rsp.status_code
200
rsp.json()
{'id': 135,
 'info': {'child_task_ids': [],
          'extra': {},
          'input_src_name': 'default',
          'message': '',
          'percentage_completed': 100.0,
          'status': 'completed',
          'warnings': [{'classname': 'MissingStudentsException',
                        'data': {'missing_students': [{'School ID': '112636575',
                                                       'Student First Name': None,
                                                       'Student Last Name': None}]}}]},
 'job_type': 'CourseDataSync',
 'last_update': '2019-09-17T11:42:51.895898+00:00',
 'person_id': 56761,
 'submitted_date': '2019-09-17T11:42:50.641392+00:00',
 'target_org_id': 3171}

Now we have missing students. We were unable to detect this before because the data was incomplete, but having made it past that stage of validation, we come to this one. We go and either:

  • Delete those students
  • Check their School ID* column to make sure that the value is correct and correctly formatted with any leading zeroes
  • Add missing students into Teamworks via the admin panel or through a Teamworks user upload.

And then we run again.

rsp = oauth.post(
    'https://api.teamworksapp.com/api/v1/academic_success/sync/enrollment',
    files={'input_data': open('~/Purdue Class Schedules Summer 2019.csv')},
    data={'input_type': 'CSV', 'dry_run': True},
    headers={'X-TW-PersonId': '56761'}
)
rsp.status_code
200
rsp.json()
{'id': 138,
 'info': {'child_task_ids': [],
          'extra': {},
          'input_src_name': 'default',
          'message': '',
          'percentage_completed': 100.0,
          'status': 'completed',
          'warnings': []},
 'job_type': 'CourseDataSync',
 'last_update': '2019-09-17T11:47:17.671564+00:00',
 'person_id': 56761,
 'submitted_date': '2019-09-17T11:47:16.598450+00:00',
 'target_org_id': 3171}

We made it! Status is completed instead of failed for the first time and that means that we can be pretty sure our data is going to upload successfully to Teamworks. Now we repost with the additional parameter in data, 'dry_run': False

rsp = oauth.post(
    'https://api.teamworksapp.com/api/v1/academic_success/sync/enrollment',
    files={'input_data': open('~/Purdue Class Schedules Summer 2019.csv')},
    data={
        'input_type': 'CSV',
        'dry_run': False
    },
    headers={'X-TW-PersonId': '56761'}
)
rsp.status_code
200
rsp.json()
{'id': 140,
 'info': {'child_task_ids': [],
          'extra': {},
          'input_src_name': 'default',
          'message': '',
          'percentage_completed': 0.0,
          'status': 'pending',
          'warnings': []},
 'job_type': 'CourseDataSync',
 'last_update': '2019-09-17T11:49:47.750869+00:00',
 'person_id': 56761,
 'submitted_date': '2019-09-17T11:49:46.381840+00:00',
 'target_org_id': 3171}

This gives us a new status object that says status: pending, which means that the data is loading in the background. Take note of the job id, 5. Subsequent calls to /api/v1/academic_success/sync/enrollment/status/<jobid> will yield updated status. As soon as the status goes to failed or completed we can take stock.

rsp = oauth.get(
    'https://api.teamworksapp.com/api/v1/academic_success/sync/enrollment/status/140',
    headers={'X-TW-PersonId': '56761', 'X-TW-TeamId': '1353'}
)
pprint(rsp.json())
{'id': 140,
 'info': {'child_task_ids': ['27cba283-110d-4e1c-b108-18ada6fdd26d'],
          'extra': {},
          'input_src_name': 'default',
          'message': '',
          'percentage_completed': 100.0,
          'status': 'completed',
          'warnings': []},
 'job_type': 'CourseDataSync',
 'last_update': '2019-09-17T11:49:48.707341+00:00',
 'person_id': 56761,
 'submitted_date': '2019-09-17T11:49:46.381840+00:00',
 'target_org_id': 3171}

A completed status indicates that the sync has completed successfully and that the calendar has started building. Calendars can take an hour to build or more in some cases, depending on the overall system load. Currently there's no indication of when the calendar has finished provisioning, but a check as the org superuser into student's calendars should show the data.