Excel's List Rows present in a table unreliable?

[randomuser101]

In the Flow I usually use the Excel's Get a row action. but since a new requirement is added and the unique ID is no longer unique, I looked for other option and found the List Rows present in a table a suitable candidate with a bit of tweak.

I filtered the ID column, used the Order parameter and the top 1 to get only the latest row of the ID.
On Dev I encountered an issue where sometimes the table is not up to date or sometimes it returns empty row, instead of the existing row, after some googling and stuff, I tried adding a refresh table script then triggering that script before doing the List Row action, in hope that the query will always target fresh data.

But it seems like it still fails cause sometimes it still returns empty rows.
Is there a known bug about this action, or maybe I'm missing some step/doing some wrong steps?
Thank you for any help.  

[ManishSolanki]

Hi @randomuser101 

If you are immediately reading rows after adding or update operation, you may get undesired outcome as excel stored in cloud (SharePoint or one drive) takes some time in refreshing the data. I would suggest giving a pause of 5-10 minutes before reading the rows. To pause the flow actions, you could use "Delay" action.

If this helps & solves your problem, please remember to give a 👍 and accept my solution as it will help others in the future.

Thanks

[randomuser101]

thank you for replying, the data that encounters this issue have 1 - 7 day delay between the first inserted data and the next time a new data with the same Id is submitted again.
I think the time should be no issue.
From further checking I also found out 50:50 rate of success vs fail.
I can't even explain why some flow are working correctly while some are not.

My params for the List action are these
Filter : [column_name] eq '[value]'
Order : [column_name] desc
Top : 1

[randomuser101]

anyone have any idea about this issue?
or maybe any other place I can ask for help about this issue?

[rzaneti]

Hi @randomuser101 ,

I don't know exactly what may be causing this issue, but a possible alternative (if your organization allows it) is to run an Office Script to perform this action. In summary, Office Scripts is a kind of 'VBA' for Excel Web, based in JavaScript programming language, and has an integration with Power Automate, through the 'Run Script' action. 

If this approach makes sense for you, I can help you to write a Script to achieve to achieve this result. 

Also, here are some references about this feature:

- Official docs: https://learn.microsoft.com/en-us/office/dev/scripts/overview/excel

- Power Automate action: https://learn.microsoft.com/en-us/office/dev/scripts/develop/power-automate-integration 

- VBA vs Office Scripts: http://digitalmill.net/2023/06/10/office-scripts-the-new-vba/ 

- Getting started with Office Scripts: http://digitalmill.net/2023/06/19/get-started-with-office-scripts/ 

- Returning data from Excel ranges with Office Scripts: http://digitalmill.net/2023/09/01/accessing-excel-ranges-with-power-automate/ 

[randomuser101]

Hi, thank you for replying, at this point I am willing to try anything, I'm still kinda blank about your proposed solution, so far maybe like using the script to populate another table and run it before fetching a row containing the script result, and maybe some tweaks since there are many user, maybe prepare several rows for the number of user so that the process won't interfere with each other. 
If your idea is not that, can you at least explain what the process would be.
Thank you in advance.

[rzaneti · Accepted solution]

Hi @randomuser101 ,

There are different ways to solve your problem with the scripts. I don't know exactly how does your current file looks like, but I prepared the example below just to showcase how can you use the Office Scripts to return data based in some filters to Power Automate.

If this approach makes sense for you, please share a sample of your file with fake data and inform which data would you like to return from there. The general logic of your script must be similar to the logic below

Let's assume that we want to return the most recent sales qty of a specific product code (which will be informed in Power Automate). As you can see, there are duplicates in the product_code column, so we will need to filter it first and then "sort by date" to pick the most recent: 

To perform this task, I created the getRecord script (highlighted in yellow):

Here is the full code of the script (I didn't include comments in the code at this time, but I can do it when we prepare a code for your use case):

function main(workbook: ExcelScript.Workbook, productCode:number) {

    const tbl = workbook.getTable('Table1').getRangeBetweenHeaderAndTotal().getValues()

  const selectedProductCode = tbl.filter(e => e[0] === productCode)
  
  let foundRecord:(string | number)[] = null
  
  for(let i = 0; i < selectedProductCode.length; i++){
    if(foundRecord === null){
      foundRecord = selectedProductCode[i]
    } else if (foundRecord[2] < selectedProductCode[i][2]){
      foundRecord = selectedProductCode[i]
    }
  }

  foundRecord[2] = convertDate(foundRecord[2])

  const recordObj = {
    product_code: foundRecord[0],
    sales_qty: foundRecord[1],
    created_at: foundRecord[2]
  }
  return recordObj


}

function convertDate(serialDate:number){
  const excelEpoch = new Date(1899, 11, 31)
  const milliseconds = (serialDate - 1) * 24 * 60 * 60 * 1000;

  const date = new Date(excelEpoch.getTime() + milliseconds);

  return date;
}

In Power Automate, I add a 'Run Script' action, select the desired file and script (highlighted in green) and, once I do it, a new field will be displayed to enter the product code to be found (highlighted in blue). 

 After running this action, Power Automate receives the data related to the most recent entry of product_code 2 inside the 'result' property, in a JSON format. 

Some additional comments:

- You can have more than one, or none inputs for your script.

- You have a lot of freedom to define the content of the JSON returned by 'Run Scripts'. 

- There is a limitation for this action: all scripts must run in a maximum of 120 seconds. The Script above took 4 seconds to run. 

[randomuser101]

Thank you for the sample and explanation, I think I get your idea. I'll consult this with my team.
I didn't think of using script for this before you mentioned it. Thank you so much.