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
Super User
Super User

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
Super User
Super User

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.

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
Super User
Super User

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
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

New Ideas Forum MPA.jpg

A new place to submit your Ideas for Power Automate

Announcing a new way to share your feedback with the Power Automate Team.

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Super User 2 - 2022 Congratulations 768x460.png

Welcome Super Users

The Super User program for 2022- Season 2 has kicked off!

Users online (5,016)