Emailed report import

Using Dropbox email receiving in tandem with Middle, you can automate report imports into Hubspot.

1

Setup Dropbox email receiving

Email to Dropbox: Dropbox App CenterDropbox

2

Authenticate Dropbox in Middle

Dropbox

3

Map column headers to Hubspot properties

Crucial to this process is mapping the columns you'd like to import into Hubspot. It's helpful to create a spreadsheet of the columns in your report and the Hubspot properties these coorespond to. Make note of the Hubspot property types, especially if any of the properties are dates or datetimes. Dates and datetimes will need to be designated in the workflow to formatting.

4

Create a workflow

Create a workflow to facilitate your file import. Feel free to copy and paste the JSON template below to get a head start.

Workflows

5

Configure your workflow

The template below includes a set variable step (Step 1) where you'll need to set variable specific to your import. These inputs are as follows:

filter_phrase

This is a filter phrase for the workflow to filter for the reports you want to import in this workflow. The workflow is setup to use simple non-case-sensitive includes logic. This can be edited in step 2 of the template.

property_mapping

See the following doc on how Middle uses a colon-delimited list to map properties between your report and Hubspot:

File Import Action

It helps to use a concatenate function in your spreadsheet to generate the colon delimited list

unique_hubspot_property_name

This is the property you are using to reconcile identity between your report and your Hubspot object.

External ID properties

unique_property_column_header

The value that should be passed in from your report to the reconciliation property in Hubspot.

date_properties

A list of the column headers that are going to be imported to date properties in Hubspot.

datetime_properties

A list of the column headers that are going to be imported to datetime properties in Hubspot.

6

Save and test your workflow

Lock the workflow version to make it active and then test by going to your synced Dropbox records and manually executing one on your new workflow.

Typically we see column header and property validation errors. If you see an error like this, make sure what you entered into the workflow corresponds with your report and Hubspot properties.

7

Setup a workflow trigger

You'll likely want this workflow to trigger off of a newly synced Dropbox file.

File formats

Middle workflows can handle .bz2 and .csv file formats. If your dropbox file is in another format, reach out to [email protected] to discuss file conversion strategies.

Generic template

{"name": "Dropbox Report Import", "description": "", "steps": [{"step_number": 0, "description": "Begin workflow on Dropbox Folder content", "node_type": "root_node", "root": {"next_step_number": 1, "input_attribute": {"type": "object", "is_array": false, "children": [{"key": "input_record", "display_name": "Folder content", "type": "foreign_key", "is_array": false, "foreign_key_entity_uuid": "956cba57-d35a-4893-b100-50fbce57357b"}]}}}, {"step_number": 1, "description": "Field and filter configuration", "node_type": "set_variable_node", "setvariable": {"next_step_number": 2, "output_type": {"type": "object", "is_array": false, "children": [{"key": "date_properties", "display_name": "Date Field Name", "description": "The name of the date in the column header", "type": "string", "is_array": false}, {"key": "datetime_properties", "display_name": "Datetime Field Name", "description": "The name of the date in the column header", "type": "string", "is_array": false}, {"key": "filter_phrase", "display_name": "File Name Filter Phrase (leave blank if not needed)", "description": "The workflow will filter for Dropbox filters that INCLUDE this phrase (set up to not be case sensitive)", "type": "string", "is_array": false}, {"key": "property_mapping", "display_name": "Column Delimited Property Mapping (column header on left and Hubspot property on left)", "description": "See: https://docs.middle.app/middle-docs/guides/app-reference/hubspot/file-import-action", "type": "string", "is_array": false}, {"key": "unique_hubspot_property_name", "display_name": "Unique Hubspot Property Name", "description": "Internal name.", "type": "string", "is_array": false}, {"key": "unique_property_column_header", "display_name": "Unique Property Column Header", "description": "Name of column in header.", "type": "string", "is_array": false}]}, "output_np": {"type": "construct_object", "expression": "{}", "params": [{"key": "filter_phrase", "type": "template"}, {"key": "property_mapping", "type": "template", "expression": "column_header:hubspot_property"}, {"key": "date_properties", "type": "template"}, {"key": "datetime_properties", "type": "template"}, {"key": "unique_property_column_header", "type": "template"}, {"key": "unique_hubspot_property_name", "type": "template"}]}}}, {"step_number": 2, "description": "Is there no report filter or does the file name include the filter phrase?", "node_type": "filter_node", "filter": {"filter_condition": {"type": "or", "params": [{"type": "comparison", "expression": "VALUE_IS_FALSEY", "params": [{"type": "global_var_selector", "expression": "WORKFLOW:1", "props": ["filter_phrase"]}]}, {"type": "comparison", "expression": "STRING_INCLUDES", "params": [{"type": "upper", "params": [{"type": "global_var_selector", "expression": "WORKFLOW:0", "props": ["input_record", "name"]}]}, {"type": "upper", "params": [{"type": "global_var_selector", "expression": "WORKFLOW:1", "props": ["filter_phrase"]}]}]}]}, "next_step_number_on_true": 3}}, {"step_number": 3, "description": "Get download URL from file path", "node_type": "action_node", "action": {"action_input": {"type": "construct_object", "expression": "{}", "params": [{"key": "file_path", "type": "global_var_selector", "expression": "WORKFLOW:0", "props": ["input_record", "path_lower"]}]}, "action_external_id": "6c058cd7-4a5c-48fc-8b4c-4c0750903f7c", "app_connection_id": 353, "next_step_number": 4}}, {"step_number": 4, "description": "Import file into Hubspot", "node_type": "action_node", "action": {"action_input": {"type": "construct_object", "expression": "{}", "params": [{"key": "s3_presigned_url", "type": "global_var_selector", "expression": "WORKFLOW:3", "props": ["link"]}, {"key": "file_name", "type": "global_var_selector", "expression": "WORKFLOW:0", "props": ["input_record", "name"]}, {"key": "import_name", "type": "template", "expression": "{} {}", "params": [{"key": "import_name", "type": "global_var_selector", "expression": "WORKFLOW:0", "props": ["input_record", "name"]}, {"type": "global_var_selector", "expression": "NOW"}]}, {"key": "record_type", "type": "template", "expression": "Deal"}, {"key": "import_type", "type": "template", "expression": "UPSERT"}, {"key": "optional_formatting", "type": "boolean", "expression": "BOOLEAN_IS_TRUE"}, {"key": "date_column_names", "type": "splitlines", "params": [{"key": "date_column_names", "type": "global_var_selector", "expression": "WORKFLOW:1", "props": ["date_properties"]}]}, {"key": "datetime_column_names", "type": "splitlines", "params": [{"type": "global_var_selector", "expression": "WORKFLOW:1", "props": ["datetime_properties"]}]}, {"key": "field_definitions_string", "type": "global_var_selector", "expression": "WORKFLOW:1", "props": ["property_mapping"]}, {"key": "is_bz2_file", "type": "boolean", "expression": "BOOLEAN_IS_TRUE"}, {"key": "reconciliation_pair_string", "type": "template", "expression": "{}:{}:{}", "params": [{"type": "global_var_selector", "expression": "WORKFLOW:1", "props": ["unique_property_column_header"]}, {"type": "global_var_selector", "expression": "WORKFLOW:1", "props": ["unique_hubspot_property_name"]}, {"type": "switch", "params": [{"type": "comparison", "expression": "STRING_IS_IN", "params": [{"type": "global_var_selector", "expression": "WORKFLOW:1", "props": ["unique_hubspot_property_name"]}, {"type": "splitlines", "params": [{"type": "template", "expression": "email\nrecord_id"}]}]}, {"type": "template", "expression": "PRIMARY"}, {"type": "template", "expression": "SECONDARY"}]}]}]}, "action_external_id": "f9773493-f8fd-4f50-9d5c-25bbafa44fac", "app_connection_id": 352}}]}