cancel
Showing results for 
Search instead for 
Did you mean: 

Dynamic Excel File location support

Allow dynamic fields when working with Excel in Power Automate. 
Would be very useful when creating excel sheets in power automate to be able to then populate those sheets with data.

Status: New
Comments
sumurthy
Microsoft

Can you please elaborate on what you mean by dynamic fields? Does Office Scripts not satisfy your needs?  --

 

Office Scripts in Excel on the web let you automate your day-to-day tasks. You can record your Excel actions with the Action Recorder, which creates a script. You can also create and edit scripts with the Code Editor. Your scripts can then be run within flows. It provides a wide variety of functions like range edits, named items, charting, pivots, etc. 

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

Community samples: https://github.com/sumurthy/officescripts-projects

 

StinsmanJ
Regular Visitor

When you use Power Automate to make a new Excel Table. You cannot then add rows to that table. 

The table did not exist when the flow started therefore it cannot be touched. says Dynamic fields are not supported. 

Just trying to "Create Table" then "Add Rows" but its next to impossible to add rows to a table that did not exist when the flow was triggered. 

Also trying to use a template, alter the template then copy the changed template. Also no good as you cant change the name on a copy. 

StinsmanJ
Regular Visitor

Like I am working from Forms. 

when a new response is made in forms I want to take that response:

Create a Named Excel Sheet using Info from that Response

Then take more info from that response and add it into rows, in the Excel File that was just created.

I've parsed all the Forms data no issues, but making the Excel File and populating it has been a monumental task that i have now spent over 4 hours on and still am getting no where. 

StinsmanJ
Regular Visitor

and yeah I could build a C# front end to enter data into instead of the Form, I could then feed CSV Data to an Excel VB Macro Script that then takes it and uses the Table Convert function to populate the page.

But... isn't the point of Forms and Power Automate and Office 365 to make all of that obsolete? 

Like if the "Add Row to Table" Function's "File" field allowed me to put a Dynamic File name (IE the table created in the "Create File" function in the previous step.) then I would have been done in 30 mins. 

GeoffRen
Microsoft

I'd recommend checking out the link sumurthy sent. He's referencing the "Run Script" action specifically. This is the action you want to use if none of the other (very basic) actions meet your needs. You can sort of think of it as running a VB Macro Script as part of the Power Automate Flow. You can accept as little or as many dynamic input parameters as you want since the parameters are just parameters feeding a script so you have complete control over what those parameters do.

 

Here's a working example I just made:

 

Script that does what you specified (create worksheet with dynamic name, create and add rows to a table with dynamic inputs):

function main(workbook: ExcelScript.Workbook, worksheetName: string, input: string[][]) {
  // Add a new worksheet with the specified name
  const sheet = workbook.addWorksheet(worksheetName);
  // Add a new three column table
  let newTable = workbook.addTable(sheet.getRange("B2:D2"), false);
  // Populate table with inputted values
  for (const val of input) {
    newTable.addRow(-1, val)
  }
}

 

Hardcoded inputs:

GeoffRen_1-1611765343784.png

 

My results in Excel:

GeoffRen_0-1611765320440.png

 

StinsmanJ
Regular Visitor

typing this out for the third time... Apparenlty if you are not signed in it can't sign you in and keep your post... you go back to the main page for powerusers...

Looking at what you've done there looks like it will accomplish what I would like to do... But do you not agree that seems like a lot of work just to add a row to a table? 

If the Power Automate "Add Row to Table" Function supported a Dynamic File Name in the "File" field this would have been one simple and straight forward step like so:
https://1drv.ms/u/s!AubmV4N2AegYgZJWJwk53PzwTaZSlA?e=cR5K7h

 

It could also be accomplished by using that same function to update a Template file then copy the template to a new file with the correct name... Except... The copy file function does not support Renaming the file... Another massive over sight...

StinsmanJ
Regular Visitor

Also the photo options in the post editor is really crap. 
You need to allow uploading of photos or the use of onedrive links in your "Add Photo" option when creating a post. I Tried to share a screen shot and had to resort to just giving a one drive link as your forum would not allow me to add a picture.