提高 Google Apps 脚本中 Trace Dependents 脚本的性能

Question

我有一个 Google Apps 脚本，它将复制 Excel 的“跟踪依赖项”功能，方法是从整个工作表中查找单元格的所有依赖项，同时考虑命名范围。该脚本非常适合小型工作表，不幸的是，在处理较大的工作表时，脚本将在完成之前超时。我的工作表包含大约 100 万个单元格，脚本有时能够完全运行，但即使这样也需要大约 5 分钟，这相当长。

本质上，该脚本的工作原理是循环遍历工作表中的每个公式并对它们执行正则表达式测试，以查看公式是否包含审核的单元格引用或名称。

我想知道我的脚本中是否有任何快速的胜利可以帮助加快性能，或者是否有人对如何以某种方式进行改进有任何建议？

抱歉，如果这不是问此类问题的正确地方，如果有其他地方我应该问这个问题，请告诉我。

const getNamedRange = function (actSheet, cell) {
  //loop through the sheets named ranges and if the nr's range is the cell, then that name is the name of the current cell
  let matchedName;
  actSheet.getNamedRanges().forEach((name) => {
    if (name.getRange().getA1Notation() === cell) matchedName = name.getName();
  });
  return matchedName;
};

const isInRange = function (ss, currentCell, stringRange, j, k) {
  //extract the sheet name from the range if it has one
  const sheetName = stringRange[0].toString().match(/^(.*)!/)
    ? stringRange[0].toString().match(/^(.*)!/)[1]
    : null;
  //if there is a sheet name, get the range from that sheet, otherwise just get the range from the active sheet as it will be on the same sheet as audited cell
  const range = sheetName
    ? ss.getSheetByName(sheetName).getRange(stringRange)
    : ss.getActiveSheet().getRange(stringRange);

  const startRow = range.getRow();
  const endRow = startRow + range.getNumRows() - 1;
  const startCol = range.getColumn();
  const endCol = startCol + range.getNumColumns() - 1;

  const cellRow = currentCell.getRow();
  const cellCol = currentCell.getColumn();

  const deps = [];
  if (cellRow >= startRow && cellRow <= endRow && cellCol >= startCol && endCol <= endCol)
    deps.push([j, k]);

  return deps 
};

function traceDependents() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const currentCell = ss.getCurrentCell();
  const curCellRef = currentCell.getA1Notation();
  const dependentRefs = [];

  const sheets = ss.getSheets();
  const actSheet = ss.getActiveSheet();
  const actSheetName = actSheet.getName();
  const actIndex = actSheet.getIndex();

  //get the name of the cell
  const namedRange = getNamedRange(actSheet, curCellRef);

  //get the row and column text from the current cell
  const rowText = currentCell.getRow().toString();
  const columnText = curCellRef.substring(0, curCellRef.length - rowText.length);

  //If the sheet name has a space, then need to add the quote marks and ! as per Google Sheets standard
  const formattedActSheetName = actSheetName.includes(" ")
    ? `'${actSheetName}'!`
    : `${actSheetName}!`;

  for (let i = 0; i < sheets.length; i++) {
    const range = sheets[i].getDataRange();
    const formulas = range.getFormulas();
    const dependents = [];

    //If the sheet is the current sheet, then all references will not have the sheet ref, so it should be blank
    const curSheetRef = i === actIndex - 1 ? "" : formattedActSheetName;

    //create the tests to see if the formulas include the current cell
    const crRegex = new RegExp(
      `(?<!!|:)${curSheetRef}${curCellRef}(?![0-9])|` +
        `(?<!!|:)${curSheetRef}\\$${curCellRef}(?![0-9])|` +
        `(?<!!|:)${curSheetRef}[^$]${columnText}\\$${rowText}(?![0-9])|` +
        `(?<!!|:)${curSheetRef}\\$${columnText}\\$${rowText}(?![0-9])`
    );
    const nrRegex = new RegExp(`(?<!_)${namedRange}(?!_)`);

    //run through all of the cells in the sheet and test their formulas with the above to see if they are dependents
    for (let j = 0; j < formulas.length; j++) {
      const row = formulas[j];
      for (let k = 0; k < row.length; k++) {
        const cellFormula = row[k];

        if (crRegex.test(cellFormula) || nrRegex.test(cellFormula))
          dependents.push([j, k]);

        //check if the current cell formula includes a range in it, e.g. A1:A20, if it does, create a unique array with all the large ranges
        const largeRegex = new RegExp(
          `(?<!!|:|\\$)${curSheetRef}\\$?[A-Z]{1,3}\\$?([0-9]{1,7})?:\\$?[A-Z]{1,3}\\$?([0-9]{1,7})?`,
          "g"
        );
        const largeRange = [...new Set(cellFormula.match(largeRegex))];

        //if there are any large ranges, check if the range includes the audited cell. If it does, add the cell to the dependents
        if (largeRange) {
          largeRange.forEach((range) => {
            range.replaceAll("$", "");
            isInRange(ss, currentCell, range, j, k).forEach((dep) =>
              dependents.push(dep)
            );
          });
        }
      }
    }

    //Format the dependent's cell references with their sheet name to allow navigation to them
    for (let l = 0; l < dependents.length; l++) {
      const cell = range.getCell(dependents[l][0] + 1, dependents[l][1] + 1);
      dependentRefs.push(`${sheets[i].getName()}!${cell.getA1Notation()}`);
    }
  }

  //Add the current cell as the first element of the array
  dependentRefs.unshift(`${actSheetName}!${curCellRef}`);

  return [...new Set(dependentRefs)];
}