I know this is not the full solution -- but for the core part of your solution you can use Office Scripts.
See here for more details:
https://docs.microsoft.com/en-us/office/dev/scripts/overview/excel
You can use Run Action on Excel online flow to first create an output that summarizes the leads by sales person.
Input table
(it's in a sheet called Sheet4)
Office Script
function main(workbook: ExcelScript.Workbook): ReturnTemplate {
const keyColName = 'Sales lead'
const table1 = workbook.getWorksheet('Sheet4').getTables()[0];
const keyColumnValues: string[] = table1.getColumnByName(keyColName).getRangeBetweenHeaderAndTotal().getValues().map(v => v[0] as string);
const uniqueKeys= [...Array.from(new Set(keyColumnValues))];
console.log(uniqueKeys);
const returnObj: ReturnTemplate = {}
uniqueKeys.forEach((key: string) => {
table1.getColumnByName(keyColName).getFilter()
.applyValuesFilter([key]);
const rangeView = table1.getRange().getVisibleView();
returnObj[key] = returnObjectFromValues(rangeView.getValues() as string[][]);
})
console.log(JSON.stringify(returnObj));
table1.getColumnByName(keyColName).getFilter().clear();
return returnObj
}
function returnObjectFromValues(values: string[][]): BasicObj[] {
let objArray = [];
let objKeys: string[] = [];
for (let i=0; i < values.length; i++) {
if (i===0) {
objKeys = values[i]
continue;
}
let obj = {}
for (let j=0; j < values[i].length; j++) {
obj[objKeys[j]] = values[i][j]
}
objArray.push(obj);
}
return objArray;
}
interface BasicObj {
[key: string] : string
}
interface ReturnTemplate {
[key: string]: BasicObj[]
}
Output:
{
"person1@mail.com": [{
"Sales lead": "person1@mail.com",
"Client name": "Foo1",
"Client contact": 123456789,
"Status": "Approach",
"Notes": ""
}, {
"Sales lead": "person1@mail.com",
"Client name": "Foo3",
"Client contact": 123456791,
"Status": "Approach",
"Notes": ""
}],
"person2@mail.com": [{
"Sales lead": "person2@mail.com",
"Client name": "Foo2",
"Client contact": 123456790,
"Status": "Approach",
"Notes": ""
}, {
"Sales lead": "person2@mail.com",
"Client name": "Foo4",
"Client contact": 123456792,
"Status": "Approach",
"Notes": ""
}],
"person3@mail.com": [{
"Sales lead": "person3@mail.com",
"Client name": "Foo5",
"Client contact": 123456793,
"Status": "Approach",
"Notes": "Note Intl time zone"
}],
"person4@mail.com": [{
"Sales lead": "person4@mail.com",
"Client name": "Foo6",
"Client contact": 123456794,
"Status": "Hold",
"Notes": ""
}]
}
You can then use this output in the next flow to send email.
If the output structure needs to change (perhaps an array with each element belonging to sales lead, you can alter the script to do it. Let me know if you need help.
