Use Case Scenario:
A company uses Google Big Query to store their data. These data are updated by an application daily. The company wants to automatically create a ticket in HubSpot when a data has meet certain criteria. To accomplish this, we will create a Python script that will pull the data from BigQuery and connect to HubSpot using HubSpot Ticket API to create a ticket.
HubSpot is a popular platform for marketing, sales, and customer service. It offers a rich set of features and integrations that can help businesses grow and optimize their workflows. One of the ways to access and leverage HubSpot’s capabilities is through its API, which allows developers to programmatically interact with HubSpot data and services.
In this tutorial, we will create a python script that will retrieve data from Google BigQuery table and send it to Hubspot to automatically create a ticket using Hubspot Ticket API Endpoint
Install the following Client libraries in your local terminal – Google BigQuery, Hubspot API Python client library, Google OAuth2 client library
pip install –upgrade google-cloud-bigquery
pip install –upgrade hubspot-api-client
pip install –upgrade google-authInitialize the connections
from google.cloud import bigquery
from google.oauth2 import service_account
from hubspot import HubSpot
credentials = service_account.Credentials.from_service_account_file(“PATH TO YOUR JSON FILE”)
client = bigquery.Client(credentials= credentials,project=’PROJECT ID’)
url_create_ticket = “https://api.hubapi.com/crm-objects/v1/objects/tickets”
headers = {
‘content-type’: ‘application/json’,
‘authorization’: ‘Bearer XXXX-XXXX-XXX-XXXX’
}Get the data from BigQuery Table and send to Hubspot for ticket creation
query_data = client.query(“””
SELECT x_subject, x_description, x_priority
FROM testdataset.test_table_A
WHERE x_priority != ‘Low’
“””)
data_results = query_data.result()
for row in data_results:
test_subject = format(row.x_subject)
test_description = format(row.x_description)
test_status = format(row.x_priority)
data = [
{
“name”: “subject”,
“value”: test_subject
},
{
“name”: “content”,
“value”: test_description
},
{
“name”: “priority”,
“value”: test_status
},
{
“name”: “hs_pipeline”,
“value”: 0
},
{
“name”: “hs_pipeline_stage”,
“value”: 1
}
]
response = request(“POST”, url_create_ticket, headers=headers, json=data)
print(response.text)
