Power Automate Excel Get The Value Of A Cell Or Range
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']
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')
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]
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')
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']
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 Power Apps & Power Automate articles sent to your inbox each week for FREE
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.
I am new to Power Automate. In your example, it appears you have to already know the values of the cell or range to build the flow. Is this correct? And if so, how is the flow helping? Thanks
John,
It depends. In my example I show how to get values from a known cell location or range. You can also use Excel named ranges to get cell or range addresses. Further to that I wrote an article on how to get all of the values in an Excel table that might have a different amount of rows each time (see below).
If you want to know what’s possible ask GPT to give you advice on which Microsoft Graph API calls to make.
Link to article:
https://www.matthewdevaney.com/fastest-way-to-read-large-excel-table-in-power-automate/
Hello John,
If you need to get a dynamic number of rows from an Excel sheet not formatted as a table, then you may want to try this office script set-up:
https://community.powerplatform.com/galleries/gallery-posts/?postid=9f7a7bcf-c88f-ef11-ac20-7c1e525bd67d
John,
When considering Office Scripts make sure to note you only get 1,600 action runs per day. It’s a hard limit. If you go over, you must wait until the next day.
Hey Matthew, if I’m not mistaken, we need to embed the Office Script within Excel for the connector to recognize it. However, if someone like a storekeeper wants to upload the Excel file and isn’t familiar with Office Scripts, is there a way to include your own Office Script directly in a Power Automate flow and run it across all the Excel files used in that flow?
Btw, keep up the great work—your articles have helped me a lot, and I know they’ve made a difference for others like me!
Thanks Matthew ! Your solution solves my problem how to get the values from action table at excel file to put them into planner 🙂
Matthew, one more question, how to use that flow with sharepoint files ?
Yes thats possible, add an other string variable:
[DRIVEID] with the value: actions(‘Get_worksheets’)[‘inputs’][‘parameters’][‘drive’]
and then adjust the url in de “send an http request”:
https://graph.microsoft.com/v1.0/me/drives/%5BDRIVEID%5D/items/%5BITEMID%5D/workbook/worksheets/%5BWORKSHEETNAME%5D/range(address='A1‘)
Jaap, can you show me one exact url ? I’ve got “code”: “ItemNotFound”,
Thanks for your aid.
https://graph.microsoft.com/v1.0/me/drives/@{variables('DriveId')}/items/@{variables('varItemID')}/workbook/worksheets/@{variables('varSheetName')}/range(address='A2')This is the URL form de code view in power automate, for cell A2
I believe there’s a connector specifically for SharePoint, if I’m not mistaken—this one, I think.
Siddharth,
The SharePoint HTTP connector does not have access to the endpoints needed for this. Please use the technique I suggested above.
Hi Matthew,
A related tip: we leverage the Run script from SharePoint library Office scripts action to deliver basic compute functionalities to Logic Apps / Power Automate. See the example below which transforms CSV to a JSON object.
The script does not use or need a particular Excel sheet:
interface outData { body: {}[], } function main(workbook: ExcelScript.Workbook, fileContent: string, sepLine: string = String.fromCharCode(13) + String.fromCharCode(10), sepField: string = ","): outData { let data: string[] = fileContent.split(sepLine); let headers: string[] = parseCSV(data[0]); let lines: { [key: string]: string }[] = []; data.slice(1, data.length).forEach(csvLine => { let line: { [key: string]: string } = {}; parseCSV(csvLine).forEach((value, index) => { line[headers[index]] = value; }); lines.push(line); }); let retVal: outData = { body: lines } console.log(retVal); return (retVal) function parseCSV(line: string): string[] { let inQuotes = false; const result: string[] = []; let value = ''; for (let i = 0; i < line.length; i++) { const char = line[i]; if (char === '"') { if (inQuotes && line[i + 1] === '"') { value += '"'; i++; } else { inQuotes = !inQuotes; } } else if (char === sepField && !inQuotes) { result.push(value); value = ''; } else { value += char; } } result.push(value); return result; } }Another use case, is to find differences between to json objects to determine what to create / update / delete.
Thanks for all your efforts to the community & cheers,
Marc
Thank you for sharing your script Marc 🙂
Thanks for the great article! I’m able to retrieve the values from the Excel file, but my incoming files have different headers each time so I cannot hard code the headers like you did in the Select action. Is there a way to map the data dynamically?