Power Automate Excel Get The Value Of A Cell Or Range

Imported reference from Matthew Devaney's blog for learning purposes. Original: https://www.matthewdevaney.com/power-automate-excel-get-the-value-of-a-cell-or-range/. Author: Matthew Devaney.

Power Automate Excel Get The Value Of A Cell Or Range Posted by - Matthew Devaney on - November 3, 2024 30 Comments

Power Automate can get a cell value from Excel without being included in a table. It can also get a range of cells outside of a table. When an Excel workbook is stored in OneDrive we can use the Office 365 Users – Send An HTTP action to access the Excel file and get values from it. This is a standard action that does not require a premium license. Table of Contents

Introduction: Get Values From An Excel Report

Build A New Cloud Flow In Power Automate

Extract The DriveId Of The Excel Workbook

Get Cell Value From Excel In Power Automate Run The Flow To Get A Single Cell Value

Get A Range Of Cells From Excel In Power Automate Run The Flow To Get A Range Of Cells

Introduction: Get Values From An Excel Report

A company sales report in Excel shows a list of vendors, categories and sales in dollars.

Using Power Automate we can get the value of a single cell “A2”.

Or we can extract the entire range of values found in cells “A2:C3”. Create An Excel Spreadsheet In OneDrive

The first task we must do is create a spreadsheet to read values from. Open OneDrive and add a new Excel workbook. Write the following data into Sheet1 .

Save the workbook and name it SalesByCustomer.xlsx.

Build A New Cloud Flow In Power Automate

Go to Power Automate and create a new flow with an instant trigger named “Excel Get Specific Cell Or Range.”

Extract The DriveId Of The Excel Workbook

To get cell or range values from an Excel workbook using Power Automate we need to know the workbook’s ItemId. The easiest way to do this is by running any Excel action and extracting the ItemId from the action’s input parameters.

Add the Excel – Get Worksheets action and target the SalesByCustomer Excel workbook in OneDrive.

Then add two Initialize Variable actions after the Excel action.

varItemId will hold the unique identifier of the Excel workbook. Use this Power Automation expression to extract the ItemId from the action inputs.

actions( 'Get_worksheets' )[ 'inputs' ][ 'parameters' ][ 'file' ] Code language: JavaScript ( javascript )

varWorksheetName stores the name of the Excel worksheet we want to get values from. Manually enter the sheet name for this variable. Sheet1

Get Cell Value From Excel In Power Automate

To get the value of a cell in Excel we will use the Office 365 Users – Send An HTTP Request action. This standard action allows us to access OneDrive using the Microsoft Graph API and interact with a workbook.

Input the following URI to get a value from cell A2 of the SalesByCustomer workbook.

https: //graph.microsoft.com/v1.0/me/drive/items/@{variables('varItemId')}/workbook/worksheets/@{variables('varWorksheetName')}/range(address='A2') Code language: JavaScript ( javascript ) Use the GET method. GET

And set the Content-Type to json using this code. application/json

The Send An HTTP request action outputs a JSON response. We only want the value of cell A2 from that response.

Insert a Data Operations – Compose action and use this expression to get the value of a single cell.

body( 'Send_an_HTTP_request:_Get_Cell' )?[ 'values' ][ 0 ][ 0 ] Code language: JavaScript ( javascript ) Run The Flow To Get A Single Cell Value

That was easy. Save and run the flow to ensure it works.

The Compose action shows the value “Nintendo.”

Get A Range Of Cells From Excel In Power Automate

If we wanted to get the values from a range of cells instead we could change the cell addresses being targeted in the Office 365 Users – Send An HTTP Request action.

Update the URI to target the address A2:C3 .

https: //graph.microsoft.com/v1.0/me/drive/items/@{variables('varItemId')}/workbook/worksheets/@{variables('varWorksheetName')}/range(address='A2:C3') Code language: JavaScript ( javascript )

Then replace the Compose action after Send An HTTP Request with a Select action . We will use it to shape the range into an array of values. Use this expression in the From field.

body( 'Send_an_HTTP_request:_Get_Range' )?[ 'values' ] Code language: JavaScript ( javascript )

Then define the following key-value pairs in the Map area. The values must all be written as expressions. Key Value Customer item()[0] Category item()[1] Sales item()[2] Run The Flow To Get A Range Of Cells

We are done. Let’s run the flow and take a look at the results.

The Select action shows an array of values from A2:C3. Did You Enjoy This Article? 😺

Subscribe to get new Copilot Studio articles sent to your inbox each week for FREE Enter your email address Sign Me Up Questions?

If you have any questions or feedback about Power Automate Excel Get The Value Of A Cell Or Range please leave a message in the comments section below. You can post using your email address and are not required to create an account to join the discussion. Matthew Devaney Power Automate

Save Docusign Document To SharePoint With Power Automate