Javascript 谷歌应用程序脚本谷歌功能的代码优化

有没有可能减少函数的这三个部分的运行时间？该函数本身运行良好，但这三个部分加起来超过一秒钟.想知道是否有一种方法可以减少对SpreadSheetApp的调用(通过某种方式结合getSheet和getRange？)，或者是否可以更快地从Google Drive加载JSON文件.

var roster = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];个

function loadIconDictionary()个

var currentCell = sheet.getRange(currentRow, columnMappings[col].sourceCol).getValue();个

下面的代码和运行时.如果输入的单元格包括在字典中，该函数只判断编辑，如果包括，它会在该单元格的右侧放置一个图像.图标字典函数加载包含词典的json文件.

//Loads icon if a user fills in a class name
function userEdit(e) {
  console.time('myFunction');
  var sheet = e.source.getActiveSheet();
  var col = e.range.getColumn();
  var roster = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];
  console.timeEnd('myFunction');
  // Define the column mappings for your specific case
  var columnMappings = {
    1: { sourceCol: 1, targetCol: 2 },
    7: { sourceCol: 7, targetCol: 8 },
    13: { sourceCol: 13, targetCol: 14 }
  };

  // Check if the edited column is in the mappings
  if (columnMappings[col] && sheet != roster) {
    var currentRow = e.range.getRow();
    var targetCol = columnMappings[col].targetCol;
    console.time('myFunction2');
    if (Object.keys(ICON_DICTIONARY).length === 0) {
      ICON_DICTIONARY = loadIconDictionary(); // Load icon dictionary if not already loaded
    }
    console.timeEnd('myFunction2');
    console.time('myFunction3');
    try {
      var currentCell = sheet.getRange(currentRow, columnMappings[col].sourceCol).getValue();
    } catch(e) {
      Logger.log(e);
    }
    console.timeEnd('myFunction3');
    if (ICON_DICTIONARY.hasOwnProperty(currentCell)) {
      sheet.getRange(currentRow, targetCol).setValue('=IMAGE("' + ICON_DICTIONARY[currentCell] + '")');
    }
    else if (currentCell === "") {
      sheet.getRange(currentRow, targetCol).setValue('');
    }
  }
}

function loadIconDictionary() {
  var fileId = '1Qe_ST11nJIBnPGX_98N0Etd2iHiG70cd'; // Replace with the ID of your file

  // Get the file by ID
  var file = DriveApp.getFileById(fileId);

  if (file) {
    // Convert the blob to a string
    var fileContent = file.getBlob().getDataAsString();

    if (fileContent) {
      try {
        // Parse the content into a JavaScript object (assuming it's JSON)
        var parsedData = JSON.parse(fileContent);

        // Now 'parsedData' contains the content of the file
        return parsedData
      } catch (e) {
        Logger.log('Error parsing file content: ' + e);
      }
    } else {
      Logger.log('File is empty.');
    }
  } else {
    Logger.log('File not found.');
  }
}

Oct 4, 2023, 1:08:24 AM Debug   myFunction: 436ms
Oct 4, 2023, 1:08:24 AM Debug   myFunction2: 406ms
Oct 4, 2023, 1:08:25 AM Debug   myFunction3: 357ms

编辑:一次编辑多个单元格的功能:

//DO NOT CHANGE loadIconDictionary to getIconDictionary, onEdit does not have privileges for DriveApp!
function onEdit(e) { 
  SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Roster");
  var sheet = e.source.getActiveSheet();
  var col = e.range.getColumn();

  // Define the column mappings for your specific case
  var columnMappings = {
    1: { sourceCol: 1, targetCol: 2 },
    7: { sourceCol: 7, targetCol: 8 },
    13: { sourceCol: 13, targetCol: 14 }
  };

  // Check if the edited column is in the mappings
  if (columnMappings[col] && sheet != SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Roster")) {
    var currentRow = e.range.getRow();
    var numRows = 12; // Number of rows to update
    var targetCol = columnMappings[col].targetCol;
    var valuesToSet = [];  
    if (Object.keys(ICON_DICTIONARY).length === 0) {
      ICON_DICTIONARY = getIconDictionary(); // Load icon dictionary if not already loaded
    }
    for (var i = 0; i < numRows; i++) {
      var currentCell = sheet.getRange(currentRow + i, columnMappings[col].sourceCol).getValue();
      var cellValue = '';

      if (ICON_DICTIONARY.hasOwnProperty(currentCell)) {
        cellValue = '=IMAGE("' + ICON_DICTIONARY[currentCell] + '")';
      }
      else if (currentCell != "" || currentCell === false) {
        cellValue = sheet.getRange(currentRow + i, columnMappings[col].targetCol).getValue();
      }

      valuesToSet.push([cellValue]);
    }

    // Set values in a single batch operation
    sheet.getRange(currentRow, targetCol, numRows, 1).setValues(valuesToSet);
  }
}