Sumproduct through Google Script PT2

Question

This is a follow-up to a question I had asked some time ago. The person who answered it requested I set up a new question rather than modify the existing so here it goes.

Here is the sample document. This document has a total of 5 tabs:

1. Dataset Before - This is what the dataset will look like before processing.
2. Dataset Result (Old) - This is what the dataset looks like after processing with the current script.
3. Dataset Result (New Requirement) - This is what the dataset should look like after adding my new requirements.
4. Completion Data - This is the data that determines completion status.
5. Settings - This sheet will have the mappings of the different course codes per course.

Here is what I have currently:

  // This is from your provided Spreadsheet.
  const obj = [
    { name: "Dataset Before", range: "A2:A" },
    { name: "Completion Data", range: "A2:C" },
    { name: "Settings", range: "E5:I" }
  ];

  // 1. Retrieve values and ranges from 3 sheets.
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const [sheet1, sheet2, sheet3] = obj.map(({ name, range }, i) => {
    obj[i].sheet = ss.getSheetByName(name);
    obj[i].range = obj[i].sheet.getRange(range + obj[i].sheet.getLastRow());
    obj[i].values = obj[i].range.getValues();
    if (i == 1) {
      // For "Completion Data" sheet.
      // Create an object for searching values.
      obj[i].values = obj[i].values.reduce((o, [a, b, c]) => {
        if (c == "Completed") {
          o[b] = o[b] ? { ...o[b], [a]: c } : { [a]: c };
        }
        return o;
      }, {});
    } else if (i == 2) {
      // For "Settings" sheet.
      // Transpose the retrieved values.
      obj[i].len = obj[i].values[0].length;
      obj[i].values = obj[i].values[0].map((_, col) => obj[i].values.map((row) => row[col]));
    }
    return obj[i];
  });

  // Create an array for putting into the destination range by follwoing your logic.
  const values = sheet1.values.map(([a]) => {
    if (sheet2.values[a]) {
      return sheet3.values.map(e => e.some(f => sheet2.values[a][f]) ? "Completed" : "Incomplete");
    }
    return Array(sheet3.len).fill("Incomplete");
  });

  // Put the values to the destination range.
  sheet1.sheet.getRange(2, 2, values.length, values[0].length).setValues(values);
}

Currently the script does what I want and it does it in around 40 seconds. Really quick compared to what I had before. It searches for the ID of the person on the "Completion Data" tab and brings back "Completed" if the status says "Completed" or "Incomplete" if any other status is returned.

New Requirement: I now need it to bring back the completion date concatenated with the word "Completed" like Completed (04/24/2024) or bring back Incomplete (Enrolled) if the status shows "Enrolled", otherwise just Incomplete.

Unfortunately, for me I am not fully understanding what is going on in this script to make the adjustments I want. I tried creating something from scratch on my own using my existing knowledge but it involved loops within loops and took over 15 min per column. Execution time would not allow it all to finish.

My best guess when trying to adjust the code above was to make changes to return sheet3.values.map(e => e.some(f => sheet2.values[a][f]) ? "Completed" : "Incomplete"); but I kept getting errors. This is a little too advanced for me at the moment and I would really appreciate the help.

Please let me know if I'm missing anything or if anything is unclear so I can edit my post. Thanks.

Answer 1

In your situation, as a simple modification, how about the following modification?

Modified script:

