node.js - 如何复制 Google 表格并进行数据验证以保持完整？

Question

我有一个包含两张表的 Google 电子表格模板:数据输入 和数据验证。 数据验证表有许多列，其中包含与数据输入表上的匹配列的有效值。一切都按预期进行。我需要将这两张表复制到待测表 (SUT)。我正在使用工作表 API 来复制两张工作表。我首先复制Data Validation 表，然后复制Data Entry 表。这是代码，这似乎有效。

 const request =  {
    spreadsheetId :fromSpreadsheetId,
    sheetId : fromSheetId,
    resource:{
      destinationSpreadsheetId: toSpreadsheetId,
    },
  }
  const result = await  _sheetService.spreadsheets.sheets.copyTo(request)

在我的 SUT 上，两个工作表都会出现，并且数据输入工作表包含所有预期的下拉列表，并且它们都有正确的值。看起来很完美。问题是，当您从任何列的任何下拉列表中选择一个项目时，它会选择并输入正确的值，然后添加一个红色三角形和已输入无效值的消息。如果该列具有拒绝设置，则该值将被删除并出现错误对话框。

图像显示了两个单元格，我已从下拉列表中选择了视频类(class)。

如果我进入并重新选择要验证的列，请使用Data→DataValidation…，然后单击Save按钮该列开始工作，因此它会显示所有内容看起来是正确的，但工作表并不这么认为。有没有任何编程方式来强制我手动执行上述过程？我还需要在 sheets.copyTo 方法中执行其他操作才能使其正常工作吗？

编辑

该项目是用 Node.js 结合 TypeScript 和 JavaScript 编写的。用于与 Sheets API 通信并在电子表格之间复制工作表的较低级别代码可以在 this github file 中找到。 。该方法是copySheetFromTo，它位于文件的底部。

一个sample source sheet具有公共(public)查看权限

一个sample destination sheet具有公共(public)编辑权限

integration test使用上述两个文件复制工作表的文件位于文件底部，名称开头有“DEBUGGING TEST”(从第 209 行开始)

Answer 1

• 您想要复制包含数据验证的工作表。
• 使用复制的工作表时，数据验证的下拉菜单出现错误。
  • 您想要删除此错误。

如果我的理解是正确的，这个答案怎么样？在此答案中，为了消除错误，我覆盖复制表的数据验证作为解决方法。请将此视为几种解决方法之一。

适合您情况的流程如下。

流程:

1. 使用spreadsheet.get 方法从源电子表格中的Data Entry 工作表(“DataValidationTest”)检索所有数据验证。
2. 将源电子表格(“DataValidationTest”)中的数据输入工作表复制到目标电子表格(“公共(public)目标工作表”)。
3. 复制Data Entry 工作表后，将工作表名称从Copy of Data Entry 重命名为Data Entry。
4. 然后，使用电子表格.batchUpdate 方法将检索到的数据验证覆盖到Data Entry 工作表中。
   • 在这种情况下，电子表格.get 方法检索的数据验证的结构与电子表格.batchUpdate 方法的结构几乎相同。此解决方法使用了此方法。
5. 将源电子表格(“DataValidationTest”)中的数据验证工作表复制到目标电子表格(“公共(public)目标工作表”)。
6. 将数据验证副本的工作表名称重命名为数据验证。

示例脚本:

当您测试此脚本时，请设置变量。我认为 sheet.spreadsheets.get()、sheet.spreadsheets.batchUpdate() 和 sheet.spreadsheets.sheets.copyTo() 的 sheet 与脚本的 sheetOps 相同。

const srcSpreadsheet = "###";  // Please set this.
const tempDestSheetId = "###";  // Please set this.
const srcDataEntrySheetId = 0;  // Please set this.
const srcDataValidationSheetId = 123456789;  // Please set this.

let dataValidation = await sheet.spreadsheets.get({
  spreadsheetId: srcSpreadsheet,
  ranges: ["Data Entry"],
  fields: "sheets/data/rowData/values/dataValidation"
});
let data = dataValidation.data.sheets[0].data;
let rows = [];
for (let i = 0; i < data.length; i++) {
  if (data[i].rowData) {
    rows = data[i].rowData;
    break;
  }
}

sheet.spreadsheets.sheets.copyTo(
  {
    spreadsheetId: srcSpreadsheet,
    sheetId: srcDataEntrySheetId,
    resource: { destinationSpreadsheetId: tempDestSheetId }
  },
  (err, res) => {
    sheet.spreadsheets.batchUpdate(
      {
        spreadsheetId: tempDestSheetId,
        resource: {
          requests: [
            {
              updateSheetProperties: {
                fields: "title,sheetId",
                properties: { sheetId: res.data.sheetId, title: "Data Entry" }
              }
            },
            {
              updateCells: {
                rows: rows,
                range: { sheetId: res.data.sheetId },
                fields: "dataValidation"
              }
            }
          ]
        }
      },
      (er, re) => {
        if (err) {
          console.error(er);
          return;
        }
        console.log(re.data);
      }
    );
  }
);

let result1 = await sheet.spreadsheets.sheets.copyTo({
  spreadsheetId: srcSpreadsheet,
  sheetId: srcDataValidationSheetId,
  resource: { destinationSpreadsheetId: tempDestSheetId }
});

let result2 = await sheet.spreadsheets.batchUpdate({
  spreadsheetId: tempDestSheetId,
  resource: {
    requests: [
      {
        updateSheetProperties: {
          fields: "title,sheetId",
          properties: {
            sheetId: result1.data.sheetId,
            title: "Data Validation"
          }
        }
      }
    ]
  }
});

console.log(result2.data);

注意:

• 此脚本假定已能够使用 Sheets API。
• 这是经过简单修改的​​脚本，用于显示解决方法的流程。因此，请根据您的情况修改此设置。
• 重要的是，为了将数据验证覆盖到复制的Data Entry工作表中，需要在Data Entry工作表的复制完全完成后执行它。所以我像上面一样修改了脚本。

引用文献:

• Method: spreadsheets.batchUpdate
• Method: spreadsheets.get
• Method: spreadsheets.sheets.copyTo