Office-Scripts: Add Client-Analysts combinations entered as exceptions in a column into another Table

Question

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);
  }

}
...

Have you explored using javascript/typescript array.find(), array.findIndex(), and str.split() methods to target the value and extract it for writing in the final table? (That's my first gut but I'm sure there are more efficient ways to do it.) What brings you to Office Scripts for this scenario vs something more formula bound? — Jeffrey, Feb 02 '22 at 05:30
It is to be done in Typescript because we are using the workbook in a browser in sharepoint folder. Do you know how to do it, especially the part where the Exceptions string for each Project Owner in Data table is picked from Mappings table and split on '*' first if it exists, then on '~', Else on '~'. Then each Client [0 index] is to be compared with Client from Data table and replaced. Then whole row is pushed into array. Finally dumped into Final table. Wait, let me share a snippet of what i have done and where i need help. — sifar, Feb 02 '22 at 05:52
@Jeffrey i have added my code and need help only with splitting the exception string and then adding exception analyst names for specific Clients for Project Owners. — sifar, Feb 02 '22 at 06:01
thanks for sharing. This gives me a better sense of your goals. — Jeffrey, Feb 03 '22 at 07:07

score 1 · Accepted Answer · answered Feb 03 '22 at 07:21

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);
}

@jeffery your code looks concise as well as interesting...though i used a different way to achieve the same...I will think of reusing your code instead... Thanks mate! :) — sifar, Feb 03 '22 at 17:06

Office-Scripts: Add Client-Analysts combinations entered as exceptions in a column into another Table

1 Answers1