PowerBI – Using FreshDesk Web Services API

November 7, 2018

We started exploring the use of the FreshDesk cloud-based customer support platform.

Which is one of the modules of the FreshWorks Suite.

You can start off with a free subscription, which is good enough to start building you BI tool.


Once you are happy you can switch over to a paid subscription giving you extra features.


One of them is extensive reporting,

After exploring the Power BI integration. I found out that all of the third party solution provided did not work Sad smile

And one if the solutions required that you have a Power BI Pro licence…


So I ended up making my own Power BI Dashboard using the FreshDesk REST Web Services API.

See here for the API documentation : https://developers.freshdesk.com/api/

The Web Service API looks very straight forward and works with any REST compatible application.

Implementing this in Power BI, was challenging.

But as you can see here, you can do all you need in your Free Power BI Desktop too.

So this will save you some licenses money too Winking smile

You can start off with a free subscription, which is good enough to start building your BI Dashboards.



Shows that Power BI is the right marriage for the 2 platforms, O365 and FreshDesk Winking smile

In case you need to run this as offline data source or want to schedule reports.

You can use PowerShell to extract the data, and use this as a data source.

This will save you quite some money uplifting your subscription.


Example :

If you have 25 Agents and you need to schedule reports. Your subscription is $19 / user / month more expansive.

This is a burden of an additional $5,000 subscription cost.

Other paid service provider is FreshInsights / Radiare



If you run the Invoke-WebRequest command against your URI in Powershell.

Like this :

$Data = Invoke-WebRequest -uri $URL -Headers $Headers -Method GET -ContentType application/json 


You can see in the response header information the API Version and the remaining calls you have

In this case 2996 out of 3000 per hour !

This is a limit set by Freshdesk to reduce the maximum API calls per hour.

Steps to get started using Power BI using the Web Service API :

1. create a free trial subscription

2. Copy your Web Service API Key


3. Open Power BI and do a Get DATA Web Data



Paste in the web service URL for Tickets for example.

4. Connect to FreshDesk using the API Key and password


PS : To use the Power BI Service and Scheduled REFRESH,

        you need to use the Anonymous Authentication !

5. Next built your Queries and transformations to extract the data


6. Build your Visuals and Measures


Tips and Tricks

An EXTRA Bonus is that you can incorporate this in your SharePoint Online site.

No license needed see here on how to.


Schedule a Refresh using Power BI Service

Recently Microsoft changed the Power BI service policy.

Where you can’t schedule a refresh when using a public web service ?


But no worries there is a solution for this too Winking smile


Need to get started building your own interactive Dashboard, drop a message here.

SalesForce.com – Accessing using Web Services and Scripting – Part 3

March 23, 2016


Powershell – SalesForce.com – REST Example :


function Get-AuthorizationTokenWithUsernamePasswordFlow ` 
($client_id, $client_secret, $username, $password, $security_token)
    Add-Type -AssemblyName System.Web

    # $uri = "https://test.salesforce.com/services/oauth2/token";
    $uri = "https://login.salesforce.com/services/oauth2/token";
    $grant_type = "password";

    $username = [System.Web.HttpUtility]::UrlEncode($username)
    $password = [System.Web.HttpUtility]::UrlEncode($password)

    $requestBody = "";
    $requestBody += "grant_type=$grant_type";
    $requestBody += "&client_id=$client_id";
    $requestBody += "&client_secret=$client_secret";
    $requestBody += "&username=$username";
    $requestBody += "&password=$password$security_token";

    Write-Host "Uri:" $uri
    Write-Host "Body:" $requestBody

    Invoke-RestMethod -Method Post -Uri $uri -Body $requestBody

# usage create a ClientID and ClientSecret
# https://help.salesforce.com/apex/HTViewSolution?id=000205876&language=en_US

# https://developer.salesforce.com/page/Digging_Deeper_into_OAuth_2.0_on_Force.com
# Obtaining a Token in an Autonomous Client (Username and Password Flow)

$Cred = Get-Credential -credential "Email.Address@Company.com"
$username = $Cred.UserName
$password = $Cred.GetNetworkCredential().Password $client_id = "YOUR CLIENT ID";
$client_secret = "YOUR CLIENT SECRET";
$security_token = "YOUR SECURITY TOKEN";

# Debug
 Get-AuthorizationTokenWithUsernamePasswordFlow ` 
 $client_id $client_secret $username $password $security_token

