Javascript 优化Google Sheet脚本以将下拉菜单和公式添加到多行

我正在处理一个Google Sheets脚本，其中我需要使用下拉菜单动态更新工作表，并根据特定条件将特定公式应用于行.我目前的方法包括迭代对象数组(MaintainedProcess)，设置"Destination Unit"和"Responsible Manager"列的公式(如果尚未设置)，然后使用这些更改更新工作表.此外，我使用基于从另一个工作表获取的数据的验证规则为几个列创建下拉菜单("数据验证").

以下是我的代码的相关部分(为清楚起见，简化和通用):

maintainedProcesses.forEach((process, index) => {
  const rowIndex = index + 2; // Adjust for header row
  if (!process['Destination Unit']) {
    process['Destination Unit'] = `=IFERROR(VLOOKUP(C:C;'Data Validations'!A:B,2,0);"")`;
  }
  if (!process['Responsible Manager']) {
    process['Responsible Manager'] = `=IFERROR(IF(E${rowIndex}="Screening";AA${rowIndex};"");"")`;
  }
});

await sheetManager.updateSheet(maintainedProcesses);

// Fetch validation data
const validationData = await sheetManagerValidations.readToJson();
const judicialActions = [...new Set(validationData.map(item => item['Judicial Action']))];
const subjects = [...new Set(validationData.map(item => item['Subject']))];
// etc. for other dropdowns

// Set dropdown menus
await sheetManager.setDropdownMenu(judicialActions, 'Judicial Action');
await sheetManager.setDropdownMenu(subjects, 'Subject');
// etc. for other dropdowns

SetDropdown Menu方法定义如下:

/**
 * Sets a dropdown menu for a specified column in the active sheet using data validation. 
 * If the number of options exceeds 500, it uses an auxiliary sheet to store the options due to validation limit.
 * @param {Array} options - The options for the dropdown menu.
 * @param {string} columnName - The name of the column to which the dropdown should be applied.
 */
async function setDropdownMenu(options, columnName) {
  if (!Array.isArray(options)) throw new TypeError('Parameter "options" is required and must be an array');
  if (typeof columnName !== 'string') throw new TypeError('Parameter "columnName" is required and must be a string');

  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const header = sheet.getDataRange().getValues()[0];
  const columnId = header.indexOf(columnName) + 1;
  const lastRow = sheet.getLastRow();

  if (options.length <= 500) {
    // Normal behavior for options within limit
    const rule = SpreadsheetApp.newDataValidation().requireValueInList(options, true).build();
    sheet.getRange(2, columnId, lastRow - 1, 1).setDataValidation(rule);
  } else {
    // Handling for a large number of options
    const dropdownSheetName = "DropdownLists";
    let dropdownSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(dropdownSheetName);
    if (!dropdownSheet) {
      dropdownSheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet(dropdownSheetName);
    }

    // Finding an empty row to insert new options
    const startRow = dropdownSheet.getLastRow() + 1;
    const optionsColumn = options.map(option => [option]);
    dropdownSheet.getRange(startRow, 1, options.length, 1).setValues(optionsColumn);

    // Update data validation to use the range
    const validationRange = `${dropdownSheetName}!A${startRow}:A${startRow + options.length - 1}`;
    const rule = SpreadsheetApp.newDataValidation().requireValueInRange(dropdownSheet.getRange(validationRange), true).build();
    sheet.getRange(2, columnId, lastRow - 1, 1).setDataValidation(rule);
  }
}

虽然这段代码可以工作，但按顺序添加每个下拉菜单的过程感觉很慢，特别是在行数较多或涉及多个下拉菜单的情况下.我正在寻找方法来优化这个过程，理想的情况是允许更快的执行，并可能以更有效的方式添加所有下拉列表.

有没有更好的方法或最佳实践来通过Apps脚本将这些类型的更新应用到Google Sheet文档中，特别是减少跨多行添加下拉列表和公式所需的时间？

comments :

