Skip to main content
Community site session details

Community site session details

Session Id : eGm38KFNJiwGKYQimyNEwB
Copilot Studio - General
Answered

PVA to pass variables to excel sheet on SharePoint?

Like (0) ShareShare
ReportReport
Posted on 13 May 2022 11:36:23 by 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?

 

VeeLearnMSFT_0-1652441537406.png

 

Categories:
  • VeeLearnMSFT Profile Picture
    508 on 25 May 2022 at 19:58:40
    Re: PVA to pass variables to excel sheet on SharePoint?

    Thank you for your solution!

  • Verified answer
    Expiscornovus Profile Picture
    32,159 Most Valuable Professional on 23 May 2022 at 08:04:21
    Re: PVA to pass variables to excel sheet on SharePoint?

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

     

  • VeeLearnMSFT Profile Picture
    508 on 19 May 2022 at 12:01:27
    Re: PVA to pass variables to excel sheet on SharePoint?

     

     

    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.   

    VeeLearnMSFT_0-1653155902685.png

     

  • VeeLearnMSFT Profile Picture
    508 on 18 May 2022 at 11:36:48
    Re: PVA to pass variables to excel sheet on SharePoint?

    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.

  • Expiscornovus Profile Picture
    32,159 Most Valuable Professional on 17 May 2022 at 14:46:40
    Re: PVA to pass variables to excel sheet on SharePoint?

    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.

     

    rates_input_officescript.png

     

  • Expiscornovus Profile Picture
    32,159 Most Valuable Professional on 17 May 2022 at 13:34:32
    Re: PVA to pass variables to excel sheet on SharePoint?

    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());
    }

     

    officescripts_calculator.png

     

    2. Within a Cloud flow you could run this script.

     

    fillincalculatorscript.png

     

    3. In the output you could see the values.

     

    fillincalculatorscript02.png

     

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Paul Stork – Community Spotlight

We are honored to recognize Paul Stork as our July 2025 Community…

Congratulations to the June Top 10 Community Leaders!

These are the community rock stars!

Announcing the Engage with the Community forum!

This forum is your space to connect, share, and grow!

Leaderboard > Copilot Studio

#1
Michael E. Gernaey Profile Picture

Michael E. Gernaey 169 Super User 2025 Season 2

#2
Romain The Low-Code Bearded Bear Profile Picture

Romain The Low-Code... 150

#3
sandeep_angara Profile Picture

sandeep_angara 75 Super User 2025 Season 2

Loading complete