Do you need to update a cell value in Excel during your flow?
When you start working with the Excel connector in Power Automate, you’ll quickly realize all the actions to add or update data only work with Excel tables.
Updating a single cell value is a common requirement when dealing with Excel. For example, the cell might be used to store an input parameter that is used in other calculations. The value may need to be updated to change these calculations.
This post will show you how to use Excel Office Scripts and the Run script from SharePoint library action in the Excel Power Automate connector to update a cell value.
Create an Office Script to Update a Cell Value in Excel
First, you will need to create an office script in Excel. This will be used in Power Automate by the Run script action to update your cell value.
Here’s how to create a script in Excel.
- Go to the Automate tab.
- Click on the New Script command.
This will open the Office Script pane where you can add your code.
- Click on the Script name to rename it and save the script to SharePoint.
- Add a new Script name such as Update Cell Value. This is how you will reference the script from Power Automate.
- Click on the Location arrow to open the folder navigation and select a location in a SharePoint site.
This will a menu that allows you to select the SharePoint site and folder where you want to save the script.
- Select the SharePoint site and navigate to the folder where you would like to save your script.
- Press the Save button.
Now you can add your code to the Code Editor.
function main(
workbook: ExcelScript.Workbook,
sheetName: string,
cellAddress: string,
updateValue: string
) {
let sheet = workbook.getWorksheet(sheetName);
let updateCell = sheet.getRange(cellAddress).setValue(updateValue);
let cellValue = sheet.getRange(cellAddress).getValue();
return cellValue;
}
- Paste the above code into the Code Editor.
- Press the Save script button.
Now your script to update a cell value is ready to use in Power Automate.
This script will allow the Run script action in Power Automate to take the sheet name sheetName
and cell address of the cell cellAddress
you want to update as input. It also allows you to input a value updateValue
to which to update that cell.
This script will also get the new value in the cell and return it to Power Automate so you can verify the value was updated during the flow.
Office Script to Update Single Cell Value in Excel
Now that you have a script saved in SharePoint, here’s how you can use it to update an Excel cell.
- Add the Run script from SharePoint library action to your flow.
- Select the SharePoint site that contains the Excel workbook that you want to update by using the Workbook Location dropdown list.
- Select the library containing your Excel file with the Workbook Library dropdown.
- Choose the Excel file in the Workbook file picker.
- Select the SharePoint site that contains the script file that you want to run by using the Script Location dropdown list.
- Select the library containing your script file with the Script Library dropdown.
- Choose the script file in the Script file picker.
Once you select the script file, the action will display the inputs defined in the script.
- Add the name of the sheet in your Excel file in which you want to update a cell to the sheetName input. For example, the sheet named Data.
- Add the cell address in row letter and column number format to the cellAddress input for the cell you want to update. You could also use a range name if the cell was named. For example, the cell C2.
- Add the new value for the cell to the updateValue input. For example, the value 3.
In this example, the script would update cell C2 in the Data sheet to the value 3.
Now, your flow is set up to update a single cell’s value in Excel. Whenever the flow is triggered, the specified cell will be updated with the new value.
Conclusions
This article explored how to update a single cell in Excel using Power Automate.
Office Scripts and the Run script from SharePoint library action will allow you to update a single cell from your flows.
Have you explored Office Scripts with Power Automate yet? Let me know in the comments!
This worked so so well, thanks so very much!