Power Automate Excel Write Value To A Specific Cell Or Range

Power Automate Excel Write Value To A Specific Cell Or Range

Power Automate can write a value a specific cell in Excel without that cell being included in a table. It can also write values to a range of cells. 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 write values to it. This is a standard action that does not require a premium license.

Table of Contents
• Introduction: Write Values To An Excel ReportCreate An Excel Spreadsheet In OneDriveBuild A New Cloud Flow In Power AutomateFind The DriveId Of An Excel WorkbookWrite Value To A Cell In Excel Using Power AutomateRun The Flow To Write Value To A CellWrite Values To A Range Of Cells In Excel Using Power AutomateRun The Flow To Write Values To A Range Of Cells




Introduction: Write Values To An Excel Report

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



Using Power Automate we can write the value “Microsoft Xbox” to the cell “A2”.



Or we can write entire rows of data to an Excel worksheet like this.




Create An Excel Spreadsheet In OneDrive

We will begin by creating an Excel file to write values to. Open OneDrive and create an Excel workbook.



Input the following values into Sheet1.



Then save the workbook and name it SalesByCustomer.




Build A New Cloud Flow In Power Automate

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




Find The DriveId Of An 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




Write Value To A Cell In Excel Using Power Automate

To write a value to 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.



Select the PATCH Method to update a value.

PATCH



Add this JSON to the Body to write a value to a single cell.

{
  "values": [
    [
      "Microsoft Xbox"
    ]
  ]
}



And set the Content-Type field to json.

application/json




Run The Flow To Write Value To A Cell

That’s all the actions we need. Give the flow a test run to ensure it is working.



The value of cell A2 is changed from “Nintendo” to “Microsoft Xbox”.




Write Values To A Range Of Cells In Excel Using 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 and update the body.



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



And also change the Body to include multiple rows of data formatted like this:

{
  "values": [
    [
      "Microsoft Xbox",
      "Video Games",
      425000
    ],
    [
      "Steam Deck",
      "Video Games",
      175000
    ]
  ]
}




Run The Flow To Write Values To A Range Of Cells

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



The Excel workbook shows new values in the range A2:C3.





Questions?

If you have any questions or feedback about Power Automate Excel Write Value To A Specific 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

6 Comments
Oldest
Newest
Inline Feedbacks
View all comments
Aleksei
Aleksei
24 days ago

Exactly the challenge I had! Thank you very much! Your site is so helpful!

Sam
Sam
23 days ago

Thanks Matthew!!
Is there any limitation on the maximum cell range that can be specified in the api call?

Reagan
Reagan
21 days ago
Reply to  Sam

Hi Sam! I don’t know if this helps at all, but I use the Patch API in a solution to replace the Update a row tool at scale and that updates 8800 cells in a few seconds. If a limit exists, it’s substantial

Uday
Uday
21 days ago

Hi, if we use power apps triggered flows with connection reference (service account) in a solution the it is throwing unauthorized error. is there any solution for this?

Caleb
Caleb
2 days ago

I have previously modeled this in a Custom Connector I use for Graph API functionality missing from OOTB connectors, but using OneDrive HTTP request to keep it Standard is a neat idea!