cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bordo
Frequent Visitor

Create personalized HTML Table for customers from Excel spreadsheets

Hi there,

A beginner here, working on something ambitious. Any insights would make my day.

Here is the scenario I am working on:

The goal is to create a report for each customer showing the items they purchased from different departments in an HTML table format, and email it to them.

There approximately are 100 customers, each customer has purchased items from 5 different departments (such as electronics, books, apparel, etc.)

I have excel tables for each department (5 tables) showing customer id and the items they purchased along with item descriptions/details.

All 5 tables have a Customer ID column. Other columns are completely different from each other.

 

I want to get your opinion on this. How would you approach this project ? What is the best way to come up with an efficient flow system that would create personalized reports for each customer ? See the attached table images for reference.

 

Thanks in advance! 🙂

 

1.PNG2.PNG3.PNG4.PNG

 

1 ACCEPTED SOLUTION

Accepted Solutions
DamoBird365
Super User
Super User

Hi @bordo 

 

I've done something similar with an Excel Template File and you can even convert this to PDF if need be.

 

Take a look here https://www.damobird365.com/perfectly-complete-an-excel-invoice-template/

 

I've done a demo where I took this to the next level, with multiple customer and transaction data stored in SharePoint Lists, the same concept applies to Excel Tables, if that is how you choose to store your data.

 

https://www.youtube.com/watch?v=LHDfyxf9BNc&list=PLzq6d1ITy6c2_qM_ocYDtEaENrqi92YmM I blabber on a bit in the first 3 minutes but hopefully the rest of the demo is useful to you.

 

I've uploaded the scripts and flow to https://github.com/DamoBird365/PowerAutomate/tree/main/ExcelInvoiceDemo if you want to try it out.

 

It would be good to get any feedback on this too, positive or negative.

 

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

2 REPLIES 2
DamoBird365
Super User
Super User

Hi @bordo 

 

I've done something similar with an Excel Template File and you can even convert this to PDF if need be.

 

Take a look here https://www.damobird365.com/perfectly-complete-an-excel-invoice-template/

 

I've done a demo where I took this to the next level, with multiple customer and transaction data stored in SharePoint Lists, the same concept applies to Excel Tables, if that is how you choose to store your data.

 

https://www.youtube.com/watch?v=LHDfyxf9BNc&list=PLzq6d1ITy6c2_qM_ocYDtEaENrqi92YmM I blabber on a bit in the first 3 minutes but hopefully the rest of the demo is useful to you.

 

I've uploaded the scripts and flow to https://github.com/DamoBird365/PowerAutomate/tree/main/ExcelInvoiceDemo if you want to try it out.

 

It would be good to get any feedback on this too, positive or negative.

 

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 😉

bordo
Frequent Visitor

Hi Damien! @DamoBird365 

 

Your videos are super helpful and I've watched them all to compose my own flow. It is slightly different than yours as my scenario was not the same. My script causes an error when I run the script. It says "InventoryArray is undefined" . However, I've defined interface for arrayvar [ ] at the end. The flow runs okay but the script won't populate the excel template for some reason. It populates the facultyName string just fine, but the array is not populating. The output of the select function is correct, so I know that everything up to the 'Run Script' step works. I would appreciate any insights. Thanks a lot!

 

Here is my script:

function main(
  
  workbook: ExcelScript.Workbook,
  facultyName: string,
  InventoryArray: arrayvar[]

  ){
 
  let worksheet = workbook.getWorksheet("ReportTemplate");

  worksheet.getRange("C10").setValue(facultyName);

  const startInventory = 17;

  for (let i= 0; i < InventoryArray.length; i++) {
    const currentObject = InventoryArray[i];

    const formattedrow = [[currentObject.Title, currentObject.Model, currentObject.Manufacturer, currentObject.Serial, currentObject.WarrantyStatus, currentObject.WarrantyEnd,]];
    
    const rowRange = `B${startInventory + i}:G${startInventory + i}`;
    worksheet.getRange(rowRange).setValues(formattedrow);
  }

  return "List" + InventoryArray.length + "Records Loaded in Excel"
  
}
  
interface arrayvar {
  Titlestring,
  Modelstring,
  Manufacturerstring,
  Serialstring,
  WarrantyStatusstring,
  WarrantyEndstring,
}





And here is my flow:

 

 

bordo_0-1624596638323.png

 

bordo_0-1624635944544.png

 

 

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
Top Kudoed Authors
Users online (3,396)