Freshdesk Sharepoint Integration using Powershell
Freshdesk to Sharepoint with Powershell - Part 1
Sharepoint provides a simple way to create multiple views of data for different purposes using lists. Freshdesk contains our open ticket data with a display that is not always optimal for managing tickets and agents.
This set of scripts updates a familiar Sharepoint list with the Freshdesk open ticket information. Sharepoint is used to view and share information in a simple readable form. The list is updated each 5-minutes providing a near real-time view of ticket information.
Once setup, the Sharepoint list can be modified to create the simple views of tickets by Customer, by Engineer, By Call Type, By Logged time. Updates to the data are made with a hyperlink back to the actual Freshdesk ticket for updating with new information as needed.
The scripting comes in two parts for simplicity.
- Part one downloads all open tickets to a .csv file used for Sharepoint input.
- Part two updates the Sharepoint list with ticket information and removes resolved tickets.
This script will simply copy and paste into the Powershell IDE.
# ########################################
# Grab open FreshDesk tickets to .csv file
#
# Part one - Sharepoint integration
# By Mark D. Wilkinson
# ########################################
Remove-Variable * -ErrorAction SilentlyContinue
# Insert your API Key from your Freshdesk login profile 'Your API Key'
$FDApiKey = "Replace this with Your API Key "
# Enter your company URL here
$CompanyURL = "Replace this with Your Company Freshdesk URL i.e. https://company.freshdesk.com"
# Default path is the script path
$OutputPath = "$PSScriptRoot\OpenTickets.csv"
# Params for Freshdesk Access
$pair = "$($FDApiKey):$($FDApiKey)"
$bytes = [System.Text.Encoding]::ASCII.GetBytes($pair)
$base64 = [System.Convert]::ToBase64String($bytes)
$basicAuthValue = "Basic $base64"
$FDHeaders = @{ Authorization = $basicAuthValue }
[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::TLS12
# Download 30 tickets at a time until done
Write-Host "Downloading Unresolved Tickets"
$Counter=1
Do {
$FDBaseEndpoint = "$CompanyURL/helpdesk/tickets/filter/unresolved?format=json&page=$Counter" # All unresolved tickets
$FDDownloadData = Invoke-WebRequest -uri $FDBaseEndpoint -Headers $FDHeaders -Method GET -ContentType application/json | ConvertFrom-Json
$FDContactData = $FDContactData + $FDDownloadData
Write-Host " Downloaded" $FDDownloadData.count "tickets"
$Counter++
}
Until($FDDownloadData.count -lt 30)
# Output to csv for later use
$FDContactData | Export-csv $OutputPath
Write-Host " Downloaded" $FDContactData.count "tickets into csv file" $OutputPath
# Script Ends
Test out using the Powershell IDE
Once running create a scheduled task running the script each five minutes, keeping the .csv file updated with Freshdesk open tickets.
Next Steps Coming in Part 2
- In Part 2 we will used the .csv file to update Sharepoint.
- Please Post a comment to help others set this up.
Have in mind that I am not a programmer.
ReplyDeleteYour part one helped me tremendously thank you for that,
Check the following for communication with SQL
# Output to csv for later use
$FDContactDatacsv | Export-csv $filepath$filename.csv
#JSON output with depth
$FDContactData | ConvertTo-Json -depth 100 | Set-Content "$filepath$filename.json"
Write-Host " Downloaded" $FDContactData.count "tickets into csv & json files" $filepath$filename
# Get Data Script Ends
#SQLDB part
#SQLDB params
$SQLservername = "Servername"
$SQLDBname = "Databasename"
$SQLTablename = "Tablename"
$user = "username"
$pass = "password"
#Create SQL statements
$add = @()
foreach ($r in $FDContactData)
{
#make some properties null for my sanity
$r.cc_email = $r.cc_email -replace ';',''
$r.description = $r.description -replace '"','' -replace "'",''
$r.subject = $r.subject -replace "'",''
$r.description_html = ""
$r.custom_field = $r.custom_field -replace ';',''
$SQLcheck = "SELECT count(1) from "+$SQLTablename+" where display_id = " + $r.display_id
$sqlresponse = Invoke-Sqlcmd -ServerInstance $SQLservername -Database $SQLDBname -Query $SQLcheck -Username $user -Password $pass
$sqlresult = $sqlresponse.Column1
If ($sqlresult -eq '1')
{
$SQLColumnlist = ""
$SQLcolumns =""
$z=@()
$z = ($r | Select-Object -First 1).PSObject.Properties | ?{$_.Value -ne $null} #| FT Name,Value$sql
$Columnnames = ($z.name)
foreach ($col in $Columnnames)
{
if ($col -eq $Columnnames[0])
{
$withquotes = ""
$withquotes = "'" + $r.$col + "'"
$SQLColumnlist = $col + " = "+ $withquotes
}
else
{
$withquotes = ""
$withquotes = "'" + $r.$col + "'"
$SQLColumnlist = $SQLColumnlist + " , "+ $col + " = "+ $withquotes
}
}
$add += "UPDATE " + $SQLTablename +" SET " + $SQLColumnlist + " Where Display_id ="+ $r.display_id +$nl
}
else
{
$SQLColumnlist = ""
$SQLcolumns =""
$z=@()
$z = ($r | Select-Object -First 1).PSObject.Properties | ?{$_.Value -ne $null} #| FT Name,Value
$Columnnames = ($z.name)
foreach ($col in $Columnnames)
{
if ($col -eq $Columnnames[0])
{
$SQLcolumns = $col
$SQLColumnlist = "'" + $r.$col
}
else
{
$SQLcolumns = $SQLcolumns + "," + $col
$SQLColumnlist = $SQLColumnlist + " ',' "+ $r.$col
}
}
$add += "INSERT INTO " + $SQLTablename +" (" + $SQLcolumns + ") "+ "VALUES (" + $SQLColumnlist + "')" + $nl
}
}
foreach ($aline in $add)
{
Invoke-Sqlcmd -ServerInstance $SQLservername -Database $SQLDBname -Query $aline -Username $user -Password $pass
}