Skip to content

Instantly share code, notes, and snippets.

What would you like to do?
$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 = "$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 "" -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 "" -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 = "$employeeid"
$Employeename = ((Invoke-RestMethod -Method Get -uri $uriGet -Headers $Header).value).displayName
#Loop through employees time sheet entires and add to variable
$uriGet = "$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){
#Getting the the currency of the project and converting the revenue to NOK.
$ProjectID = ($TimeSheetEntry.project).id
$uriGet = "$ProjectID"
$project = @()
$project = (Invoke-RestMethod -Method Get -uri $uriGet -Headers $Header).value
$CurrentRevenue = @()
if ($ -ne 1){
$rate = (Invoke-RestMethod -Method Get -uri "https://$($project.currency.url)/rate?date=$($" -Headers $Header).value.rate
$factor = ($currency | Where-Object {$ -eq $}).factor
$CurrentRevenue = ($TimeSheetEntry.chargeableHours * $TimeSheetEntry.hourlyRate) * ($rate / $factor)
# ID 1 = NOK - No conversion required
$CurrentRevenue = ($TimeSheetEntry.chargeableHours * $TimeSheetEntry.hourlyRate)
$body3 = @()
$body3 = @"
"ProjectID": "$($",
"EmployeeName": "$($Employeename)",
"EmployeeID": "$($Employeeid)",
"Hours": "$($TimeSheetEntry.hours)",
"HourlyRate": "$($TimeSheetEntry.hourlyRate)",
"CurencyID": "$($",
"RevenueNOK": "$CurrentRevenue",
"TimesheetEntryID": "$($TimeSheetEntry.ID)",
"TimesheetEntryDate": "$($"
$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.
"Adding to table"
#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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment