In this next post on the Tripletex API, we are going to look at how I created a PowerShell script that gets all hours a consultant has put into Tripletex towards a customer project, and then adds the hours into a table in Teams Dataverse. The script is well-commented, so you should be able to understand what it does after reading the comments.
We need to prepare a couple of things before running the script. You should also have read the previous post in this series, to know how to get your tokens.
First, we need to prepare the Teams Dataverse environment, so we have a place to put the data.
Open up your Microsoft Teams client, and go to the general channel of the team you want to have the Dataverse environment connected to. Click + to add a new tab.
Search for and click on Power Apps.
Click on Add.
Click on “Create an app in Power Apps”.
Click on Build.
Search for and select the team you want to use, then click Create.
If it takes to long time to load, go out of the Power Apps menues, select Power Apps again, then click Build.
Click New and App.
Click Save.
Click Back and Leave.
Click on See all.
Click Tables->New->Table
Give it a Display name and a primary column name, then click Save:
Click + and add the following columns as Single line of text.
Now we need to create three Instant Cloud Flows, for adding, updating and reading the table we just created.
Create them as follows.
First is the flow to Add data to the table.
You will need this JSON schema to copy/paste into the HTTP request in the flow.
Copy the HTTP POST URL into your script, and use the same settings as here:
Next is reading from the table.
You will need this JSON schema to copy/paste into the HTTP request in the flow.
Copy the HTTP POST URL into your script, and use the same settings as here:
Next is updating entries in the table.
You will need this JSON schema to copy/paste into the HTTP request in the flow.
Copy the HTTP POST URL into your script, and use the same settings as here:
The next step is to run the script to get data into your table.
The script is well-commented, so you should be able to understand what it does after reading the comments.
| #Authentication | |
| $consumerToken = "xxxxxx" | |
| $employeeToken = "xxxxxx" | |
| #Add your HTTP Trigger URIs from the cloud flows here: | |
| $AddURI = "xxxxx" | |
| $ReadURI = "xxxxx" | |
| $UpdateURI = "xxxxxx" | |
| $expirationDate = get-date ((get-date).Adddays(+2)) -format "yyyy-MM-dd" | |
| $uri = "https://tripletex.no/v2/token/session/:create?consumerToken=$consumerToken&employeeToken=$employeeToken&expirationDate=$expirationDate" | |
| #Get token and create header. | |
| $token = ((Invoke-RestMethod -Method PUT -Uri $uri -ContentType "application/JSON").value).token | |
| $user = '0' | |
| $pass = $token | |
| $pair = "$($user):$($pass)" | |
| $encodedCreds = [System.Convert]::ToBase64String([System.Text.Encoding]::ASCII.GetBytes($pair)) | |
| $Header = @{"Authorization" = "Basic $encodedCreds"} | |
| $Currency = (Invoke-RestMethod -Method Get -uri "https://tripletex.no/v2/currency" -Headers $Header).values | Select-Object id, code, factor | |
| $today = get-date -format "yyyy-MM-dd" | |
| #How many days back in time to check employee activity. | |
| #Value is set to 14 days, as its unlikely that someone will change hour entries older than 14 days. | |
| #Change the value to whatever value you want on the first run, if you need to start storing data older than 14 days, then change back to 14 days. | |
| $DaysPast = get-date ((get-date).Adddays(-14)) -format "yyyy-MM-dd" | |
| #List of all employees | |
| $EmployeeIDs = ((Invoke-RestMethod -Method Get -uri "https://tripletex.no/v2/employee?includeContacts=false&onlyContacts=false&from=0&count=1000" -Headers $Header).values | Select-Object id).id | |
| #Loop through all employes to find all projects in the last 90 days they have put in hours on. | |
| foreach($employeeID in $employeeIDs){ | |
| #Gets an employees name. | |
| $uriGet = "https://tripletex.no/v2/employee/$employeeid" | |
| $Employeename = ((Invoke-RestMethod -Method Get -uri $uriGet -Headers $Header).value).displayName | |
| #Loop through employees time sheet entires and add to variable | |
| $uriGet = "https://tripletex.no/v2/timesheet/entry?employeeId=$employeeid&dateFrom=$DaysPast&dateTo=$today&from=0&count=1000" | |
| $TimeSheetEntries = (Invoke-RestMethod -Method Get -uri $uriGet -Headers $Header).values | where-object{$_.project} | |
| $count = 0 | |
| foreach($TimeSheetEntry in $TimeSheetEntries){ | |
| $count++ | |
| $count | |
| #Getting the the currency of the project and converting the revenue to NOK. | |
| $ProjectID = ($TimeSheetEntry.project).id | |
| $uriGet = "https://tripletex.no/v2/project/$ProjectID" | |
| $project = @() | |
| $project = (Invoke-RestMethod -Method Get -uri $uriGet -Headers $Header).value | |
| $CurrentRevenue = @() | |
| if ($project.currency.id -ne 1){ | |
| $rate = (Invoke-RestMethod -Method Get -uri "https://$($project.currency.url)/rate?date=$($TimeSheetEntry.date)" -Headers $Header).value.rate | |
| $factor = ($currency | Where-Object {$_.id -eq $project.currency.id}).factor | |
| $CurrentRevenue = ($TimeSheetEntry.chargeableHours * $TimeSheetEntry.hourlyRate) * ($rate / $factor) | |
| }else{ | |
| # ID 1 = NOK - No conversion required | |
| $CurrentRevenue = ($TimeSheetEntry.chargeableHours * $TimeSheetEntry.hourlyRate) | |
| } | |
| $body3 = @() | |
| $body3 = @" | |
| { | |
| "ProjectID": "$($project.id)", | |
| "EmployeeName": "$($Employeename)", | |
| "EmployeeID": "$($Employeeid)", | |
| "Hours": "$($TimeSheetEntry.hours)", | |
| "HourlyRate": "$($TimeSheetEntry.hourlyRate)", | |
| "CurencyID": "$($project.currency.id)", | |
| "RevenueNOK": "$CurrentRevenue", | |
| "TimesheetEntryID": "$($TimeSheetEntry.ID)", | |
| "TimesheetEntryDate": "$($TimeSheetEntry.date)" | |
| } | |
| "@ | |
| $body4 = @" | |
| { | |
| "TimesheetEntryID": "$($TimeSheetEntry.ID)", | |
| } | |
| "@ | |
| #Check if the timesheet entry already exists in the table. | |
| $CheckTimesheetEntryID = @() | |
| $CheckTimesheetEntryID = Invoke-RestMethod -body $body4 -ContentType "application/json" -Method POST -Uri $ReadURI | |
| #If the timesheet entry does not exist, add it to the table. | |
| If(!$CheckTimesheetEntryID){ | |
| "Adding to table" | |
| $body3 | |
| #Adding timesheet entry to table in Dataverse. | |
| Invoke-RestMethod -body $body3 -ContentType "application/json;charset=utf-8" -Method POST -Uri $AddURI | |
| } | |
| #Rounding numbers to 2 decimals, so we can compare them. | |
| $CheckTimesheetEntryIDcr25d_hours = @(); | |
| $CheckTimesheetEntryIDcr25d_hours = [math]::Round($($CheckTimesheetEntryID.cr25d_hours),2); | |
| #If the hours in the timesheet entry is different from the hours in the Dataverse table, update the hours in the Dataverse table. | |
| #This is just i case the user has edited the hours in the timesheet. | |
| If($CheckTimesheetEntryID ){ | |
| If($CheckTimesheetEntryIDcr25d_hours -notlike $($TimeSheetEntry.hours)){ | |
| "Updating entry" | |
| $body20 = @" | |
| { | |
| "RowID": "$($CheckTimesheetEntryID.cr25d_projectsusershoursid)", | |
| "Hours": "$($TimeSheetEntry.hours)" | |
| "@ | |
| Invoke-RestMethod -body $body20 -ContentType "application/json;charset=utf-8" -Method POST -Uri $UpdateURI | |
| } | |
| } | |
| } | |
| } |
The next step would be either to expand this report to include the total hours used on a project, how many hours you have left of the project budget, and so on.
Now that you have some data to play with, an idea would be to connect the Dataverse table with Power BI to visualize it.
Hope this blog post is understandable, even though I have not gone into deep step-by-step in all parts of it.
A blog about automation and technologies in the cloud 