You’re offline. This is a read only version of the page.
508
The screenshot below is a file on SharePoint that is used to calculate rates based off of the Pay Grade Slotting, wether they work Night Shifts, Licensure date or Years of Service as of a Point in Time, in this case 12/21/2021. Weather they are full time employee.
Based on the pay grade and the years of service the calculator selects the years of experience and the rate for that Grade...Th experience is in half year increments. Just curious if its possible to pass these variables to the file in Sharepoint and bring back the rate which in this case is $54.14. Is this possible?
Thank you for your solution!
Hi @VeeLearnMSFT,
Can you try these three expression below to split and extract the values into three output variables in the last action of your flow?
First value:
Share
Report
All responses (
Answers (
replace(replace(split(body('Run_script'), ',')[0], '[', ''), ']','')
Second value:
formatnumber(float(replace(replace(split(body('Run_script'), ',')[1], '[', ''), ']','')), 'C2', 'en-us')
Third value:
formatnumber(float(replace(replace(split(body('Run_script'), ',')[2], '[', ''), ']','')), 'C2', 'en-us')
Hello @Expiscornovus , Made some changes again. Here is what my script looks like now
function main
(
workbook: ExcelScript.Workbook,
paygrade: string,
worknights: string,
licensuredate: string,
yearsexperience: string,
fte: string
) {
let selectedSheet = workbook.getActiveWorksheet();
// Set values for e55:e59 on selectedSheet
selectedSheet.getRange("F7:F11").setValues([[paygrade], [worknights], [licensuredate], [yearsexperience], [fte]]);
// Retrieve the values
let range = selectedSheet.getRange("F12:F14").getValues();
// Return a response to the Cloud Flow
return range
}Here is the PVA bot output. Ideally what I want is to split this somehow so that I get the years of experience. Which in this case is 6, the hourly rate which is $59.64 rounded, and an annual salary of $95,600. So the bot would respond this way.
"Based off of the licensure date you have entered the years of experience was calculated at 6 years, it produced a hourly wage of $59.64 and with an annual salary of $95,600.
Wow this is amazing you are a genius!
Is it possible for you to show me what the formulas are inside of Return Value(s) Power Virtual agents?
I have never tried to implement office scripts but I am going to give it an honest effort. I apologize in advance but I may ask you more questions.
Thank you kindly for taking the time out of your day to provide a solution for us. It's amazing to what this community does. Really appreciate this.
Hi @VeeLearnMSFT,
When you also want to set the values you can define variables in your Office Script.
In this example all variables are of type string. It probably makes more sense to change a couple to different data types like boolean or number 😁
function main
(
workbook: ExcelScript.Workbook,
paygrade: string,
worknights: string,
licensuredate: string,
yearsexperience: string,
fte: string
) {
let selectedSheet = workbook.getActiveWorksheet();
// Set values for C2:C6 on selectedSheet
selectedSheet.getRange("C2:C6").setValues([[paygrade], [worknights], [licensuredate], [yearsexperience], [fte]]);
// Retrieve the values
let range = selectedSheet.getRange("C8:C10");
// Print the values.
console.log(range.getValues());
}
In the Run Script action those would show up as additional input fields.
Hi @VeeLearnMSFT,
Are the fields/the worksheet formatted as a table? If not, this could be a challenging flow.
It might be worth checking out the Office Scripts options. However, that does require some Typescript coding.
Below is an example which could retrieve the three cells.
I haven't checked if you can actually use input variables in the setValues method of the TypeScript. I have will have another look at that part.
1. First create an Office Script within your Excel file.
function main(workbook: ExcelScript.Workbook) {
let selectedSheet = workbook.getActiveWorksheet();
// Set values for C2:C6 on selectedSheet
selectedSheet.getRange("C2:C6").setValues([["Critical Care"],["No"],["06/21/2021"],["8/10/2022"],["0.8"]]);
// Retrieve the values
let range = selectedSheet.getRange("C8:C10");
// Print the values.
console.log(range.getValues());
}
2. Within a Cloud flow you could run this script.
3. In the output you could see the values.
#1
Romain The Low-Code...
25
#1
Pablo Roldan
25
#3
stampcoin
10
