google-apps-script - 如何避免根据同一日期范围内的列值插入一行数据？

Question

我正在从用户输入将数据插入工作表。在插入之前，我想确保在给定的日期范围内已经有一行具有相同的电子邮件地址（第 3 列，代码中的值 e + k3）。如果它已经存在，它将不会插入该行，但会处理其他输入。如何解决？感谢田池。

function onePeriodm(){
  
  const srcSheetName = "Dashboard";
  
  // 1. Retrieve values from the source sheet.
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const srcSheet = ss.getSheetByName(srcSheetName);
  const [[,,,,,k3],,[f5,g5,...h5i5j5k5]] = srcSheet.getRange("F3:K5").getDisplayValues();
  
  // 2. Convert the values for putting to the destination sheet.
  const dstValues = h5i5j5k5.reduce((ar, e) => {
    if (e != "") ar.push([g5, Number(e), e + k3, , , f5]);
    return ar;
  }, []);
  
  // 3. Put the converted values to the destination sheet.
  const dstSheet = ss.getSheetByName(f5);
  const dstCurrentValues = dstSheet.getRange(`A2:A${dstSheet.getLastRow()}`).getDisplayValues().flat();
  const index = dstCurrentValues.lastIndexOf(dstValues[0][0]) + 2;
  dstSheet.insertRowsAfter(index, dstValues.length);
  dstSheet.getRange(index + 1, 1, dstValues.length, dstValues[0].length).setValues(dstValues);
}

标签： google-apps-scriptgoogle-sheets

Answer 1

您需要更新该reduce函数，以便在现有数据集已包含输入日期和学生 ID 的行时不会生成新行。

这需要您将获取当前值的位置向上移动到步骤 #2，并扩展您检索到的值以包含电子邮件列，以便您可以在reduce函数中访问这些值。我们现在也不能使用这个.flat()技巧，因为我们正在查看多个列，但这很好，因为我们有.find()，.map()而且效果也一样。

现在reduce变成了第 3 步，我们添加了我们的需求。我们.find()用来尝试获取具有给定日期和学生电子邮件的现有记录。 find如果失败则返回undefined，因此我们可以将reduce条件更新为仅从 just 推studentId != ""送到studentId != "" && !existingEntry.

因为我们更改了 的形状dstCurrentValues以扩展它并获取电子邮件值，所以我们需要更改.flat()以.map(row => row[0])实现相同的形状lastIndexOf。

为了所有读者的利益，变量名称已从“A1”表示法更新为反映它们包含的值的名称。

function onePeriodm() {
  const srcSheetName = "Dashboard";

  // 1. Retrieve values from the source sheet.
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const srcSheet = ss.getSheetByName(srcSheetName);
  const [
    [, , , , , emailPattern],
    ,
    [courseCatalogId, dateString, ...studentIds],
  ] = srcSheet.getRange("F3:K5").getDisplayValues();

  // 2. Retrieve current values
  const dstSheet = ss.getSheetByName(courseCatalogId);
  const dstCurrentValues = dstSheet
    .getRange(`A2:C${dstSheet.getLastRow()}`) // expanded to include email column
    .getDisplayValues(); // not flattening since we have multiple columns now

  // 3. Convert the values for putting to the destination sheet.
  //    Do not include if there is already an email for this date in current values
  const dstValues = studentIds.reduce((array, studentId) => {
    const existingEntry = dstCurrentValues.find(
      (row) => row[0] === dateString && row[2] === studentId + emailPattern
    );
    if (studentId != "" && !existingEntry)
      array.push([
        dateString,
        Number(studentId),
        studentId + emailPattern,
        ,
        ,
        courseCatalogId,
      ]);
    return array;
  }, []);

  // 4. Put the converted values to the destination sheet.
  const index = dstCurrentValues.map((row) => row[0]).lastIndexOf(dateString);
  const row = index + 2;
  dstSheet.insertRowsAfter(row, dstValues.length);
  dstSheet
    .getRange(row + 1, 1, dstValues.length, dstValues[0].length)
    .setValues(dstValues);
}