$Token = Get-AuthorizationTokenWithUsernamePasswordFlow ` 
$client_id $client_secret $username $password $security_token

$URI = $Token.instance_url+"/services/data/v20.0/"

$Response = Invoke-RestMethod -URI $URI -Method GET ` 
-Headers @{"Authorization" = "OAuth " + $Token.access_token};

Write-Host ""

# Response = availaible REST services


Write-Host ""

# Query Accounts Example 
Write-Host "Accounts :"

$StrQuery = "SELECT Name, Id from Account LIMIT 100"

$URI = $Token.instance_url+"/services/data/v20.0/query?q=" + $StrQuery

$Response = Invoke-RestMethod -URI $URI -Method GET ` 
-Headers @{"Authorization" = "OAuth " + $Token.access_token};

Write-Host ""
Write-Host " # of Records : "

Write-Host ""
Write-Host "Enum Account Name :"
$Response.records | % {$_.name}

# Query Opporunities Example 
$StrQuery = "SELECT AccountId, OwnerId, Name, Description, ` 
StageName, Amount, Probability FROM Opportunity WHERE StageName <> `</pre>
<pre>'Closed Lost' ORDER BY StageName "

$URI = $Token.instance_url+"/services/data/v20.0/query?q=" + $StrQuery

$Response = Invoke-RestMethod -URI $URI -Method GET ` 
-Headers @{"Authorization" = "OAuth " + $Token.access_token};


Write-Host ""
Write-Host "Query Output :"

