Power Automate Excel Get The Value Of A Cell Or Range

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 ReportBuild A New Cloud Flow In Power AutomateExtract The DriveId Of The Excel WorkbookGet Cell Value From Excel In Power AutomateRun The Flow To Get A Single Cell ValueGet A Range Of Cells From Excel In Power AutomateRun 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.

KeyValue
Customeritem()[0]
Categoryitem()[1]
Salesitem()[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.





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

Subscribe
Notify of
guest

16 Comments
Oldest
Newest
Inline Feedbacks
View all comments
John
John
1 month ago

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

Tyler K
Tyler K
1 month ago
Reply to  John

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

Siddharth Pd.
Siddharth Pd.
24 days ago

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!

Maciej
Maciej
1 month ago

Thanks Matthew ! Your solution solves my problem how to get the values from action table at excel file to put them into planner 🙂

Maciej
Maciej
29 days ago

Matthew, one more question, how to use that flow with sharepoint files ?

Last edited 29 days ago by Maciej
Jaap Juursema
26 days ago
Reply to  Maciej

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

Maciej
Maciej
20 days ago
Reply to  Jaap Juursema

Jaap, can you show me one exact url ? I’ve got “code”“ItemNotFound”,

Thanks for your aid.

Jaap Juursema
16 days ago
Reply to  Maciej
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

Siddharth Pd.
Siddharth Pd.
24 days ago
Reply to  Maciej

I believe there’s a connector specifically for SharePoint, if I’m not mistaken—this one, I think.

Screenshot 2024-11-11 114751.jpg
Marc
29 days ago

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

Regan
Regan
23 days ago

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?

arkady
arkady
4 days ago

I have error when call HTTP:  “code”: “InvalidAuthenticationToken”,
    “message”: “Access token is empty.”,
    “innerError”: {