After reading some blog posts including Chris Webb's posts about Web Services in Power Query 

We decided to try and implement a smart solution for one of our customers, requiring Retrieve and Update functionality from and to a CRM using REST API and JSON format using Power Query!

The following post describes how we implemented the solution using Excel , Power Query and a pinch of VBA.

Background

Our customer is a retailer that uses a CRM called VTiger to manage clients and invoices.
Every day, they receive an Excel file from the delivery company they work with, this file contains a list of dozens of invoices that were delivered and the amount of money they received from the customer.
Then comes the problematic part: an employee needs to log in to the CRM and match the details in the Delivered Invoices file to each relevant invoice in the system, separately. If the amounts match, the Status needs to be changed to Paid, and the delivery status to Delivered.
This process takes hours of tedious work.

We made a solution that saves the customer hours of tedious work and also prevents human errors.
The solution is: Using Power Query to compare the Delivered Invoices data with actual invoices details from the CRM, imported using the CRM's API. Then using the same API to update the invoices back into the CRM with the required changes. All through Power Query, with a click of a button!
The process looks like this:

The final output looks like this:

You can see that the last 3 columns indicate what was updated in the entry, and if the data was not updated ? the last column indicates why.

In this post, we will go through:

1)  General syntax for API Requests in Power Query

2) An example of its implementation in the authentication process that we needed to do in order to import and update
data to the CRM

3) How we imported data from the CRM

4) How we updated data into the CRM

API Requests

Before looking at the solution itself, here are the 2 main methods that were used, the implementation of GET request and POST request with JSON format.

GET Request:

