如何避免在同一日期范围内基于列值插入数据行? [英] How to avoid insertion of a row of data based on a column value within the same date range?

问题描述

我正在将数据从用户输入插入到工作表中.在插入之前，我要确保在给定的日期范围内已经存在具有相同电子邮件地址的行(第3列，代码中的值e + k3).如果已经存在，则不会插入该行，但会处理其他输入.怎么解决呢?感谢Tanaike.

I am inserting data to a sheet from user inputs. Before inserting I want to make sure that there is already a row having same email address (column 3, value e + k3 in the code) within the given date range. If it is already there, it will not insert that row but will process other inputs. How to solve it? Thanks to Tanaike.

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

推荐答案

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

You need to update the reduce function so you will not generate a new row when the existing data set already has a row with the input date and student ID.

这要求您将获取当前值的位置上移到步骤2，并扩展检索到的值以包括email列，以便可以在reduce函数中访问这些值.由于我们正在查看多个列，因此我们现在也不能使用.flat()技巧，但这很好，因为我们有.find()和.map()同样可以工作.

This requires you shift up where you get the current values to step #2 and expand what values you retrieve to include the email column so you can access those values in the reduce function. We also can't use the .flat() trick now since we are looking at multiple columns, but that's fine because we have .find() and .map() that will work just as well.

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

Now the reduce becomes step #3, and we add in our requirement. We use .find() to try and get the an existing record with the given date and student email. find returns undefined if it fails, so we can update our reduce condition to only push from just studentId != "" to studentId != "" && !existingEntry.

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

Because we changed the shape of dstCurrentValues to expand it and get email values, we need to change .flat() to .map(row => row[0]) to achieve the same shape for lastIndexOf.

为了所有读者的利益，变量名已从"A1"更新为"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);
}

这篇关于如何避免在同一日期范围内基于列值插入数据行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！