How to get latest data from Excel table after executing Run Script from Power Automate

[Anonymous]

I am new to PA. Here is my requirement:

Trigger My Flow when email arrives -> Copy Attachment to Share Point -> Run Script Against the new excel file -> Get the result rows from table -> update another excel with latest data

My Run script will basically creates Table1 with the used range in sheet1 and then update table column names and then filter out couple of columns. My Run script has below code:

function main(workbook: ExcelScript.Workbook)
{

  //Sets the Active Worksheet to the first worksheet in the workbook
  let selectedSheet = workbook.getWorksheets()[0];
  //Get used range of previously mentioned worksheet 
  let usedRange = selectedSheet.getUsedRange()
  // Add a new table
  let newTable = workbook.addTable(usedRange, true);

   // Replace all   with _ on range 1:1 on selectedSheet - Only Table Header
  selectedSheet.getRange("1:1").replaceAll(" ", "_", { completeMatch: false, matchCase: false });

  let table1 = workbook.getTable("Table1");
  
  // Apply checked items filter on table table1 column Resource_Manager
  table1.getColumnByName("Column1").getFilter().applyValuesFilter([""]);

  // Apply checked items filter on table table1 column Employee_Name

  table1.getColumnByName("Column2").getFilter().applyValuesFilter(["Some_Value"]);
  
  return selectedSheet.getUsedRange().getValues();

}

When I execute below flow, Run script returning all the columns in the 'Table1'. But I would like to get only the columns displayed in Table1 after Run Script executes (after applying multiple filters). As I need to apply multiple filters I can not user 'List Rows Present In Table - Filter ODATA Query'.  My Table1 contains multiple rows and multiple columns. Not sure what am I missing here. Appreciated your help.

Here is my Flow:

[VJR · Accepted solution]

Hello @Anonymous 

Could you try the usage of ..getRange().getVisibleView() mentioned in below link

https://docs.microsoft.com/en-us/office/dev/scripts/resources/samples/filter-table-get-visible-range

Also check out the training video at the bottom of the link.

[Anonymous]

Thank you @VJR . Now my Flow returning required data. 

Once I got the output from 'Run Script' flow, how can I use the JSON data to update rows in Excel using 'Update a row' flow until the JSON data ends.

[VJR]

Hi @Anonymous 

That's great that you are now able to retrieve the rows.

Not clear about what you are looking for.

If you are able to then please share how the Json looks like and what do you want to update in Excel. 

[Anonymous]

Hi @VJR 

My Run Script Flow returning below JSON data. I would like to update another excel file with below JSON data. My another excel file (Excel2) is situated at share point and contains a table with the same column names as the columns in JSON data. 

[
  [
    "Column1",
    "Column2",
    "Column3",
    "Column4",
    "Column5"
  ],
  [
    "Row1",
    "Row2",
    "Row3",
    "Row4",
    "Row5"
  ],
  [
    "Row1",
    "Row2",
    "Row3",
    "Row4",
    "Row5"
  ]
  <Multiple Rows>
]

I tried to create the flow using JSON data. As I have to update each row in Excel2 using each row returned by JSON, not able to get how can I fetch column name and column values from JSON output. 

Can you please help. Below is my theoretical flow. No clue on how to get column names and column values from JSON to update a row in Excel.

[Anonymous]

Hi @VJR ,

The problem with the return type in Run Script. I have updated my Run Script code to re-format the return value as JSON object and 'Parse JSON' took care of remaining activities.

https://docs.microsoft.com/en-us/office/dev/scripts/resources/samples/get-table-data