google-apps-script - 为 onLoad 事件优化 Google Sheets 函数

Question

我有一个包含多张工作表的电子表格。打开电子表格时，我想做一些事情：

1. 修剪表（e.source）；
2. 保护HeaderRows（e.source）；
3. 保护锁定列（e.source）；
4. hideApplicationMetadataSheets(e.source);

trimSheets ：修剪工作表中的所有空白行和列

protectHeaderRows ：对包含许多我不想搞砸的数组公式的标题行设置保护。保护设置为WarningOnly 并设置背景和文本颜色。

protectLockedColumns ：工作表上任何具有锁定值 '' (U+1F512) 的列也受到保护和突出显示。

hideApplicationMetadataSheets ：带有名称模式的工作表（例如 '/^(Audit:)*/'）我想隐藏

我已经使用类似于下面示例的模式来处理所有这些。在完成 4 个任务之前，完整的 onLoad 超时，耗时 > 30 秒。下面的示例用于修剪 10 张纸张大约需要 5 秒。

在没有处理的情况下通过工作表的迭代相对较快（< 100ms），但对于每个读取的属性都会显着增加。有没有办法更有效地做到这一点？

'use strict';

/***********************************************************************************************************************
 * Function to trim the contents of sheets reducing the size to the data grid only
 *
 * Version History
 * ---------------
 * 1.0 2020-08-26 
 **********************************************************************************************************************/ 
 
 
 
/**
 * Trim sheet to the data grid
 *
 * @param   {Sheet} the sheet to be trimmed.
 * @return  {Sheet} the sheet.       
 * @customfunction
 */
function trimSheet(sheet) {
//sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet

  _log_(`function trimSheet for sheet ${sheet.getName()}`, 'START_TIME' );

  if (sheet === undefined ) { 
    throw new Error ('function trimSheet() called without a sheet reference');
  }
  const dr = sheet.getDataRange();
  const sCol = dr.getLastColumn() + 1;
  const sRow = dr.getLastRow() + 1;
  const nCol = sheet.getMaxColumns() - sCol;
  const nRow = sheet.getMaxRows() - sRow;
  
  _log_(`Sheet : ${sheet.getName()}, columns : ${sCol} for ${nCol}, rows : ${sRow} for ${nRow}`);
 
  try {
    if (nCol > 0) {
      sheet.deleteColumns(sCol, nCol);
    }
    if (nRow > 0) {
      sheet.deleteRows(sRow, nRow);
    }
  } catch (e) {
    _log_(`function trimsheet failed for sheet ${sheet}`, 'WARNING' );
  }
  
  _log_(`function trimSheet for sheet ${sheet.getName()}`, 'END_TIME' );
  return sheet;
} // trimSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet


/**
 * Trim all sheets of a spreadsheet
 *
 * @param   {Spreadsheet} the spreadsheet to be trimmed.
 * @return  {Spreadsheet} 
 * @customfunction
 */
function trimSheets(spreadsheet) {
// spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

  _log_(`function trimSheets`, 'START_TIME' );
  if (spreadsheet === undefined ) { 
    throw new Error ('function trimSheets() called without a spreadsheet reference');
  }

  let sheets = spreadsheet.getSheets().map((s) => trimSheet(s));
  
  _log_(`function trimSheets`, 'END_TIME' );
  return spreadsheet;
} // trimSheets = SpreadsheetApp.getActiveSpreadsheet()

// function to test the above work
function __test_trimSheets() {
  trimSheets(SpreadsheetApp.getActiveSpreadsheet()); ;
}

记录：

'use strict';