我特别感兴趣的是任何可以批量处理这些操作或使其更高效的策略. 我的环境是Google Apps脚本，可以访问Google Sheet API. 感谢您的真知灼见！

SheetManeger类的附加函数

/**
       * @summary Overwrite the sheet with new provided data
       * @async
       * @example
       * const sheetManager = new SheetManager(sheetName);
       * const data = [
       *  {'ID': '1029', 'DATE': '09/20/2023'},
       *  {'ID': '1030', 'DATE': '09/22/2023'}
       * ]
       * sheetManager.updateSheet(data)
       *
       * @param {Array<Object>} data 1D array of objects
       * @param {Object} columnOptions Options for additional columns needed for writing
       * @param {Array} columnOptions.extraColumns Array of extra columns that may be needed
       * @param {String} columnOptions.individualSheetType Type of individual sheet: SCREENING, ACTION
       * @param {Object} clearOptions Options for sheet clearing
       * @param {Boolean} clearOptions.commentsOnly Clear comments only
       * @param {Boolean} clearOptions.contentsOnly Clear contents only
       * @param {Boolean} clearOptions.formatOnly Clear formatting only
       * @param {Boolean} clearOptions.validationsOnly Clear validations only
       * @param {boolean} force Forces the execution of overwrite
       * @returns {Promise<void>}
       */
      async updateSheet(data, columnOptions = {
        extraColumns: null,
        individualSheetType: null
      }, clearOptions = {
        commentsOnly: false,
        contentsOnly: false,
        formatOnly: false,
        validationsOnly: false
      }, force = false) {
        const onlyReadIds = [this.configs.getConfigurationSpreadsheetId(), this.configs.getMasterSpreadsheetId(), this.configs.getLayoutSpreadsheetId()];
        if (onlyReadIds.includes(this.spreadsheetId) && !force) {
          console.error(`Attempt to overwrite the spreadsheet with ID ${this.spreadsheetId}.\nCheck if asynchronous functions (async) are being properly awaited (await)`);
          throw new Error(`Attempt to overwrite the spreadsheet with ID ${this.spreadsheetId}.`);
        }
        if (!data) throw new TypeError('"data" parameter required');
        if (!Array.isArray(data)) throw new TypeError('"data" parameter must be of type Array<Object>');
        if (data.length > 0 && data.some(element => !this.isObject(element))) throw new TypeError('Elements of "data" must be of Object type');
    
        // Initialize headerManager. Headers filled via Layout
    const headerManager = new HeaderManager(new SheetManager(this.sheetName, this.configs.getLayoutSpreadsheetId(), this.oauth2Client));

    // Obtain the header of the active tab
    let newHeader = await headerManager.get(columnOptions);

    // Fill in the header without formatting
    headerManager.setStandardHeader(this.sheet, newHeader);

    // Clear content of the current tab
    const lastRow = this.sheet.getLastRow();
    const lastColumn = this.sheet.getLastColumn();
    const dataRange = this.sheet.getRange(2, 1, lastRow, lastColumn);
    dataRange.clear(clearOptions);
    dataRange.removeCheckboxes();

    // Convert array of objects to 2D data matrix
    const updatedDataMatrix = await this.jsonArrayToMatrix(data);

    // Fill in the standard formatted header
    headerManager.setStandardHeader(this.sheet, newHeader, true);

    // Get only the content (without header) of the data
    const dataContent = updatedDataMatrix.slice(1);

    // Return, if there is no content
    if (!dataContent || !dataContent[0]) return;

    // Overwrite active tab with content
    this.sheet.getRange(2, 1, dataContent.length, dataContent[0].length).setValues(dataContent);

    // Fill checkbox in the respective columns
    this.setCheckboxOnQuestionMark(updatedDataMatrix);
    this.setNotesOnSpecificColumns(updatedDataMatrix)

    // Format data according to the columns (date, currency, ...)
    headerManager.setColumnFormat(newHeader, this.sheet);
  }