I have a set of areas where multiple tests were ran at different locations. If a test doesn't pass, locations are retested on a different date.

Currently, below function that was posted as a solution by @TheWizEd to my question (

Present Output:

Intended Output:

CodePudding user response：

Modification points:

• In your script, the 1st column of the source sheet is checked. In order to achieve your goal, I thought that 1st and 2nd columns are required to be checked.
• About the output values in your script, only 2 elements are included in each array of results values. In order to achieve your goal, 3 elements are required to be included in each array.

When these points are reflected in your script, how about the following modification?

Modified script:

function Summary() {
  const SheetName03 = "Sheet1"; // Please set the sheet name of source sheet.
  const SheetName04 = "Sheet2"; // Please set the sheet name of destination sheet.
  const SpreadsheetID = "###"; // Please set your Spreadsheet ID.

  const ss = SpreadsheetApp.openById(SpreadsheetID);
  const sheet = ss.getSheetByName(SheetName03);
  const sheet04 = ss.getSheetByName(SheetName04);
  const [, ...values] = sheet.getDataRange().getValues();
  const obj = values.reduce((m, [a, b, c, ...d]) => {
    const k = `${a}@@@${b.toISOString()}`;
    return m.set(k, m.has(k) ? [...m.get(k), ...d] : d);
  }, new Map());
  const results = [...obj].map(([k, v]) => {
    const [a, b] = k.split("@@@");
    return [a, new Date(b), v.includes("Fail") ? "Fail" : [...new Set(v)][0] == "Pass" ? "Pass" : "Data Missing"]
  });
  sheet04.getRange(2, 1, results.length, results[0].length).setValues(results);
}

• When this script is run, the columns "A" and "B" of the source sheet are checked, and an array for putting to the destination sheet is created and the array is put to the destination sheet.

Note:

• In this modified script, it supposes that the values of column "B" of the source sheet are the date object. Please be careful about this.

• In this modified script, your showing sample Spreadsheet is used. So, when you change the sheet or your actual sheet is different from your showing sheet, this script might not be able to be used. Please be careful about this.

References:

• reduce()
• map()