cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Luciano_A
Helper I
Helper I

Upload a non-table-formatted Excel file content to Sharepoint

Dears,

 

I have a rather simple question: Is it possible via PowerAutomate to automate the upload of a non-table-formatted Excel file content to Sharepoint?

 

Below an example of how the file would look like:

 

Report owner:John Doe  
Location:Milan
Paris
Tokyo
  
Start Date01/01/2021  
Employee NameEmployee AddressEmployee Job Description 
John Doe123 Street, BostonRandom text 
John Doe1123 Street, BostonRandom Text 
1 ACCEPTED SOLUTION

Accepted Solutions
DamoBird365
Super User
Super User

Hi @Luciano_A 

 

I have created a basic proof of concept for you.

 

My cloud flow:

DamoBird365_0-1620765069781.png

 

My excel sheet

DamoBird365_1-1620765088224.png

 and the Excel Script Solution:

function main(workbook: ExcelScript.Workbook) {
  
  // Get the first worksheet 
  const sheet = workbook.getFirstWorksheet();
  
  //get active range of WorkSheet
  let range = workbook.getActiveWorksheet().getUsedRange();

  // Get last used row of WorkSheet
  let lastrow = range.getRowCount();

  //get table range  
  const TableRange = `A9:L${lastrow}`;

  // Create a table using the data range.
  let newTable = workbook.addTable(sheet.getRange(TableRange), true);
  newTable.setName("TestTable");
  
  // Get the first (and only) table in the worksheet.
  let table = sheet.getTables()[0];

  // Get the data from the table.
  let tableValues = table.getRangeBetweenHeaderAndTotal().getValues();

  console.log(lastrow);
  return tableValues

}

Not only does it create a table for you, should you want to use the get rows action, it returns the results back to the flow as follows:

 

DamoBird365_2-1620765192823.png

 

Hopefully this is a good starter for you but please let me know how you get on.

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien


P.S. take a look at my new blog here and like & subscribe to my YouTube Channel thanks 😉

 

 

View solution in original post

12 REPLIES 12
DamoBird365
Super User
Super User

Hi @Luciano_A 

 

You want to explore Excel Scripts

 

I have a demo of populating an excel file from PowerAutomate using scripts but I understand you want to do this in reverse?  Read from excel and pass back to PowerAutomate.  Definitely possible, you just need to explore the functionality and examples above.

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien


P.S. take a look at my new blog here and like & subscribe to my YouTube Channel thanks 😉

 

 

Luciano_A
Helper I
Helper I

Hi @DamoBird365 , thank you for your reply!

I want exactly do the same, from Excel to Sharepoint List via PowerAutomate, trying to keep "manual input" to 0 or almost.

