-
Notifications
You must be signed in to change notification settings - Fork 0
Plugin Hubspot
FlorianSf edited this page Nov 17, 2023
·
3 revisions
https://developers.hubspot.com/docs/api/working-with-oauth
https://developers.hubspot.com/docs/api/oauth/tokens
Token information, replace [token] curl --request GET --url https://api.hubapi.com/oauth/v1/access-tokens/[token]
For Hubspot it is important to differentiate between private apps, which don't support oAuth, but just plain access tokens and the apps in the public marketplace which support oauth.
Currently the access token has no expiration so it will be refreshed when the access token expired and a call is done
This implementation works well with all v3 CRM-APIs
Please ask apteco to go through this process
# Import module
Import-Module aptecopsframework -Verbose
# Load plugins
#Add-PluginFolder -Folder "D:\Scripts\AptecoPSFramework_Plugins"
#Register-Plugins
# Choose plugin and install it
$plugin = get-plugins | where-object { $_.name -like "*Hubspot*" }
Install-Plugin -Guid $plugin.guid
Import-Plugin -Guid $plugin.guid
# Get settings for this plugin and change some
$settings = Get-settings
$settings.logfile = ".\file.log"
# Set the settings
Set-Settings -PSCustom $settings
# Create a token for hubspot and save the path to it
$tokenFile = ".\hs.token"
$tokenSettings = ".\hs_token_settings.json"
Request-Token -SettingsFile $tokenSettings -TokenFile $tokenFile -UseStateToPreventCSRFAttacks
# Save the settings into a file
$settingsFile = ".\settings.json"
Export-Settings -Path $settingsFile# Import module
Import-Module aptecopsframework -Verbose
# Load plugins
#Add-PluginFolder -Folder "D:\Scripts\AptecoPSFramework_Plugins"
#Register-Plugins
# Choose plugin and install it
$plugin = get-plugins | where-object { $_.name -like "*Hubspot*" }
Install-Plugin -Guid $plugin.guid
Import-Plugin -Guid $plugin.guid
# Get settings for this plugin and change some
$settings = Get-settings
$settings.logfile = ".\file.log"
# Set the settings
Set-Settings -PSCustom $settings
# Save a token for hubspot and save the path to it
Save-PrivateAppToken -TokenFile ".\hs.token"
# Save the settings into a file
Export-Settings -Path ".\settings.json"# Get properties of contacts
get-property -Object contacts | Out-GridView
# This returns all properties automatically
get-crmdata -Object contacts -LoadAllProperties
# This returns only selected properties - Hubspot always delivers hs_object_id, lastmodifieddate and createdate with it
get-crmdata -Object contacts -Properties anrede, email -limit 3
anrede createdate email hs_object_id lastmodifieddate
------ ---------- ----- ------------ ----------------
Herr 2020-06-18T06:40:47.992Z ex.ample@example.com 41101 2023-10-19T14:39:15.495Z
2019-03-14T16:17:54.478Z abcdef.ddfasdf@ggg.de 108802 2023-08-07T09:50:06.778Z
2019-03-14T16:17:54.507Z lkdhsa.aslsie@abc.de 108804 2023-08-07T09:50:09.493Z
# Load all records with associations, so for every extension you add on the list, a new column will be created
# and it will be either $null or filled with an id
Get-CRMData -Object contacts -LoadAllRecords -Properties firstname -Associations companies -ExtendAssociations "contact_to_company"
createdate hs_object_id lastmodifieddate contact_to_company
---------- ------------ ---------------- ------------------
2020-06-18T06:40:47.992Z 41101 2023-10-19T14:39:15.495Z 7981616015
2019-03-14T16:17:54.478Z 108802 2023-08-07T09:50:06.778Z
2019-03-14T16:17:54.507Z 108804 2023-08-07T09:50:09.493Z
2019-03-14T16:17:54.457Z 108852 2023-10-16T14:25:46.942Z 10944278300
2019-03-14T16:17:54.490Z 109251 2023-08-07T10:30:02.657Z
2019-03-14T16:17:54.858Z 109552 2023-08-07T10:30:12.820Z
2019-03-14T16:17:54.938Z 109602 2023-08-07T09:50:19.458Z
2019-04-04T09:00:28.040Z 173101 2023-08-07T10:30:01.473Z 9015900279
2019-04-16T09:23:55.665Z 185901 2023-08-07T10:30:13.103Z
2019-04-16T12:59:13.701Z 200652 2023-10-13T09:56:21.754Z 4764279460
# To get the wrapped data (like it is sent from hubspot) just add the flag -AddWrapper, shwon as a list
get-crmdata -Object contacts -Properties anrede, email -limit 3 -AddWrapper | fl
id : 41101
properties : @{anrede=Herr; createdate=2020-06-18T06:40:47.992Z; email=ex.ample@example.com; hs_object_id=41101; lastmodifieddate=2023-10-19T14:39:15.495Z}
createdAt : 2020-06-18T06:40:47.992Z
updatedAt : 2023-10-19T14:39:15.495Z
archived : False
id : 108802
properties : @{anrede=; createdate=2019-03-14T16:17:54.478Z; email=abcdef.ddfasdf@ggg.de; hs_object_id=108802; lastmodifieddate=2023-08-07T09:50:06.778Z}
createdAt : 2019-03-14T16:17:54.478Z
updatedAt : 2023-08-07T09:50:06.778Z
archived : False
id : 108804
properties : @{anrede=; createdate=2019-03-14T16:17:54.507Z; email=lkdhsa.aslsie@abc.de; hs_object_id=108804; lastmodifieddate=2023-08-07T09:50:09.493Z}
createdAt : 2019-03-14T16:17:54.507Z
updatedAt : 2023-08-07T09:50:09.493Z
archived : False
# To get the associations with the record, just add the -associations parameter like here, shown as json to show the nesting
get-crmdata -Object contacts -Properties anrede, email -limit 3 -addwrapper -Associations Companies, Contacts -verbose | ConvertTo-Json -Depth 99
VERBOSE: GET
https://api.hubapi.com/crm/v3/objects/contacts?archived=False&properties=anrede,email&limit=3&associations=Companies,Co
ntacts with 0-byte payload
VERBOSE: received -1-byte response of content type application/json;charset=utf-8
[
{
"id": "41101",
"properties": {
"anrede": "Herr",
"createdate": "2020-06-18T06:40:47.992Z",
"email": "[email protected]",
"hs_object_id": "41101",
"lastmodifieddate": "2023-10-19T14:39:15.495Z"
},
"createdAt": "2020-06-18T06:40:47.992Z",
"updatedAt": "2023-10-19T14:39:15.495Z",
"archived": false,
"associations": {
"companies": {
"results": [
{
"id": "7981616015",
"type": "contact_to_company"
},
{
"id": "7981616015",
"type": "contact_to_company_unlabeled"
}
]
}
}
},
...
# To load just active records ids (to sort out the deleted ones without receiving a delete webhook)
( get-crmdata -Object contacts -LoadAllRecords -AddWrapper -verbose ).id
# To filter/search for data, define a filter first and then use it
# This is a basic use of the filter which does not allow multiple filtergroups at the moment
# If you need multiple filter groups, just let us know or create a pull request
# Good source for possible filters is here: https://developers.hubspot.com/docs/api/crm/contacts and search for "search"
# These properties will automatically returned, when using filters: https://developers.hubspot.com/docs/api/crm/search
$filter = [Array](
[Ordered]@{
"propertyName"="hubspotscore"
"operator"="GTE"
"value"="0"
}
)
get-crmdata -Object contacts -Limit 10 -verbose -Filter $filter -Sort hubspotscore -properties email, firstname, lastname
# Or to load all records for that filter (hubspot only allows 100 records per request, and only 4 searches per second so it can take a while)
$c = get-crmdata -Object contacts -verbose -Filter $filter -Sort hubspotscore -properties email, firstname, lastname -LoadAllRecords
$c | out-gridview
# Load all lists
Get-List -LoadAllLists
# Search for a list
Get-List -Query "Free"
# Add a member to a list (this needs to be the ILS-List-ID)
# You get a status in the result in recordIdsAdded and recordIdsMissing
# Already existing members on that list are not listed in the result
Add-ListMember -ListId 355 -AddMemberships 463451
# Add multiple members to a list
Add-ListMember -ListId 355 -AddMemberships 463451, 456
# Get ListMember
Get-ListMember -ListId 355
# Remove ListMember
Remove-ListMember -ListId 355 -AddMemberships 463451
The first command is limited to ten records, but shows you the principles
get-crmdata -Object deals -limit 10 -Associations company, contact -ExtendAssociations deal_to_company, deal_to_contact
# returns
createdate hs_lastmodifieddate hs_object_id deal_to_company deal_to_contact
---------- ------------------- ------------ --------------- ---------------
2022-03-22T09:31:27.758Z 2022-08-01T09:20:01.075Z 8308832420 8139932182
2022-03-23T12:55:22.010Z 2022-08-01T09:19:36.531Z 8321280898 7805725247
2022-03-23T12:51:40.994Z 2023-09-19T08:00:02.266Z 8321289886 8139953811 2570651
2022-03-23T12:55:22.010Z 2022-08-01T09:17:22.741Z 8321290506 7805730879
2022-03-23T12:55:22.010Z 2022-08-11T14:55:32.743Z 8321307569 7805731256
2022-03-23T13:06:01.448Z 2022-09-30T12:24:04.851Z 8321309317 7672437093
2022-03-23T12:51:40.994Z 2022-08-01T09:19:51.080Z 8321316167 7805731377
2022-03-23T13:06:01.448Z 2022-12-12T11:11:10.891Z 8321335118 6224004504
2022-03-23T13:06:01.448Z 2023-10-30T11:31:26.853Z 8321351524 6256620684 1764205
2022-03-23T13:06:01.448Z 2023-06-27T08:21:16.582Z 8321361808 3324258237 1591401
So, to load companys, contacts, deals and engagements into files you could simply do
Import-Module aptecopsframework
import-settings
get-crmdata -Object companies -loadallrecords -loadallproperties | Export-csv -Path ".\companies.csv" -Delimiter "`t" -Encoding UTF8 -NoTypeInformation
get-crmdata -Object deals -loadallrecords -loadallproperties -Associations company, contact -ExtendAssociations deal_to_company, deal_to_contact | Export-csv -Path ".\deals.csv" -Delimiter "`t" -Encoding UTF8 -NoTypeInformation
get-crmdata -Object contacts -loadallrecords -loadallproperties -Associations company -ExtendAssociations contact_to_company | Export-csv -Path ".\contacts.csv" -Delimiter "`t" -Encoding UTF8 -NoTypeInformation
get-crmdata -Object deals -loadallrecords -loadallproperties -Associations company, contact -ExtendAssociations deal_to_company, deal_to_contact | Export-csv -Path ".\deals.csv" -Delimiter "`t" -Encoding UTF8 -NoTypeInformation
get-crmdata -Object notes -loadallrecords -loadallproperties -Associations company, contact -ExtendAssociations note_to_company, note_to_contact | Export-csv -Path ".\notes.csv" -Delimiter "`t" -Encoding UTF8 -NoTypeInformation
get-crmdata -Object meetings -loadallrecords -loadallproperties -Associations company, contact -ExtendAssociations meeting_to_company, meeting_to_contact | Export-csv -Path ".\meetings.csv" -Delimiter "`t" -Encoding UTF8 -NoTypeInformation
get-crmdata -Object tasks -loadallrecords -loadallproperties -Associations company, contact -ExtendAssociations task_to_company, task_to_contact | Export-csv -Path ".\tasks.csv" -Delimiter "`t" -Encoding UTF8 -NoTypeInformation
get-crmdata -Object calls -loadallrecords -loadallproperties -Associations company, contact -ExtendAssociations call_to_company, call_to_contact | Export-csv -Path ".\calls.csv" -Delimiter "`t" -Encoding UTF8 -NoTypeInformation
For a more advanced usecase we summarize some things, make some loops and use a sqlite database to store the data:
try {
#-----------------------------------------------
# PREPARATION
#-----------------------------------------------
# Load module, settings and plugin
Set-Location -Path "D:\Apteco\Build\Hubspot\preload\HubspotExtract_v2" #"D:\Scripts\AptecoPSFramework_HubspotLoadData"
Import-Module AptecoPSFramework, SimplySql, WriteLog, SqlPipeline
Import-Settings "D:\Scripts\AptecoPSFramework_HubspotLoadData\settings.yaml"
# Hubspot settings
$objectsToLoad = [array]@(
"companies"
"contacts"
"deals"
"notes"
"meetings"
"tasks"
"calls"
)
# Database settings
$dbname = "D:\Apteco\Build\Hubspot\data\hubspot.sqlite"
$backupDb = "$( $dbname ).$( [datetime]::Now.toString("yyyyMMddHHmmss") )"
# Other settings
Set-Logfile ".\hubspot.log"
# Current time
$startTime = [DateTime]::Now
#-----------------------------------------------
# CHECK DATABASE
#-----------------------------------------------
# Rename existing database
If ( ( Test-Path -Path $dbname ) -eq $true ) {
Move-Item -Path $dbname -Destination $backupDb
}
# Make connection to a new database
Open-SQLiteConnection -DataSource $dbname
#-----------------------------------------------
# LOAD HUBSPOT DATA AND LOAD INTO NEW DATABASE
#-----------------------------------------------
#[int]$recordsInsertedTotal = 0
Write-Log "Loading and inserting data"
$objectsToLoad | ForEach-Object {
# Use the current object and reset the counter
$object = $_
# Load data from Hubspot
Get-CRMData -Object $object -LoadAllProperties -AddWrapper -LoadAllRecords -Associations companies, contacts -IncludeObjectName | Add-RowsToSql -TableName "items" -UseTransaction -CreateColumnsInExistingTable -FormatObjectAsJson
}
#-----------------------------------------------
# LOAD PROPERTIES
#-----------------------------------------------
Write-Log "Loading and inserting properties"
$objectsToLoad | ForEach-Object {
# Use the current object and reset the counter
$object = $_
# Load data from Hubspot
Get-Property -Object $object -IncludeObjectName | Add-RowsToSql -TableName "properties" -UseTransaction -CreateColumnsInExistingTable -FormatObjectAsJson
}
#-----------------------------------------------
# CHECK AND CLOSE CONNECTION
#-----------------------------------------------
$itemsCount = Invoke-SqlQuery "select object, count(*) as count from items group by object order by count desc" #"SELECT count(*) FROM items"
Write-Log "Confirmed $( ( $itemsCount | measure count -sum ).Sum ) items in total"
$itemsCount | ForEach-Object {
Write-Log " $( $_.object ): $( $_.count )"
}
$propertiesCount = Invoke-SqlQuery "select object, count(*) as count from properties group by object order by count desc"
Write-Log "Confirmed $( ( $propertiesCount | measure count -sum ).Sum ) properties in total"
$propertiesCount | ForEach-Object {
Write-Log " $( $_.object ): $( $_.count )"
}
#-----------------------------------------------
# MEASURE
#-----------------------------------------------
$ts = New-TimeSpan -Start $startTime -End ( [DateTime]::Now )
Write-Log "Needed $( $ts.TotalSeconds ) in total" -severity INFO
#-----------------------------------------------
# REMOVE DATABASE
#-----------------------------------------------
# Rename existing database
If ( ( Test-Path -Path $backupDb ) -eq $true ) {
Remove-Item -Path $backupDb
}
#-----------------------------------------------
# GENERATE QUERIES TO USE
#-----------------------------------------------
$objectsToLoad | ForEach-Object {
# Use the current object and reset the counter
$object = $_
$objectProperties = Invoke-SqlQuery -Query "SELECT name, label FROM properties where object = '$( $object )'" #| Out-GridView
$propsList = [System.Collections.ArrayList]@()
$objectProperties | ForEach-Object {
[void]$propsList.add(" json_extract(i.properties, '$.$( $_.name )') ""$( $_.label.replace('"','').replace("'",'') )""")
}
# General query for the object/table
$queryString = [System.Text.StringBuilder]::new()
[void]$queryString.Append( "SELECT" )
[void]$queryString.AppendLine( " id, createdAt, updatedAt, archived," )
[void]$queryString.AppendLine( "$(( $propsList -join ", `r`n" ))" )
[void]$queryString.AppendLine( "FROM items i where object = '$( $object )'" )
$queryString.toString() | Set-Content ".\query_$( $object ).sql" -Encoding UTF8 -force
# Lookup queries
$queryLookups = @"
SELECT p.name
, json_extract(j.value, '$.value') code
, json_extract(j.value, '$.label') description
FROM properties p
, json_each(p.options) j
WHERE p.OBJECT = '$( $object )'
AND p.fieldType = 'select'
AND json_extract(j.value, '$.value') IS NOT NULL
AND json_extract(j.value, '$.value') != ''
AND json_extract(j.value, '$.hidden') = 0
AND p.name IS NOT NULL
ORDER BY name
, json_extract(j.value, '$.displayOrder')
"@
$objectLookups = Invoke-SqlQuery -Query $queryLookups
$objectLookups | group name | ForEach-Object {
$name = $_.name
$queryLookups -replace ("p.name IS NOT NULL", "p.name = '$( $name )'") | Set-Content ".\lookup_$( $object )_$( $name ).sql" -Encoding UTF8 -force
}
}
} catch {
Write-Log $_.Exception -severity ERROR
throw $_.Exception
} finally {
#-----------------------------------------------
# CLOSE THE CONNECTION
#-----------------------------------------------
Close-SqlConnection
}