Utilizing PowerApps Formulas: Linking Excel Calculations to Power Apps

Introduction:

The need to integrate Microsoft tools often leads us to bridge Microsoft Excel and Power Apps. This blog explores how we can harness the power of Excel calculations through PowerApps formulas and reflect the results in Power Apps, leveraging Power Automate and Office Script.

App developers often need to transfer Excel’s computing prowess into Power Apps. This need typically arises when porting pre-existing spreadsheet logic into Power Apps or when Excel functions, which Power Apps lack, become necessary. Take, for instance, the Workdays function.

Integration Using Power Automate Flow and Office Script

If you’re new to Office Script, think of it as the modern incarnation of Excel macros designed for files on SharePoint or OneDrive for Business. Written in Typescript, it simplifies scripting by offering a recorder that captures screen activities. Microsoft’s official documentation provides further information on Office Script.

Demonstration: Linking a Mortgage Loan Calculator to Power Apps

We will demonstrate this method using the mortgage loan calculator spreadsheet from Microsoft’s template collection. Users enter details into cells C4 to C8, like purchase price and interest rate. The spreadsheet then calculates outcomes such as the monthly loan payment in cell D2. Recreating these complex calculations directly in Power Apps can pose challenges.

We aim to create an Office Script that enters the Excel inputs and then retrieves the monthly loan payment. A Power Automate flow will then invoke this script, enabling its use from within Power Apps.

Steps to Set Excel Values Using Office Script

First, store your spreadsheet on SharePoint or OneDrive for Business. Create a new Office Script through the Automate tab by choosing “New Script” and naming it “Set Mortgage Loan Value”.

function main(workbook: ExcelScript.Workbook,
    purchasePrice: number,
    interestRate: number,
    durationMonths: number,
    loanAmount: number) 
{
    let worksheet = workbook.getActiveWorksheet();
    worksheet.getRange("C4").setValue(purchasePrice);
    worksheet.getRange("C5").setValue(interestRate);
    worksheet.getRange("C6").setValue(durationMonths);
    worksheet.getRange("C7").setValue(loanAmount);
    return worksheet.getRange("D2").getValue();
}

The script’s core updates the cell values from C4 to C7 and then returns the value of D2, which contains the monthly loan payment calculation.

Designing a Power Automate Flow to Trigger the Office Script

To start an Office Script, you need to set up a Power Automate flow. Begin with a new ‘instant cloud’ flow with a Power Apps trigger, and then use the Excel online business connector to select your spreadsheet. Choose the ‘Run script’ action and provide the necessary location details, library, and file names.

At the flow’s conclusion, include a “Respond to PowerApp” action to ensure the ‘MonthlyPayment‘ result returns as text due to some known limitations with Power Apps.

Initiating the Power Automate Flow from Power Apps

In Power Apps, you can now link to this flow and input the necessary data. The OnSelect attribute of the “calculate monthly payment” button would now look like this:

Set(varResult, 
    SetMortgageSpreadsheetValues.Run(
          txtPurchasePrice.Text, 
          txtInterestRate.Text, 
          txtDuration.Text, 
          txtLoanAmount.Text
    ).monthlypayment
)

This command assigns the flow’s output to a variable named varResult, making it possible to display it on a label.

Conclusion:

Harnessing Excel’s computational power to reflect outcomes in Power Apps offers an efficient solution, particularly with complex PowerApps formulas. With the combined strength of Office Script and Power Automate flow, this integration not only becomes feasible but also streamlined.

Need assistance or have queries about integrating other tools with Power Apps? Don’t hesitate to contact us. Our experts are always here to guide and assist you.