cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
saiemandi
Regular Visitor

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

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:

 

saiemandi_0-1635275146939.png

saiemandi_1-1635275263600.png

saiemandi_2-1635275352195.png

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
VJR
Memorable Member
Memorable Member

Hello @saiemandi 

 

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.

View solution in original post

5 REPLIES 5
VJR
Memorable Member
Memorable Member

Hello @saiemandi 

 

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.

View solution in original post

saiemandi
Regular Visitor

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
Memorable Member
Memorable Member

Hi @saiemandi 

 

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. 

saiemandi
Regular Visitor

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.

saiemandi_0-1635325559554.png

 

saiemandi
Regular Visitor

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

 

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Users online (3,232)