In order to submit a GET request, we simply use the Web.Contents() function with the request itself, and we put this inside a Json.Document() function to parse the answer we get from the API into more readable format:

	Json.Document(Web.Contents(#GetRequest#))

 

POST Request:

Submitting a POST request is a bit more tricky.
Here's the complete process for a POST Request:

        actualUrl = #URL#,
	cntnt = Text.ToBinary(Uri.BuildQueryString(#ARRAY_OF_PARAMETERS#)),
	options = [Headers =[#"Content-type"="application/x-www-form-urlencoded"],Content=cntnt],
	result = Json.Document(Web.Contents(actualUrl, options)),

A more detailed explanation:
First Row: We need to save aside the URL itself that we send the request to
Second Row: We make an array of parameters, and parse it into a QueryString, and then into   binary format.
Third Row: We then make an array of 'Headers' and 'Content'; 'Headers' gets a string that states to the API the content type, and
'Content' is the information we saved from before.
Last Row: We now send the request to the API and read the answer back in JSON format, just as we did in the GET request, only we
now send the Web.Contents() function 2 parameters, the URL itself, and the object we made the step before that.

Now let's look at the solution itself, and see these methods in action.

Authentication

Each CRM may have different authentication methods and requirements, Let's go through the process on the vTiger case.
In order to connect with this CRM API you need a SessionID.
To get a SessionID you need to request one using an authentication string that is an MD5 formatted string from a combination of a TokenID and a personal AccessKey that is unique to your user.

The authentication process In steps:

1)      Get a Token ? Use username in GET request.
2)      MD5 Code ? Use Token and Personal Access Key in MD5 function (Done in VBA because we could not find a way to do it using
Power Query)
3)      SessionID ? Use the MD5 converted string in a POST request

Solutions:

1) Token:
Get Request

	Source = Json.Document(Web.Contents("https://#COMPANY#.od1.vtiger.com/webservice.php?
        operation=getchallenge&username=" & #USERNAME#)),
        result = Source[result],
        token = result[token]

2) MD5 Conversion:
The simplest way we found to format the string into MD5 format is through a VBA MD5 function, which we found inside a
custom made MD5 class we downloaded here:
     https://www.bullzip.com/md5/vb/md5-visual-basic.htm

 3) Session ID:
       SessionID is the key authentication we will need in the actual process of downloading and uploading data to the CRM.
       We submitted a POST request in order to get a session ID:

let
    actualUrl = "https://#COMPANY#.od1.vtiger.com/webservice.php?operation=login",
    cntnt = Text.ToBinary(Uri.BuildQueryString([username=#USERNAME#,accessKey=#ACCESSKEY# ])),
    options = [Headers =[#"Content-type"="application/x-www-form-urlencoded"],Content=cntnt],
    result = Web.Contents(actualUrl, options),
    #"Imported JSON" = Json.Document(result),
    result1 = #"Imported JSON"[result],
    sessionName = result1[sessionName]
in
    sessionName

 

Import Invoices Data From CRM

In order to know which invoices in the CRM we need to update, for each row in the delivered invoices table we need to:

1)      Retrieve data of the specific invoice number from the CRM, using the Ref Numbers
2)      Check that the invoice data in the CRM requires updating
3)      Set aside notes regarding the entry, e.g. ? Invoice does not exist, amounts do not match etc.

We loaded the Delivered Invoices table, and added a column with a function we created that uses a GET request to return some invoice data from the CRM (the same method I used in the first step of the authentication).
The function's main purpose ? to import an invoice data.

    Json.Document(Web.Contents("https://#COMPANY#.od1.vtiger.com/webservice.php?operation=query&sessionName=" 
	& SESSIONID  & "&query=select * from Invoice where cf_877 = '" & InvoiceReferenceNumber & "';"))

Then we checked whether the fields we needed are within the required criteria. If not ? we would then put all the entries that were not relevant aside so in the end it would be possible to append all of these rows with the final result and have information about each row of why it failed to run.

Let

//Get Delivered Invoiced Table
    Source = Excel.CurrentWorkbook(){[Name="tblRawData"]}[Content],

//Get the corresponding Invoice Data from the CRM 
    GetInvoices = Table.AddColumn(Source , "InvoiceData", each try Invoices([Reference No]) otherwise "Invoice  
                  Does Not Exist"),
//Set aside rows with error :invoice does not exist
    ErrorInvoiceDoesNotExist =Table.RenameColumns( Table.SelectRows(GetInvoices , each ([InvoiceData] = 
                              "Invoice Does Not Exist")),{{"InvoiceData", "Error"}}),
//Continue to work with the imported invoices 
    ImportedInvoices = Table.SelectRows( GetInvoices , each ([InvoiceData] <> "Invoice Does Not Exist")),
    Expanded = Table.ExpandTableColumn(ImportedInvoices , "InvoiceData", {"Amount", "InvoiceStatus", 
               "RefNum", "LBCStatus","Column1.id"}, {"Invoice.Amount",  "Invoice.InvoiceStatus", 
               "Invoice.RefNum", "Invoice.LBCStatus","Invoice.Id"}),
    CompareAmounts = Table.AddColumn(Expanded , "AreAmountsEqual", each if [Amount]=[Invoice.Amount] 
                     then 1 else "Amounts Do Not Match"),
//Set aside rows with error: Amounts do not match
    FilterOutIncorrectAmounts = Table.SelectRows(CompareAmounts , each ([AreAmountsEqual] <>1)),
    ErrorAmountsDoNotMatch  = Table.RenameColumns(#"Filtered Rows1",{{"AreAmountsEqual", "Error"}}),
//Take the relevant invoices and continue to run the checks to see what needs to be done with each of them 
    AmountsOk = Table.SelectRows(CompareAmounts , each ([AreAmountsEqual] = 1)),
//....
//More Checks And Setting Aside Errors...
//.....

Update Data Into The CRM

Now that we have a table with all the entries, and prompts that say whether we should update the invoices in the CRM, we can actually go on and do just that.
The update query in the CRM also requires a 'POST Request', and it requires the whole Invoice object, with all of its parameters, in order to upload the data.

What we did is import the whole Invoice object as is, and then replace the text inside with the new values.
We wrote a function that gets the InvoiceID, the name of the field that needs to be replaced, its current value, and the new one we would like to update it to.

Inside that function we imported the whole invoice object that was we needed to update, but we did not parse it from JSON to a record, but simply took the text as is and used the Text.Replace() function to change the field from its previous value to the new one.
There were some additional changes to the text, so it would go back in the same format as it came.
We made the function return a string that says exactly what it updated, and if it failed, it would return "ERROR".

(InvoiceID, Field, ReplaceFrom, ReplaceTo) =>
let
    Source = Web.Contents("https://#COMPANY#.od1.vtiger.com/webservice.php?operation=retrieve&sessionName=" 
            &  SessionID & "&id=" & InvoiceID),
    #"Imported Text" = Table.FromColumns({Lines.FromBinary(Source,null,null,1255)}),
    InvoiceObj= Text.Replace(Text.Replace(#"Imported Text"{0}[Column1],"{" & """" & "success" & """" & ":true," 
                & """" & "result" & """" & ":",""),"}}","}"),
    FixedReplaceFrom = Text.Replace(ReplaceFrom,"/","/"),
    FinalObj= Text.Replace(InvoiceObj,""""& Field & """" &  ":" & """" & FixedReplaceFrom & """",""""& Field & 
              """" &  ":" & """" & ReplaceTo & """"),
    actualUrl = "https://#COMPANY#.od1.vtiger.com/webservice.php?operation=update",
    cntnt = Text.ToBinary(Uri.BuildQueryString([sessionName=SessionID ,element=FinalObj])),
    options = [Headers =[#"Content-type"="application/x-www-form-urlencoded"],Content=cntnt],
    result = Web.Contents(actualUrl, options),
    FinalOutput = if Json.Document(result)[ success] = true 
                  then "Changed " & Field & " from " & ReplaceFrom & " to " & ReplaceTo 
                  else "ERROR"
in
    FinalOutput

We then used this function in the main query inside added columns.

//Run a function to update the invoice in the CRM with the updated statuses
    ChangeStatus = Table.AddColumn(FinalBeforeChangeStatus, "ActionsTakenStatus", each if 
                  [NeedChangeStatus]=false then "Nothing" else 
                  (ChangeValInInvoice([Invoice.Id],"invoicestatus",[Invoice.InvoiceStatus],"Paid"))),
    ChangeLBSStatus = Table.AddColumn(ChangeStatus , "ActionsTakenLBC", each if [NeedChangeLBC]=false then 
                      "Nothing" else (ChangeValInInvoice([Invoice.Id],"cf_879",[Invoice.LBCStatus],"Delivered"))),

//Append the final table of updated invoices with all the errors that happened along the way so the user
//will know what happened with each of them 
     Append = Table.Combine({ChangeLBSStatus ,ErrorInvoiceDoesNotExist ,ErrorAmountsDoNotMatch, ErrorCanceled}) 
in 
     Append

That's all for now,
Power Query is so powerful, Whenever we think we've seen it all , You find something new !