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 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.

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

8 Comments
Oldest
Newest
Inline Feedbacks
View all comments
John
John
3 days 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
2 days 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

Maciej
Maciej
1 day 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
1 day ago

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

Last edited 1 day ago by Maciej
Marc
1 day 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