javascript - 为范围内的每个名称创建一个单独的工作表，并将每个名称下的值移动到每个工作表

Question

我有一个主列表，第 2 行中有几十个名称分布在一堆列 (A2:Z2) 中。每个名称下都有一个值和数据列表。

第 2 行	约翰	莎莉	詹姆士
第 3 行	价值	价值	价值
第 4 行	价值	价值	价值
第 5 行	价值		价值
第 6 行	价值		价值

每个名称都应创建到一个工作表中。

这是用于为第 2 行中的每个名称创建工作表的脚本：

function generateSheetByName() {
  const ss = SpreadsheetApp.getActive();
  var mainSheet = ss.getSheetByName('Master List');
  const sheetNames = mainSheet.getRange(2, 1, mainSheet.getLastRow(), 1).getValues().flat();
  sheetNames.forEach(n => ss.insertSheet(n));
}

我希望这个脚本不仅为每个名称创建一个工作表，而且还将每个名称下的所有值一直延续到相应列的最后一行。

例如，John 在 A2 中，A3:A 是应该传递到创建的工作表的值。Sally 是 B2 和 B3:B 是应该结转的值。

在约翰的工作表中 - “约翰”是 A1 中的标题，列值位于 A2:A

对于制作的每张纸，我还想手动添加其他值。例如，如果创建了“John”表，并在 A2:A22 中添加了 20 个值，我希望脚本在 B2:B22 中添加复选框。或者总是在 B1 中添加一个公式，例如“=counta(a2:a)”或其他东西。

我怎样才能通过有效的循环来做到这一点？请注意，这可能会创建 50 张纸并在每张纸上携带超过 10-50 个值

示例图像：

主列表：

每个名称都将创建一个看起来像这样的工作表

约翰的单子

标签： javascriptgoogle-apps-scriptgoogle-sheetsgoogle-sheets-api

Answer 1

我相信你的目标如下。

• 您想使用 Google Apps 脚本从第一张图片到第二张图片。
• 将值放入创建的工作表时，您希望将复选框插入“B”列，并希望将公式放入单元格“B1”。
• 您想减少脚本的处理成本。

在这种情况下，下面的示例脚本怎么样？

示例脚本：

在这个示例脚本中，为了降低脚本的处理成本，我使用了 Sheets API。当使用 Sheets API 时，流程成本将能够降低一点。因此，在使用此脚本之前，请在 Advanced Google services 中启用 Sheets API。

function generateSheetByName() {
  // 1. Retrieve values from "Master List" sheet.
  const ss = SpreadsheetApp.getActive();
  const mainSheet = ss.getSheetByName('Master List');
  const values = mainSheet.getRange(2, 1, mainSheet.getLastRow(), mainSheet.getLastColumn()).getValues();

  // 2. Transpose the values without the empty cells.
  const t = values[0].map((_, c) => values.reduce((a, r) => {
    if (r[c]) a.push(r[c]);
    return a;
  }, []));

  // 3. Create a request body for using Sheets API.
  const requests = t.flatMap((v, i) => {
    const sheetId = 123456 + i;
    const ar = [{ addSheet: { properties: { sheetId, title: v[0] } } }];
    const temp = {
      updateCells: {
        range: { sheetId, startRowIndex: 0, startColumnIndex: 0 },
        fields: "userEnteredValue,dataValidation"
      },
    };
    temp.updateCells.rows = v.map((e, j) => {
      if (j == 0) {
        return { values: [{ userEnteredValue: { stringValue: e } }, { userEnteredValue: { formulaValue: "=counta(a2:a)" } }] }
      }
      const obj = typeof (e) == "string" || e instanceof String ? { stringValue: e } : { numberValue: e }
      return { values: [{ userEnteredValue: obj }, { dataValidation: { condition: { type: "BOOLEAN" } } }] }
    });
    return ar.concat(temp);
  });

  // 4. Request to the Sheets API using the created request body.
  Sheets.Spreadsheets.batchUpdate({requests}, ss.getId());
}

笔记：

• 在这个示例脚本中，我使用了您的示例输入和输出情况。因此，当这些结构与您的实际情况不同时，脚本可能无法使用。请注意这一点。

参考：

• 方法：电子表格.batchUpdate