Суммарное произведение через Google Script PT2

В вашей ситуации, в качестве простой модификации, как насчет следующей модификации?

Модифицированный скрипт:

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

Тестирование:

Когда этот сценарий запускается в предоставленной вами электронной таблице, на листе «Набор данных до» получается следующий результат.

Добавлен

Судя по вашему следующему ответу,

Это увеличило время обработки примерно на 20 секунд (~ 70 000 строк в наборе данных, ~ 150 000 в данных завершения) в общей сложности на 1 минуту 10 секунд... но это не имеет большого значения.

В качестве простой модификации, как насчет замены getValues и serValues на Sheets API следующим образом?

Прежде чем использовать этот скрипт, включите Sheets API в расширенных сервисах Google.

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.info(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" });
}