Dynamic & Interactive Org chart with Smartsheet data as backend - Using Python and d3.js

Smaller teams and organizations at times struggle when it comes to building a customized dynamic organization / team chart / structure. There are a lot of tools and plugins available online which provide a good visualization but they lack the data management aspects. Some of them need quite a lot of manual efforts.

Here is my attempt to address some of these issues and making it easy for smaller teams to build their own organization chart.

The solution relies on leveraging an existing employee / team data (in this example smartsheets), massaging it and rendering with good interactive visualization using d3.js

Key Features of this solution

1. Profile image
2. Multilevel hierarchy
3. Integration with smartsheet to dynamically pull data and profile images
4. Detailed profile view
5. Tags for managing skills tagging
6. Search / Locate feature
7. Full Screen feature
8. Expand / Collapse feature
9. Scalable - Ability to include more fields / tags
10. Customization feasibility - UI and color scheme can be customized
11. Maintainability - Data can be managed easily in smartsheet. Team Leads can collaborate to maintain their teams data easily using smartsheet's collaboration features 
12. Extensibility - Can be extended to leverage other data sources e.g. json, flat file, structured databases, spreadsheet

Smartsheets are really awesome when it comes to managing data and collaborating across different teams. It comes very handy when you need to manage data in spreadsheet format and at the same time want multiple contributors to add / update to it simultaneously. However when it comes to visualizations, smartsheet lacks a bit. They have a good card view, but its not very intuitive and do not produce a very good visualization.

d3.js provides all capabilities to render an good interactive visualization of your data.

In my example, I have used python to build a small application that integrates with smartsheets, pulls the needed data, transforms it for consumption by d3.js where it can be further rendered as an interactive org chart.

Smartsheets provides API to programmatically access and manage smartsheet data. There is smartsheet python SDK which can be used to connect to smartsheets using your python applications.

This way teams and HRs can manage their team details and changes in smartsheet at their convenience in spreadsheet format including their profile photos and not to worry about the visualization.

Here is a code snippet for python application to convert smartsheet data into needed json format for further consumption for rendering org chart. Its bit crude, but produces the desired outcome

import smartsheet
import json
import requests

# Define a recursive function to add elements to Org Chart json data object
# If input is a dictionary and parent id is matching, add child elements directly
# If input is a list, iterate through the list to find the parent and add child elements to corresponding parent
def find_and_add_child(OrgData,Child):
# If argument is a dictionary data, add child record if parent id matches id of the element
   if type(OrgData) is dict:
# If parent id matches, append a children to parent
      if OrgData['id']==Child['parentid']:
         OrgData['children'].append(Child)
         return
# If parent id doesn't match and element has children within it, recursively look for a match
      if 'children' in OrgData:
         find_and_add_child(OrgData['children'],Child)
      else:
         return
# If the argument is a list, iterate through it to find a match
   else:
      for item in OrgData:
# If parent id matches, append child to item
         if item['id']==Child['parentid']:
# If child element doesn't exist, crete the key first and then append child data to it
            if 'children' in item:
               item['children'].append(Child)
            else:
               item['children'] = []
               item['children'].append(Child)
            return
# If parent id doesn't match and element has children within it, recursively look for a match
         if 'children' in item:
            find_and_add_child(item['children'], Child)
      return


# Define Smartsheet id and bearer token for Smartsheet integration
sheet_id=input("Provide smartsheet sheet id: ")
access_token=input("Provide smartsheet API bearer token: ")

# Define column indexes for required columns
# This should be based on structure of your data in smartsheet
name_index=0
image_index=1
team_index=5
role_index=3
skills_index=7

# Define output json data object
OrgChartData = {}
OrgChartData['children']=[]

# Set id and parentid to 0 initially
id=0
parentid=0

# Initiate Smartsheet connection and pull entire sheet into a json object
smartsheet_client = smartsheet.Smartsheet(access_token)
sheet = smartsheet_client.Sheets.get_sheet(sheet_id)
sheet_str=str(sheet)
sheet_json=json.loads(sheet_str)
with open('sheet_output.json', 'w') as file:
   json.dump(sheet_json,file)
print(sheet.name)

# Parse through smartsheet output json data and fetch needed details
for row in sheet_json['rows']:
   rowid=row['id']
   name=row['cells'][name_index]['value']
   team=row['cells'][team_index]['value']
   role=row['cells'][role_index]['value']
   skills=row['cells'][skills_index]['value']
# If there is an image element, pull the imageurl using the smartsheet api
   if 'image' in row['cells'][image_index]:
      imageid=row['cells'][image_index]['image']['imageId']
      height=row['cells'][image_index]['image']['height']
      width=row['cells'][image_index]['image']['width']
      imageUrl=smartsheet.models.ImageUrl(
      {
          "imageId": imageid,
             "height": height,
             "width": width
      }
      )
      response = smartsheet_client.Images.get_image_urls([imageUrl])
      url = response.image_urls[0].url

      #download the image nad place in images folder
      response = requests.get(url)
      if response.status_code == 200:
         filename="images/%s.jpg"%rowid
         print(filename)
         with open(filename, 'wb') as f:
            f.write(response.content)
            url=filename

# If there is no image element, use a placeholder
   else:
      imageid=0
      # Add placeholder image
      url="images/placeholder.png"

# Append to output json data
# If there its a child level element, it will have a parent id, so add accordingly
   if 'parentId' in row:
      parentid=row['parentId']
      child_data={
         "id": rowid,
         "name": name,
         "imageUrl": url,
         "area": team,
         "profileUrl": "",
         "office": "",
         "tags": skills,
         "isLoggedUser": 'false',
         "unit": {
            "type": "",
            "value": ""
         },
         "positionName": role,
         "parentid": parentid
      }
# Call to recursive function to add child data to appropriate parent
      find_and_add_child(OrgChartData,child_data)

# If its a top level element, it won't have a parent key, so set it to 0
   else:
      parentid=0

# Add top parent record in json data object
      OrgChartData = {
         "id": rowid,
         "name": name,
         "imageUrl": url,
         "area": team,
         "profileUrl": "",
         "office": "",
         "tags": skills,
         "isLoggedUser": 'false',
         "unit": {
            "type": "",
            "value": ""
         },
         "positionName": role,
         "parentid": parentid,
         "children":[]
         }

  # print("ID: ",rowid,"\tName: ",name,"\Skills: ",skills,"\tImage URL: ",url,"\tTeam: ",team,"\tRole: ",role,"\tParent ID: ",parentid)

# Dump the json data to a file for further consumption by d3.js for rendering Org Chart
with open('data/OrgChartData.json', 'w') as outfile:
    json.dump(OrgChartData, outfile)


# Use this OrgChartData.json file in d3.js to render the interactive Org Chart

Hope my first attempt to python is meaningful :)