Optimizing Google Sheets Script for Adding Dropdowns and Formulas to Multiple Rows

Question

I'm working on a Google Sheets script where I need to dynamically update a sheet with dropdown menus and apply specific formulas to rows based on certain conditions. My current approach involves iterating over an array of objects (maintainedProcesses), setting formulas for "Destination Unit" and "Responsible Manager" columns if they are not already set, and then updating the sheet with these changes. Additionally, I create dropdown menus for several columns using validation rules based on data fetched from another sheet ("Data Validations").

Here's the relevant portion of my code (simplified and generic for clarity):

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

The setDropdownMenu method is defined as follows:

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

While this code works, the process of adding each dropdown menu sequentially feels slow, especially with a large number of rows or when multiple dropdowns are involved. I'm looking for ways to optimize this process, ideally allowing for faster execution and possibly adding all dropdowns in a more efficient manner.

Is there a better approach or best practice for applying these kinds of updates to a Google Sheets document via Apps Script, particularly for reducing the time it takes to add dropdowns and formulas across many rows?

Comments:

I'm particularly interested in any strategies that can batch these operations or make them more efficient. My environment is Google Apps Script with access to the Google Sheets API. Thank you for your insights!

Additional function of the sheetmaneger class

/**
       * @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);
  }

Answer 1

I believe your goal is as follows.

• You want to reduce the process cost of your script.
• You want to modify only the function setDropdownMenu. Especially, you want to modify the script for putting the data validations to the cells.

In this case, how about putting the data validations using Sheets API? When Sheets API is used for the function setDropdownMenu, it becomes as follows.

Modified script:

Before you use this script, please enable Sheets API at Advanced Google services.

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 ss = SpreadsheetApp.getActiveSpreadsheet(); // Added
  const sheet = ss.getActiveSheet(); // Modified

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

  const requests = [];
  if (options.length <= 500) {

    // Modified
    requests.push({
      repeatCell: {
        cell: { dataValidation: { condition: { values: options.map(e => ({ userEnteredValue: e })), type: "ONE_OF_LIST" }, showCustomUi: true } },
        range: { sheetId: sheet.getSheetId(), startRowIndex: 1, endRowIndex: lastRow, startColumnIndex: columnId - 1, endColumnIndex: columnId },
        fields: "dataValidation"
      }
    });

  } else {
    const dropdownSheetName = "DropdownLists";
    let dropdownSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(dropdownSheetName);
    if (!dropdownSheet) {
      dropdownSheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet(dropdownSheetName);
    }
    const startRow = dropdownSheet.getLastRow() + 1;
    const optionsColumn = options.map(option => [option]);
    dropdownSheet.getRange(startRow, 1, options.length, 1).setValues(optionsColumn);
    const validationRange = `${dropdownSheetName}!A${startRow}:A${startRow + options.length - 1}`;

    // Modified
    requests.push({
      repeatCell: {
        cell: { dataValidation: { condition: { values: [{ userEnteredValue: `=${validationRange}` }], type: "ONE_OF_RANGE" }, showCustomUi: true } },
        range: { sheetId: sheet.getSheetId(), startRowIndex: 1, endRowIndex: lastRow, startColumnIndex: columnId - 1, endColumnIndex: columnId },
        fields: "dataValidation"
      }
    });

  }

  // Added
  if (requests.length == 0) return;
  SpreadsheetApp.flush(); // This line might not be required to be used.
  Sheets.Spreadsheets.batchUpdate({ requests }, ss.getId());
}

• This modified script shows the same result as your original script.

References:

• Method: spreadsheets.batchUpdate
• RepeatCellRequest