HubSpot - Getting started in Peliqan

# This script gets all fields (properties) of a given object in Hubspot, that are of type "Select" (Drop down),
# And stores these fields in a table in the DWH, and the options in a second table.

object_type = 'contacts'
schema = 'hubspot_v2'

hubspotv2_api = pq.connect('HubSpot V2')
dbconn = pq.dbconnect(pq.DW_NAME)

response = hubspotv2_api.get('object_properties', object_type = object_type)
fields = response["results"]
select_fields = []
select_field_options = []
for field in fields:
    if field['fieldType'] == 'select':
        if not field['name'].startswith('hs_'): # exclude Hubspot standard fields (remove this line to include them)
            if field['options']:
                options = field['options']
                for option in options:
                    option['property_name'] = field['name']
                    option['property_label'] = field['label']
                    option['id'] = field['name'] + '_' + option['value']
                select_field_options.extend(options)
            del field['options']
            select_fields.append(field)

st.header(f"Hubspot select fields for {object_type}")
st.subheader("Select fields (drop downs)")
st.write(select_fields)

st.subheader("Select field options")
st.write(select_field_options)

table = f"properties_select_{object_type}"
dbconn.write(schema, table, records = select_fields, pk = 'name')

table = f"properties_select_options_{object_type}"
dbconn.write(schema, table, records = select_field_options, pk = 'id')