Making Use of the HarmonySite API

Here at the London Welsh Rugby Club Choir, we are enthusiastic users of HarmonySite. Recently, we have been making use of the API that it provides to provide some useful reports for our committee meetings, and event planning.

There is a whole host of ways to use the API, but we usually use a language called Python inside a notebook from a free Google service called Colab; we have provided some instructions which should get you started below.

Prerequisites: What do you need?

• ideally some python knowledge, but anyone with some experience of almost any programming should be able to handle it. Without that, a logical mind should get you there!
• a google account (you already have that if you have a gmail adddress)
• a login and password with "Data Admin" access for your website. Your webmaster (YOUR webmaster, not harmonysite) should be able to set this up for you. You should have a specific login just for this - anything else is bad security practice.

Getting Started

1. Create a Notebook

Goto https://colab.research.google.com/ and use "File" -> "New Notebook". You can change the name if you wish up at the top left (it will be "Untitled " at the moment).

2. Use the package we wrote to simplify access

# get the package (because it's not in the default google colab runtime)
!pip install harmonysite
from harmonysite import HarmonySite

3. Check it's installed

4. Check your access works

hs = HarmonySite.build('https://your_web_site/api','login','password')
list(hs.browse("events"))

Replace the website, login and password with your website and login and password you got set up earlier. You still need the quotation marks. Now do "Runtime" -> "Run All" again.

You should see a (very messy) list of the information for the first 5 events that were entered onto your harmonysite.

An aside

If you are comfortable with python, the browse method provides you with a generator. You can now use standard python to do whatever you like with the data. The method signature is def browse(table, page_number=0, page_size=5). Knock yourself out.

However, we usually make use of a data analysis tool called "pandas", which provides a neat way of formatting the data for output, merging data togther etc.

5. Get prettier output with Pandas

import pandas as pd


def dataframe(table_name, filterColumns=None, idColumn='id'):
    """returns a dataframe for a complete table
    not part of standard package, to avoid unnecessary dependency
    """
    df = pd.DataFrame.from_records(
         hs.browse(table_name, page_number=0, page_size=9999999),
         index=idColumn
    )
    if filterColumns:
        return df.filter(filterColumns)
    return df


events = dataframe('events')
display(events)

You should see a reasonably formatted table of all the events in your harmonysite. The "def dataframe" bit adds a function to your notebook which can get any table from your harmonysite into a pandas dataframe. You just used it to pull all your events into a variable called "events", and then displayed it (and yes, I know there are shorter ways to do these things in a notebook, but this isn't a jupyter or python tutorial).

6. Do more with pandas

#  only get the columns we need
events = dataframe('events', ['Title', 'start', 'Category', 'MemberTime','Food','Parking', 'Fee', 'Coordinator'])

# replace the "Category" column with the actual name of the category, not just a number
events = pd.merge(events, dataframe('eventcats', ['Name']), how='left', left_on='Category', right_index=True)
events = events.drop('Category', axis='columns').rename(columns={'Name': 'Category'})

events.style.hide_index()

What you have just done is to subselect columns, and then to merge two tables together. This is just the start of what you can do with pandas.

Next Steps

Our suggestion would be to start again with a new notebook, and just put the following into the first cell. Don't forget to correct the website, login and password...

# get the package (because it's not in the default google colab runtime)
!pip install harmonysite

from harmonysite import HarmonySite
import pandas as pd

hs = HarmonySite.build('https://website/api','login','password')

def dataframe(table_name, filterColumns=None, idColumn='id'):
    """returns a dataframe for a complete table
    not part of standard package, to avoid unnecessary dependency
    """
    df = pd.DataFrame.from_records(
         hs.browse(table_name, page_number=0, page_size=9999999),
         index=idColumn
    )
    if filterColumns:
        return df.filter(filterColumns)
    return df

And start working with pandas from there. We normally find it satisfactory to just copy and paste fropm a notebook into emails for reporting, although there is nothing to stop you generating any files if you want to.