Foreach ($objItem in $Response.records) {

    "{0,-120} {1, 25} {2, 30}" -f `
   $objItem.name, $objItem.StageName, $objItem.Amount

This script will return your Access Token, using this function



Next it will list the REST API’s you can use


Query Accounts Example :


Enjoy !


SalesForce.com – Accessing using Web Services and Scripting SOAP – Part 2

March 23, 2016


Powershell – SalesForce.com – SOAP Example :


# WSDL is created from within SF.Com Application
$Path = Split-Path -Path $($global:MyInvocation.MyCommand.Path)
$Uri = $Path+"\Enterprise.wsdl" # You can also chose to create a Partner WSDL

$Cred = Get-Credential –credential “<a href="mailto:“Email.LastName@company.com&quot;$SecToken">Email.LastName@company.com”

$User = $Cred.UserName
$Password = $Cred.GetNetworkCredential().Password 

# Proxy
$service = New-WebServiceProxy -Uri $Uri -Namespace sforce -UseDefaultCredential

# Login
$loginResult = $service.login($Cred.UserName, $Password+$SecToken)

# Debug info
# $loginResult

$service = New-Object sforce.SforceService
$service.Url = $loginResult.serverUrl
$service.SessionHeaderValue = New-Object sforce.SessionHeader
$service.SessionHeaderValue.sessionId = $loginResult.sessionId

# Listing SalesForce Objects
$accountObjects = $service.describeGlobal()

Write-Host "Enum SalesForce Objects"
Write-Host "-----------------------"

Foreach ($item in $accountObjects.sobjects)
    Write-host ($item.name, $item.label)

Write-Host ""
Write-Host "Retrieving Object Meta Data"
Write-Host "---------------------------"

# Retrieving meta data about a specific object
$opportunityObject = $service.describeSObject("Opportunity")

foreach ($item in $opportunityObject.fields)
    Write-Host ($item.name, $item.label, $item.type)

Write-Host ""
Write-Host "RelationshipNames"
Write-Host "-----------------"

foreach ($item in $opportunityObject.childRelationships)
  Write-Host ($item.relationshipName)

Write-Host ""
Write-Host "Querying Objects"
Write-Host "----------"

# $StrQuery = "SELECT Id, Name, StageName, Amount FROM Opportunity WHERE IsWon = True";

$StrQuery = "SELECT AccountId, OwnerId, Name, Description, StageName, `
Amount, Probability FROM Opportunity";
$queryResult = $service.query($StrQuery) 
foreach ($item in $queryResult.records)
   Write-Host ( $item.AccountId, $item.OwnerId, $item.Name, ´
   $item.Description, $item.StageName, $item.Amount, $item.Probability)

rv Password, SecToken 

This Script will list SalesForce Account Objects.


Retrieves meta data about a specific object.


Retrieves RelationshipNames.


Querying Objects


For more info on how to get started see here

To go further see here



SalesForce.com – Accessing using Web Services and Scripting – Part 1

March 23, 2016

Many of the modern Web platform offer Web Services API’s to access their data.

One of them is SalesForce.com. I choose this example because it has a huge global audience and a well documented Developer Guide.

Before starting your scripting exercise you need to plough through the extensive developer manuals. Which is the lease fun part. Therefore I summarized it all for you to give you all a head start. Winking smile

SF.com has a number of Web Services Protocols as well as a number of authentication methods using the OAuth 2.0 protocol.

On top of that you can start developing in SF.com using APEX

What is APEX


Apex is a strongly typed, object-oriented programming language that allows developers to execute flow and transaction control statements on the Force.com platform server in conjunction with calls to the Force.com​ API. Using syntax that looks like Java and acts like database stored procedures, Apex enables developers to add business logic to most system events, including button clicks, related record updates, and Visualforce pages. Apex code can be initiated by Web service requests and from triggers on objects.

1. Web Services Protocol

You can use the SOAP protocol or you can use the REST protocol, to access to the web services API.

Both have different ways of dealing with this in you programming / scripting environment.



The REST API is designed to be easily accessible from any programming language – once you understand the basic concepts of the API, you will be able to apply them to PHP, Ruby, .NET, or any other environment.



Use SOAP API to create, retrieve, update or delete records, such as accounts, leads, and custom objects. With more than 20 different calls, SOAP API also allows you to maintain passwords, perform searches, and much more. Use SOAP API in any language that supports Web services.

Choosing a WSDL

· Force.com Enterprise WSDL—This API is for most enterprise users who are developing client applications for their organization. The enterprise WSDL file is a strongly typed representation of your organization’s data.

Note the following when generating the enterprise WSDL:

· If new custom fields or objects are added to, renamed, or removed from your organization’s information, you need to regenerate the WSDL file in order to access them.

· The generated WSDL contains the objects and fields in your organization, including those available in the selected versions of each installed package. If a field or object is added in a later package version, you must generate the enterprise WSDL with that package version to work with the object or field in your API integration.

· Force.com Partner WSDL—This API is for Salesforce partners who are developing client applications for multiple organizations. As a loosely-typed representation of the Salesforce object model, the partner WSDL can be used to access data within any organization.

More API’s and which one to chose ?


2. Authtentication Protocol

SF.com uses OAuth2.0 protocol for users to authorize applications, to access Force.com resources (via the Force.com REST and SOAP Web Service APIs).

More info :


The OAuth 2.0 authorization framework enables a third-party application to obtain limited access to an HTTP service.

More info on OAuth 2.0 authorization framework :


1. Obtaining an Access Token in a Web Application (Web Server Flow)

2. Obtaining an Access Token in a Browser or Native Application (User-Agent Flow)

3. Obtaining an Access Token using a JWT Bearer Token

4. Obtaining an Access Token using a SAML Bearer Assertion

5. Obtaining an Access Token using a Web SSO SAML Assertion

6. Obtaining a Token in an Autonomous Client (Username and Password Flow)

For our scripting purpose we are going to use the Autonomous Client method.


SF.com Configuration Settings

Before Starting you need to do some configurations in SF.com

1. User

On the user level you need to trigger a Security Token, that we will use in the client application



Generate your WSDL file ENTERPRISE or PARTNER



Create a new APP Config and setup the OAuth access parameters.

If you use a Client Application (no Web Interface) you don’t need to set the Callback URL.

Just fill in some dummy data.


Once you have configured the new APP, you can read out the CLIENT CONSUMER KEY and the CONSUMER SECRET

This you will need later on when configuring the Client Application Access.

4. Object Model


Next you need to get acquainted with the SF.com Object Model / Data Model in order to run the proper Queries definitions.


OK, so now you are ready to get started if you have made up your mind which of the options you want to chose.

Powershell Examples :

1. SOAP API : See here

2. REST API : See here

Out of experience the REST method is much faster.

More info here:


As you can notice is that you go a long way just using a Scripting environment.



QlikView – How to get MS Azure – Office365 BI Reports

February 3, 2016

QlikView Desktop is one of the leading BI In Memory Visualization Tools.

As I have shown in previous posts, this contains a VBScript host engine. And as well the Desktop Client is fully COM Compatible.

Giving these 2 nice bonus points for us scripters Smile

Let see how to use QlikView to provide BI Insights in your MS Azure and Office365 environment.

In the Office365 Admin portal, you can get nice statistics to analyze issues and performance if needed.


But QlikView can serve you better ! Because it is all centralized in your dashboard that is fully customizable Winking smile


Since it is all in memory, it is lightning fast to do your analysis. If you schedule the QVW you have it all ready each morning.

I built this proof of concept, and will be gradually ad more statistics.

See here for more info on Office365 Reporting web service

Happy Scripting !

SharePoint -Build a Table of Content (TOC)

September 12, 2014

The thing I was always missing in SharePoint is how we could provide the users a nice (dynamic) TOC per Site or Site Collection.

Well this is possible using the SOAP connector (Web Services) against SharePoint itself.

Major bonus that comes along using the SOAP data source is, that is can access any data within SharePoint, across all Site Collections. Smile

Let’s go

Add a SOAP connection to a Site Collection


Use the proper WDSL syntax !


Add in a new Web Part Page.


Insert the Web Part.


Get the data using the SOAP Connector you have created above, using the Insert –> Data View


Browse down to the SOAP Services to find it.


It will list too much columns and rows, so we can apply a filter. Using the Add / Remove Columns button. And Filter Button if needed.


First remove most columns, keep only Title and add Description.


Now we are going to make I a bit more dynamic, by making the Title data a Hyperlink so you can jump to the respective page.

click on the Title columns and choose INSERT –> Hyperlink. And choose the Formula button.


And now we go and look at the result so far.

Go to you Site Pages where you saved the page and check it.


Looking good !

A bit more salt and pepper will make it perfect. So we are going to add the appropriate ICONS the belong by each type of list.

Go back to the SP Designer and add a space before Title the table rows.


Go to the Data Source Details on the right side pane. Select ImageURL


Choose on top Insert Selected Fields as … Formatted –> Picture

Save it and go back to see the end result !


This will dynamically keep track of you TOC. Smile

Enjoy !

AutoIT – Getting data from a Web Service

August 20, 2014

Web Services are a nice feature on the internet. But accessing it as a scripter is not so obvious.

Let’s see here how it goes.

First we need to have the URL of the Web Service and peak into the SOAP Envelop structure.



This API returns 4 methods to retrieve data back depending on some input.

Let’s take the GetInfoByZIP for Example. You see and input field and a SOAP envelope structure which we need later on.


If you enter some ZIP CODE you will get an XML response.


But if we want to script is we need to get WSDL definition.

This site offers an online WSDL browser.



Look at the ?WSDL extention after the URL

Now we have everuthin we setup our script to get the data.


In the script I added some debug information output.

You can capture the Debug XML response, and add it to an XML Parser for further investigation.


Once you know how the XML is formatted, we can capture the data in it.


Here is the full code.

Dim $objHTTP
Dim $strEnvelope
Dim $strReturn
Dim $objReturn
Dim $strQuery
Dim $strValue

$strValue = InputBox("Testing", "Enter your new value here.", 60007)

; Initialize COM error handler
$oMyError = ObjEvent("AutoIt.Error","MyErrFunc")

$objHTTP = ObjCreate("Microsoft.XMLHTTP")
$objReturn = ObjCreate("Msxml2.DOMDocument.3.0")

; Create the SOAP Envelope
$strEnvelope = '<?xml version="1.0" encoding="utf-8"?>' & _
'<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">' & _
'<soap:Body>' & _
'<GetInfoByZIP xmlns="http://www.webserviceX.NET">' & _
'<USZip>'&$strValue&'</USZip>' & _
'</GetInfoByZIP>' & _
'</soap:Body>' & _

; Set up to post to the server
$objHTTP.open ("post", "http://www.webservicex.net/uszip.asmx?op=GetInfoByZIP", False)

; Set a standard SOAP/ XML header for the content-type
$objHTTP.setRequestHeader ("Content-Type", "text/xml")

; Set a header for the method to be called
$objHTTP.setRequestHeader ("SOAPMethodName", "urn:myserver/soap:TaxCalculator#Getsalestax")

ConsoleWrite("Content of the Soap envelope : "& @CR & $strEnvelope & @CR & @CR)

; Make the SOAP call
$objHTTP.send ($strEnvelope)

; Get the return envelope
$strReturn = $objHTTP.responseText

ConsoleWrite("-----------" & @CRLF)
ConsoleWrite ("Debug Response : "& @CR & $strReturn & @CR & @CR)
ConsoleWrite("-----------" & @CRLF)

; Load the return envelope into a DOM
$objReturn.loadXML ($strReturn)

ConsoleWrite("Return of the SOAP Msg : " & @CR & @CR & $objReturn.XML & @CR & @CR)

; Query the return envelope
$strQuery = "SOAP:Envelope/SOAP:Body"


$Soap = $objReturn.Text

MsgBox(0,"SOAP Response","The Response is  : " & $Soap)

Func MyErrFunc()
  Msgbox(0,"COM Test","We intercepted a COM Error !"       & @CRLF  & @CRLF & _
			 "err.description is: "    & @TAB & $oMyError.description    & @CRLF & _
			 "err.windescription:"     & @TAB & $oMyError.windescription & @CRLF & _
			 "err.number is: "         & @TAB & $HexNumber              & @CRLF & _
			 "err.lastdllerror is: "   & @TAB & $oMyError.lastdllerror   & @CRLF & _
			 "err.scriptline is: "     & @TAB & $oMyError.scriptline     & @CRLF & _
			 "err.source is: "         & @TAB & $oMyError.source         & @CRLF & _
			 "err.helpfile is: "       & @TAB & $oMyError.helpfile       & @CRLF & _
			 "err.helpcontext is: "    & @TAB & $oMyError.helpcontext _

In Powershell this is only a few lines 2 to be exact.But at least now you know what is going on behind the scene. Smile

See Example here

PowerShell – How to call a Web Service

January 22, 2013

Simple as of PS v2.0 you can use the

New-WebServiceProxy object to get data from a Web Service API in just a few lines.

$Weather = New-WebServiceProxy -Uri `

# Or this one, look at the Expand table option !
$zip = New-WebServiceProxy -Uri http://www.webservicex.net/uszip.asmx?WSDL 
$zip.GetInfoByCity('New York')|select -expand table|ft city, state, zip, area_code -auto

This is the XML output :

<?xml version="1.0" encoding="utf-16"?>
<CurrentWeather>   <Location>Antwerpen / Deurne, Belgium (EBAW) 51-12N 004-28E 14M</Location>   <Time>Jan 22, 2013 - 03:20 AM EST / 2013.01.22 0820 UTC</Time>   <Wind> from the E (090 degrees) at 3 MPH (3 KT):0</Wind>   <Visibility> less than 1 mile:0</Visibility>   <SkyConditions> mostly cloudy</SkyConditions>   <Temperature> 26 F (-3 C)</Temperature>   <DewPoint> 24 F (-4 C)</DewPoint>   <RelativeHumidity> 92%</RelativeHumidity>   <Pressure> 29.65 in. Hg (1004 hPa)</Pressure>   <Status>Success</Status>

Enjoy !