In a Mappings table, I am having an Exceptions column in which some cells contain strings that have multiple custom delimiters viz.,
Client1~Analyst1*Client2~Analyst2 etc
Mappings Table:
+---------------+------------------+----------------------------------------------------+
| Project Owner | Assigned Analyst | Exceptions |
+---------------+------------------+----------------------------------------------------+
| Nico Vera | Maple | Globus Ltd~Walter Lobo*Kevin Kline~Sarah Wick*Monsanto Ltd~Ana Wier |
+---------------+------------------+----------------------------------------------------+
| Vijay Malya | Sonny | |
+---------------+------------------+----------------------------------------------------+
| Sam Tucker | Parvati | Mars~Sonapuri*China Blue~Mona Dsa |
+---------------+------------------+----------------------------------------------------+
| Pessy Shroff | Roy | Harbinger Ltd~Jose Silva*Theos Ltd~Jay Mills |
+---------------+------------------+----------------------------------------------------+
From another FC Table as shown below:
FC Table:
+------+---------------+---------------+
| ID | Project Owner | Client |
+------+---------------+---------------+
| 1001 | Nico Vera | Globus Ltd |
+------+---------------+---------------+
| 1002 | Vijay Malya | Ventura |
+------+---------------+---------------+
| 1003 | Vijay Malya | Ventura |
+------+---------------+---------------+
| 1004 | Sam Tucker | Mocha Coffee |
+------+---------------+---------------+
| 1005 | Nico Vera | Roma Fashions |
+------+---------------+---------------+
| 1012 | Nico Vera | Monsanto Ltd |
+------+---------------+---------------+
| 1006 | Pessy Shroff | Murdoch Ltd |
+------+---------------+---------------+
| 1007 | Pessy Shroff | Harbinger Ltd |
+------+---------------+---------------+
| 1008 | Pessy Shroff | Theos Ltd |
+------+---------------+---------------+
| 1009 | Sam Tucker | Mars |
+------+---------------+---------------+
| 1013 | Sam Tucker | China Blue |
+------+---------------+---------------+
| 1010 | Nico Vera | Kevin Kline |
+------+---------------+---------------+
| 1014 | Nico Vera | Galettos |
+------+---------------+---------------+
Every time, I want to pull these unique project rows into another history table called Study Report table and for each Project Owner in these rows, check any exceptions in Exceptions column of the Mappings table.
If any exceptions given, then for any Client-Analyst combinations, I want to add the Client's corresponding Analyst, else add the default Analyst given in Assigned Analysts of Mappings table.
Study Report Table:
+------+---------------+---------------+------------------+
| ID | Project Owner | Client | Assigned Analyst |
+------+---------------+---------------+------------------+
| 1001 | Nico Vera | Globus Ltd | Walter Lobo |
+------+---------------+---------------+------------------+
| 1002 | Vijay Malya | Ventura | Sonny |
+------+---------------+---------------+------------------+
| 1003 | Vijay Malya | Fountain Pens | Sonny |
+------+---------------+---------------+------------------+
| 1004 | Sam Tucker | Mocha Coffee | Parvati |
+------+---------------+---------------+------------------+
| 1005 | Nico Vera | Roma Fashions | Maple |
+------+---------------+---------------+------------------+
| 1012 | Nico Vera | Monsanto Ltd | Ana Wier |
+------+---------------+---------------+------------------+
| 1006 | Pessy Shroff | Murdoch Ltd | Roy |
+------+---------------+---------------+------------------+
| 1007 | Pessy Shroff | Harbinger Ltd | Jose Silva |
+------+---------------+---------------+------------------+
| 1008 | Pessy Shroff | Theos Ltd | Jay Mills |
+------+---------------+---------------+------------------+
| 1009 | Sam Tucker | Mars | Sonapuri |
+------+---------------+---------------+------------------+
| 1013 | Sam Tucker | China Blue | Mona Dsa |
+------+---------------+---------------+------------------+
| 1010 | Nico Vera | Kevin Kline | Sarah Wick |
+------+---------------+---------------+------------------+
| 1014 | Nico Vera | Galettos | Maple |
+------+---------------+---------------+------------------+
Does anyone know how to split the Exceptions column and do this using Typescript for Excel? Any help would be most appreciated.
Edit:
....
// pick Opportunity Owner, FC Name, CD Team & Exceptions from Mapping sheet
let mapObj = MapTable.getRangeBetweenHeaderAndTotal().getValues()
.reduce((o, [a, b, c, d]) => Object.assign(o, { [a as string]: [b, c, d] }), {});
let existingIdsObj = StudyTable.getColumnByName("Study Number").getRangeBetweenHeaderAndTotal().getValues().reduce((o, [a]) => Object.assign(o, { [a as string]: true }), {});
let putValues:string[][] = FCTable.getRangeBetweenHeaderAndTotal().getValues().reduce((ar:string[][], [a, b, c, d, e, f, g, h, i, j, k, l,m]) => {
if (!existingIdsObj.hasOwnProperty(a as string)) {
// this is where i need help to define a loop for x
let x: string = (mapObj[d as string][2].trim().toString() !== '' && e.toString().toLowerCase().indexOf(mapObj[d as string][2].toString().toLowerCase().trim().split("~")[0]) !== -1) ? mapObj[d as string][2].trim().split("~")[1].toString() : mapObj[d as string][0].toString();
let y = h.toString();
switch (y) {
case "Active": y = "Open"; break;
case "Cancelled": y = "Cancelled"; break;
case "Completed": y = "Complete"; break;
default: y = "Check"; break;
}
ar.push([b, a, d, e, f, g, , , , , , y, , x, , , , c, k, m]);
}
return ar;
}, []);
// if new studies found, append them to Study Report sheet
if (putValues.length !== 0) {
StudyTable.addRows(-1, putValues);
}
}
...
I played with an office script, trying to isolate the task of splitting the exceptions and then testing a filter method that you might run as you create the final table. I'm not a developer but might be able to get you closer. Perhaps this is helpful...
function main(workbook: ExcelScript.Workbook) {
// simple array of the mapping table used for testing purposes
let exceptionsValues = [
["Nico Vera", "Maple", "Globus Ltd~Walter Lobo * Kevin Kline~Sarah Wick * Monsanto Ltd~Ana Wier"],
["Vijay Malya", "Sonny", ""],
["Sam Tucker", "Parvati", "Mars~Sonapuri * China Blue~Mona Dsa"],
["Pessy Shroff", "Roy", "Harbinger Ltd~Jose Silva * Theos Ltd~Jay Mills"]
];
// augment the original mappings array by creating a new row for each exception (split on the "*") and deleting the row with the concatenated exceptions
exceptionsValues.map((curRow, index) => {
if (curRow[2].indexOf("*") > 0) {
let tempRows = curRow[2].split(" * ");
for (let row of tempRows) {
exceptionsValues.push([curRow[0], curRow[1], row])
}
delete exceptionsValues[index]
};
});
// next as a proof of concept I filtered on values you are values in the study report
let filteredExceptions = exceptionsValues.filter(row => row[0] == "Nico Vera" && row[2].indexOf("Kevin") >= 0);
// from here you might make find the indexOf the "~" character and split the string to extract the Assigned Analysts name. At this point you already have a row for each exception so it should be relatively easy to extract
// the proof of concept would need to be run as you create each row in your study report table.
// a few console statements to see results
console.log(exceptionsValues);
console.log(filteredExceptions);
}