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 😉

View solution in original post

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
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.

Top Solution Authors
Users online (2,540)