( Pratically I'd wish the end-users to just save the "RAW" excel report to a specified folder, and then let PowerAutomate do the magics! )

I went trough the referenced examples, but none do cover this specific need I have 😞 

Hi @Luciano_A 

 

How would you envisage your SharePoint List?  Would you have 6 rows for the above example and 2 list items are created?

 

Will your excel file follow a template?

 

What would be your trigger?  New file created i.e. uploaded to a specified folder already completed?

 

Excel scripts can identify the data in you sheet and pass it back to the Cloud Flow.  I've not done your sepcific requirement before but I could give it a go if I knew a bit more.  Ideally need sample expectations based on the q's above, specifically the outcome of the data into a SharePoint list.

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien


P.S. take a look at my new blog here and like & subscribe to my YouTube Channel thanks 😉

Luciano_A
Helper I
Helper I

Let me add here an Excel template, it should make it easier to explain 🙂

 

The structure will always be the same, the only peculiarity I am just thinking about is on the HRBP column, which might contain more than one name and should be treated ( where possible ) as a multiple-person-field.

 

https://we.tl/t-QQcCdBeAMn 

Based on this template as input, I would like a similar sharepoint as output of PowerAutomate:

 

Hire DateWorker NameEmployee IDBusiness TitleLocationManagerHRBPE-Mail addressHire TypeEmployee typeAddressPrivate Phone number
01/05/2021John Doe58980ManagerTokyoManager XPartner Xjohn.doe@email.comNew HireRegularStreet 123, Toronto+1 617000001
01/05/2021Doe John59026DirectorBerlinManager XPartner X

Partner Z
doe.john@email.comNew HireRegularStreet 123, Abu Dhabi+1 617000002
01/05/2021Iron Man59111ManagerParisManager YPartner X

Partner Y
iron.man@email.comNew HireRegularStreet 123, Praha+1 617000003
01/05/2021Super Man59137DirectorTokyoManager YPartner Ysuper.man@email.comNew HireRegularStreet 123, Vienna+1 617000004
10/05/2021Green Hulk59265EmployeeTokyoManager YPartner Xgreen.hulk@email.comNew HireRegularStreet 123, Milan+1 617000005

@Luciano_A 

 

Assuming rows 1 to 8 are surplus to requirements, if you are looking for everything in row 9 down, I would create a table and then use the action get table. 

 

So, when you trigger your flow (file created in folder?), you can use the action to get tables and then retrieve the rows from that table.  Once you have that data, loop through it and create item for each row.

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien


P.S. take a look at my new blog here and like & subscribe to my YouTube Channel thanks 😉

Hi @DamoBird365  That's exactly what I am looking to automate: Create a table on the Excel file - is it possible to have this process automated in any way?

@Luciano_A if you are using a template excel file, why not make the table part of the template?  You don't need the Cloud Flow to create it?  You simply need it to find it and read it?

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien


P.S. take a look at my new blog here and like & subscribe to my YouTube Channel thanks 😉

Luciano_A
Helper I
Helper I

Ups - sorry, I probably mis-expressed myself.

 

In the actual process, the Excel file is being automatically generated and distributed via E-Mail without a table.

 

I would like to skip the manual part of creating a table inside the file, and give as input directly the raw file to PowerAutomate.

@Luciano_A 

 

What is generating the excel file for your email routine?  Why not make the table part of that template?  Otherwise, back to Excel Scripts 🙂

 

Damien

Luciano_A
Helper I
Helper I

Unfortunately, I don't see it possible 😕

 

Any tips for the Excel script pathway instead?

DamoBird365
Super User
Super User

Hi @Luciano_A 

 

I have created a basic proof of concept for you.

 

My cloud flow:

DamoBird365_0-1620765069781.png

 

My excel sheet

DamoBird365_1-1620765088224.png

 and the Excel Script Solution:

function main(workbook: ExcelScript.Workbook) {
  
  // Get the first worksheet 
  const sheet = workbook.getFirstWorksheet();
  
  //get active range of WorkSheet
  let range = workbook.getActiveWorksheet().getUsedRange();

  // Get last used row of WorkSheet
  let lastrow = range.getRowCount();

  //get table range  
  const TableRange = `A9:L${lastrow}`;

  // Create a table using the data range.
  let newTable = workbook.addTable(sheet.getRange(TableRange), true);
  newTable.setName("TestTable");
  
  // Get the first (and only) table in the worksheet.
  let table = sheet.getTables()[0];

  // Get the data from the table.
  let tableValues = table.getRangeBetweenHeaderAndTotal().getValues();

  console.log(lastrow);
  return tableValues

}

Not only does it create a table for you, should you want to use the get rows action, it returns the results back to the flow as follows:

 

DamoBird365_2-1620765192823.png

 

Hopefully this is a good starter for you but please let me know how you get on.

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien


P.S. take a look at my new blog here and like & subscribe to my YouTube Channel thanks 😉

 

 

Luciano_A
Helper I
Helper I

@DamoBird365 This is a perfect starter for me!!! Kudos to you 🙂

 

The very first step of taking a file, formatting it with a table and uploading it to a sharepoint list is finally done 🙂

 

I have now to reasearch a bit on how to transform data - I'll get there!

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.

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.

Power Automate Designer Feedback_carousel.jpg

Help make Flow Design easier

Are you new to designing flows? What is your biggest struggle with Power Automate Designer? Help us make it more user friendly!

Top Solution Authors
Users online (2,437)