/***********************************************************************************************************************
 * Function acts as a custom wrapper to the Google API Stackdriver logging functions, allowing the quantity and detail 
 * of logging to be configured project wide from one location.   
 *
 * @messageBody {object} input a string or json object containing the information to be logged.
 * @messageType {string} input the type of message to be logged.  The valid options permitted will be mapped as follows:
 *                        - INFO       (console.info)    High level information to confirm normal operation
 *                        - DEBUG      (console.log)     Low level information to support development or investigation
 *                        - PARAMETERS (console.log)     Json enclosed list of parameter names and values
 *                        - STATISTICS (console.log)     Data volumetric information
 *                        - WARNING    (console.warn)    Warning information to support risk monitoring
 *                        - ERROR      (console.error)   Failure information to support issue resolution
 *                        - START_TIME (console.time)    Start time information to measure function performance
 *                        - END_TIME   (console.timeEnd) End time information to measure function performance
 *
 * @return {boolean} output the function returns true if the information was logged successfully and false if an error 
 *                   was encountered. 
 *
 * Version History
 * ---------------
 * 1.0 2017-09-07 Created.
 **********************************************************************************************************************/
function _log_(messageBody, messageType = 'DEBUG' )
{
  //TODO
  //Add an additional check against a global logging flag used to adjust the detail/volume logged.

  var successful = true;
    
  try {
  
    if (G_LOGGING == LoggingMode.DISABLED) { return successful }
    else if (G_LOGGING == LoggingMode.CONSOLE) {
      
      //Logger.log('weve landed in CONSOLE');
      switch (messageType.toUpperCase()) {
        case 'INFO':
          console.info(messageBody);
          break;
        case 'DEBUG':
        case 'PARAMETERS':
        case 'STATISTICS':  
          console.log(messageBody);
          break;
        case 'WARNING':
          console.warn(messageBody);
          break;         
        case 'ERROR':
          console.error(messageBody);
          break; 
        case 'START_TIME':
          console.time(messageBody);
          break;
        case 'END_TIME':
          console.timeEnd(messageBody);
          break;
        default: 
          console.log(messageBody);
          console.error('Error.  _log_ does not recognise the message type "' + messageType + '"');
          successful = false;
      } 
    } else if (G_LOGGING == LoggingMode.LOGGER) {

      //If messageBody is JSON object then stringify it before it's printed in the log.
      //Otherwise the output will just put "object" rather than the data contained.
      if(typeof(messageBody) == 'object') {
        messageBody = JSON.stringify(messageBody);
      }
      
      //Also output to the editor log as an closer reference during development.  
      switch (messageType.toUpperCase()) {
        case 'INFO':
          Logger.log('[' + messageType + '] - ' + messageBody); 
          break;
        case 'DEBUG':
        case 'PARAMETERS':
        case 'STATISTICS':
          Logger.log('[' + messageType + '] - ' + messageBody); 
          break;
        case 'WARNING':
          Logger.log('[' + messageType + '] - ' + messageBody);
          break;         
        case 'ERROR':
          Logger.log('[' + messageType + '] - ' + messageBody); 
          break; 
        case 'START_TIME':
        case 'END_TIME':
          Logger.log('[' + messageType + '] Function ' + messageBody + '() - ' + (new Date())); 
          break;
        default: 
          Logger.log('[' + messageType + '] - ' + messageBody); 
          Logger.log('[ERROR] - _log_ does not recognise the message type "' + messageType + '"'); 
          successful = false;
      }        
  } else {
    throw new Error(`Logging Mode ${G_LOGGING} is not known. Check current value of G_LOGGING`);
  }
} catch(err) {
    console.error('Error.  _log_ encountered an error: ' + err);
    Logger.log('Error.  _log_ encountered an error: ' + err); 
    successful = false;
  }
  
  return successful;
}


function __test_log() {

  _log_(`test _log_`, 'START_TIME');
  let a = new Array(); 
  let testNum = 100;
  
  for (let i = 0; i < testNum; i++) {
    a.push(i);
  }

  a.forEach(x => { 
  _log_(`testing _log_ call ${x}`);
  _log_(`logging call ${x}`, 'START_TIME');
  _log_(`logging call ${x}`, 'END_TIME');
  
  })

  _log_(`test _log_`, 'END_TIME');

}

标签： google-apps-scriptgoogle-sheetscustom-function