Create personalized HTML Table for customers from Excel spreadsheets

[bordo]

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! 🙂

[DamoBird365 · Accepted solution]

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]

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 {

  Title: string,

  Model: string,

  Manufacturer: string,

  Serial: string,

  WarrantyStatus: string,

  WarrantyEnd: string,

}

And here is my flow: