Update a row in Excel Connector(Power Automate Cloud flows)
Specify search conditions and update one row of data from the table.
How to use
The target Excel file must be stored in OneDrive or SharePoint.
The target cell range must be converted to a table.
In this example, we will prepare a table with the items "Code", "Name", "Price", and "Qty".
Select the "Excel Online (Business)" connector when adding steps to the flow.
Select the "Update a row" from the "Action" menu.
Set Parameters.
Parameters
Location
A list of OneDrive and SharePoints that can be connected will be displayed.
From among them, select the location where the target file is located.
Document Library
For OneDrive, select "OneDrive"; for SharePoint, select "Document".
File
Select a file.
It looks like a file path, but it is only a display and the content is an ID.
If you delete the specified file and then create a file with the same name,
it will be treated as a different file. (Reassignment is required.)
Table
A list of tables that exist in "File" will be displayed.
Select the table of interest from the list.
Key Column
Search key to be updated.
When a table is selected, a list of columns is displayed.
Specify the columns from which to search.
Key Value
This is the search key to be updated.
Specify the value of the key column to be searched.
Date and time cells are treated as serial values.
If there are multiple matches for the "Key Value," only the first matching row will be updated.
If no match exists, an error is generated.
Items
If you specify up to "table," all items that exist in the specified table will be displayed.
Set the data you want to update here.
To delete a value
If you specify blanks in a column, the value will not change.
To delete a value, specify the blank string '' in the expression.
Update Multiple Rows
This action updates a single row.
It cannot update multiple rows by itself.
Therefore, you need to set up as many actions as you want to update.
The other flow is as follows.
- "List rows present in a table" to get all rows.
- Repeat all rows with "Apply to each".
- In "Apply to each", specify the update condition in "Condition".
- Place a line "Update a row" in "Condition". Use the dynamic content of "List rows present in a table" for "Key value" to update.
This will result in multiple rows being updated.
Update with multiple conditions
This action specifies only one key value and one key column as the update condition.
To specify multiple conditions, get all rows by "List rows present in a table", repeat all rows by "Apply to each", and specify multiple conditions for update in "Condition".
When dynamic content is specified for a file or table
No item is displayed because the items is not finalized.
Instead, "Provide the item properties" is displayed.
Specify the rows to be added here in JSON format.
Although there are many specifications for JSON, the items required here are as follows.
- Enclose the whole with {}.
- Describe a single cell in the format item name:data. Enclose the item name and data in ".
- Separate cells with ,.
Also, "Key Column" must be specified with dynamic content such as variables.
The following is an example of updating a line whose "Code" is "A-002" to the next value.
| Name | Qty |
|---|---|
| Banana | 5 |
{
"Name": "Banana",
"Qty": "5"
}
---