function myFunction() {
  // This is from your provided Spreadsheet.
  const obj = [
    { name: "Dataset Before", range: "A2:A" },
    { name: "Completion Data", range: "A2:D" },
    { name: "Settings", range: "E5:I" }
  ];

  // 1. Retrieve values and ranges from 3 sheets.
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const [sheet1, sheet2, sheet3] = obj.map(({ name, range }, i) => {
    obj[i].sheet = ss.getSheetByName(name);
    obj[i].range = obj[i].sheet.getRange(range + obj[i].sheet.getLastRow());
    obj[i].values = obj[i].range.getDisplayValues();
    if (i == 1) {
      // For "Completion Data" sheet.
      // Create an object for searching values.
      obj[i].values = obj[i].values.reduce((o, [a, b, c, d]) => {
        if (c == "Completed") {
          o[b] = o[b] ? { ...o[b], [a]: `${c} (${d})` } : { [a]: `${c} (${d})` };
        } else if (c == "Enrolled") {
          o[b] = o[b] ? { ...o[b], [a]: `Incomplete (${c})` } : { [a]: `Incomplete (${c})` };
        }
        return o;
      }, {});
    } else if (i == 2) {
      // For "Settings" sheet.
      // Transpose the retrieved values.
      obj[i].len = obj[i].values[0].length;
      obj[i].values = obj[i].values[0].map((_, col) => obj[i].values.map((row) => row[col]));
    }
    return obj[i];
  });

  // Create an array for putting into the destination range by following your logic.
  const values = sheet1.values.map(([a]) => {
    if (sheet2.values[a]) {
      return sheet3.values.map(e => {
        const k = e.find(f => sheet2.values[a][f])
        return k ? sheet2.values[a][k] : "Incomplete";
      });
    }
    return Array(sheet3.len).fill("Incomplete");
  });

  // Put the values to the destination range.
  sheet1.sheet.getRange(2, 2, values.length, values[0].length).setValues(values);
}

Testing:

When this script is run to your provided Spreadsheet, the following result is obtained in "Dataset Before" sheet.

Added

From your following reply,

It increased the time to process by about 20 seconds (~70K rows on Dataset, ~150K on Completion Data) for a total of 1min 10sec...but that's not a big deal.

As a simple modification, how about replacing getValues and serValues with Sheets API as follows?

Before you use this script, please enable Sheets API at Advanced Google services.

function myFunction() {
  // This is from your provided Spreadsheet.
  const obj = [
    { name: "Dataset Before", range: "A2:A" },
    { name: "Completion Data", range: "A2:D" },
    { name: "Settings", range: "E5:I" }
  ];

  // 1. Retrieve values and ranges from 3 sheets.
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const ssId = ss.getId();
  const [sheet1, sheet2, sheet3] = obj.map(({ name, range }, i) => {
    obj[i].sheet = ss.getSheetByName(name);
    obj[i].range = obj[i].sheet.getRange(range + obj[i].sheet.getLastRow());

    // obj[i].values = obj[i].range.getDisplayValues();
    obj[i].values = Sheets.Spreadsheets.Values.get(ssId, `'${name}'!${range}`, { valueRenderOption: "FORMATTED_VALUE" }).values;

    if (i == 1) {
      // For "Completion Data" sheet.
      // Create an object for searching values.
      obj[i].values = obj[i].values.reduce((o, [a, b, c, d]) => {
        if (c == "Completed") {
          o[b] = o[b] ? { ...o[b], [a]: `${c} (${d})` } : { [a]: `${c} (${d})` };
        } else if (c == "Enrolled") {
          o[b] = o[b] ? { ...o[b], [a]: `Incomplete (${c})` } : { [a]: `Incomplete (${c})` };
        }
        return o;
      }, {});
    } else if (i == 2) {
      // For "Settings" sheet.
      // Transpose the retrieved values.
      obj[i].len = obj[i].values[0].length;
      obj[i].values = obj[i].values[0].map((_, col) => obj[i].values.map((row) => row[col]));
    }
    return obj[i];
  });

  // Create an array for putting into the destination range by follwoing your logic.
  const values = sheet1.values.map(([a]) => {
    if (sheet2.values[a]) {
      return sheet3.values.map(e => {
        const k = e.find(f => sheet2.values[a][f])
        return k ? sheet2.values[a][k] : "Incomplete";
      });
    }
    return Array(sheet3.len).fill("Incomplete");
  });

  // console.log(values)

  // Put the values to the destination range.
  // sheet1.sheet.getRange(2, 2, values.length, values[0].length).setValues(values);
  Sheets.Spreadsheets.Values.update({ values }, ssId, `'${sheet1.name}'!B2`, { valueInputOption: "USER_ENTERED" });
}