Answered

PVA to pass variables to excel sheet on SharePoint?

(0) Share

Report

Posted on by VeeLearnMSFT

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?

Categories:

General topics

I have the same question (0)

All responses (6)

Answers (1)

Sort by

Expiscornovus 33,876 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!
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.

Was this reply helpful? Yes No
Expiscornovus 33,876 Most Valuable Professional on at

Like
a
(1)

Report
Copy link

Link copied!
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.

Was this reply helpful? Yes No
VeeLearnMSFT 508 on at

Like
a
(0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
VeeLearnMSFT 508 on at

Like
a
(0)

Report
Copy link

Link copied!
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.

Was this reply helpful? Yes No
Verified answer

Expiscornovus 33,876 Most Valuable Professional on at

Like
a
(1)

Report
Copy link

Link copied!
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:

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')

Was this reply helpful? Yes No
VeeLearnMSFT 508 on at

Like
a
(1)

Report
Copy link

Link copied!

Thank you for your solution!

Was this reply helpful? Yes No