Update all the rows in Excel based on ref Number

[Emloutwo]

I am trying to make it so that, when i submit an MS FORM, it will update each row of the table that has the corresponding ref number as submitted in the form. Initially, it was showing success but had online updated one of the rows.

Any ideas?

[Nived_Nambiar]

Hi @Emloutwo 

Update a row action updates single row at  time so if you try to move through a loop, it check for row which has the reference value all the time and will update the other column accordingly. So same row will be updated always.

So bulk update is not possible with this action 

I have some ideas on your flow design, but needs excatly a screenshot of your data how it looks in excel, it would be ok to have dummy data inside it if it is confidential 🙂

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌

[Emloutwo]

yes sure. Here you go. I want all the same suppliers to be updated, where the form submitted includes the corresponding ref number. At the moment, when we submit a form for Xero, for example, it only updates Row 6, Column E. But i want all the column Es for Xero to update.

[grantjenkins · Accepted solution]

One option would be to add an additional column to your Excel Table that will be unique across all rows.

In this example, I've added a column called Id and have the following formula in cell A2. When you add a new row the formula should fill down automatically giving you a unique row number.

=ROW(A2)

For this example, I have the following Microsoft Form that allows the user to enter the Ref.

Then in your flow you would retrieve all the rows that contained the Reference Number and for each one use the Unique Id to update each row.

See full flow below. I'll go into each of the actions.

When a new response is submitted and Get response details are what you already have.

List rows present in a table retrieves the rows from Excel. We can't use the Filter Query here because you have spaces in your Ref of Supplier column.

Filter array is we we filter the rows where the Ref of Supplier is equal to the Ref value in our Microsoft Form.

Apply to each iterates over each of the rows in our Filter array.

Update a row uses the Id from the current item we are iterating over as the Key Value using the following expression. And it updates the Check to Completed.

item()?['Id']

After running the flow, the relevant rows will be updated. If I entered 25 for the Ref we would get the following.

[Nived_Nambiar]

Hi @Emloutwo 

Sorry for late response

Also you can try this office script which can be run from power automate which helps to update all rows satisfying the required condition 🙂

Refer the below office script :

// Office Script to update column B in Sheet2 based on a condition in column A

function main(workbook: ExcelScript.Workbook,ref: string,choice:string) {
  // Get the specific worksheet (Sheet2)
  let sheet = workbook.getActiveWorksheet();


  // Get the used range in column A
  let rangeA = sheet.getUsedRange(true).getColumn(1);
  let temp= parseInt(ref);
  // Get all values in column A
  let valuesA = rangeA.getValues();

  // Check if there are values in column A
  if (valuesA) {
    // Loop through each cell in column A
    for (let i = 0; i < valuesA.length; i++) {
      // Check if the cell has a value and if it meets the condition (for example, if it equals A1)
      //console.log(valuesA[i][0]);
      if (valuesA[i][0] && valuesA[i][0] === temp) {
        // Update the corresponding cell in column B
        sheet.getRange(`D${i + 1}`).setValue(choice);
      }
    }
  } else {
    console.log("Error: Unable to retrieve values from column A");
  }
}

This office script is designed based on excel template :

I hope this is simmilar to your office script as needed.

This office script get all values of column B in excel worksheet and checks whether whether any rows equals to ref number provided as input (ref number is converted to integer before comparision, as you can see in script a variable temp which is value after integer conversion of ref.)

Now coming back, let's see how to run the same from power automate 🙂

I have designed a simple form like below:

First field takes value of Ref ID while other takes value of Status/Choice to be updated 🙂

Now write the script in excel like below 🙂

Now after saving script and then let's go to power automate cloud flow :

1. Use trigger when form is submitted trigger to trigger the flow when form is submitted :

Note that, in trigger settings enable Split on option as well before starting to design the work

2. Use get response details to get responses of that form submission :

3. use run script action to run the office which you have developed to run on your excel file:

Let's see the result then :

I have submitted the folloowing in ms form:

Hope this helps !

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