Power Apps – Get a value from a cell of any Excel file (Office Scripts)

Introduction:

From Power Apps, you can get a value from any cell in an Excel file that is saved in SharePoint or OneDrive. We will use a combination of Power Apps, Power Automate, and Office Scripts.

Prerequisites:

Make sure that you can see the Automate tab in either your Excel Desktop app or in Excel Online. Organization settings and licenses enables this one. You will also use your OneDrive account for tests.

Step 1:

From your browser, go to OneDrive. From the home page of OneDrive, do the following:

1. Click the “Add New” button
2. Click on “Excel workbook”

Step 2:

A new workbook (Excel file) will be opened in a new tab. Rename this workbook into My Excel Template.

Step 3:

From this Excel file:

1. Click the “Automate” tab on the ribbon
2. Click the “New Script” button. A new pane on the left side will appear.

Step 4:

On the script pane. You will see a pre-made script that is available by default. Replace that script with the one below:

function main(
    workbook: ExcelScript.Workbook
) {
    const mySheet = workbook.getWorksheet("Sheet1");
    let myValue = mySheet.getRange("B2").getValue();

    return myValue;
}

Step 5:

Rename the script title as Power Apps Demo. Save the script.

We are done for the Excel file for now. Do not close this Excel tab as we would need it later when testing.

Step 6:

On a new tab, create a new app using a blank canvas in Power Apps. From a blank canvas, do the following:

1. Click the Power Automate icon on the left pane
2. Click “Create new flow”

Step 7:

Click ‘Create from blank’ button. You will be directed to the flow editor.

Step 8:

Power Apps (V2) should be the trigger. We do not need to add any inputs for that trigger. After Power Apps (V2), add the action below:

Run Script

Make sure to select from the dropdown/ file picker when selecting the parameters below. Copy and pasting may not let the flow run successfully.

Location

OneDrive for Business

Document Library

OneDrive

File

/My Excel Template.xlsx

Script

Power Apps Demo

Step 9:

Add the action below:

Respond to a Power App or flow

Add the following output:

result =

Checking:

Rename the flow as Run Excel Script. Save the flow and exit. You should return to the Power App studio.

The flow named Run Excel Script should be in your Power Automate pane.

Step 10:

On the canvas of your Power app, insert a button named Button1. Change the following properties:

Text property

"Get Value from Cell B2"

OnSelect property

Set(varResult,RunExcelScript.Run().result)

Step 11:

Insert a label named Label1. Change the following properties:

Text property

varResult

Conclusion:

To test, go back to the Excel file. Type something in cell B2. The Excel file should automatically save. Close the tab of the Excel file in your browser.

Go back to the Power App. Hit play mode and then click the button. After it loads, you should be getting the value of cell B2 showing in the label.

Note: Sometimes the automation does not get the most updated data when the Excel file is open. You need to close the Excel file after you have placed the value in cell B2.