如何优化Google Apps Script中范围转数组及移位逻辑的执行效率

问题背景 #

我正在用Google Apps Script开发数据复制宏，需求为：将指定连续单行A1格式范围（如B2:H2）的数据复制到目标区域，但仅当原范围空单元格数量≤2时才执行复制。

为实现空单元格检查，我自定义了rangeToArray函数将范围字符串转为单个单元格地址数组，再循环调用sheet.getRange().isBlank()统计空单元格；同时开发rangeShift函数实现范围的列移位与长度调整，两个函数采用相同的手动解析A1范围的核心逻辑。

当前这部分逻辑执行耗时约1.8±0.2s，远高于同文档其他宏的0.3-1.4s。我刚接触JS一周，寻求这两个函数及空单元格检查逻辑的优化方案以降低执行时间。

原相关函数代码 #

rangeToArray函数 #

function rangeToArray(range) {
  var posColon = range.indexOf(":"); // position of the colon
  var posSecondLetter = 0; // position of the 2nd letter if there is one, the 1st one otherwise
  var posFourthLetter = posColon+1; // position of the 4th letter if there is one, the 3rd one otherwise
  var posTemp1 = -1;
  var posTemp2 = -1;
  var rangeArray = [];
  // Loops through all 26 letters to find if it matches with a potential 2nd or 4th letter
  for (var k = 0; k < 26; k++) {
    posTemp1 = range.indexOf(letterArray[k], 1);
    posTemp2 = range.indexOf(letterArray[k], posColon+2);
    if (posTemp1 != -1 && posTemp1 < posColon) {
      // it found what the 2nd letter is if there is one
      posSecondLetter = posTemp1;
    }
    if (posTemp2 != -1) {
      // it found what the 4th letter is if there is one
      posFourthLetter = posTemp2;
    }
  }
  // isolate the according column indicators of the 1st and 2nd cell as well as their row numbers
  var firstCellColumnIndex = numberOfLetter(range.slice(0, posSecondLetter+1));
  var secondCellColumnIndex = numberOfLetter(range.slice(posColon+1, posFourthLetter+1));
  var firstCellRowIndex = range.slice(posSecondLetter+1, posColon-posSecondLetter);
  var secondCellRowIndex = range.slice(posFourthLetter+1, range.length);
  //generating the array of cell inbetween and including them
  for (var row = firstCellRowIndex; row <= secondCellRowIndex; l++) {
    for (var col = firstCellColumnIndex; col  <= secondCellColumnIndex; m++) {
      rangeArray.push(letter(col)+row.toString());
    }
  }
  return rangeArray;
}

letter与numberOfLetter函数 #

function letter(number) {
  if (number <= 26) { //indexes from A to Z
    return listeLettres[number-1];
  } else if ((26 < number) && (number <= 52)) { //indexes from AA to AZ
    return "A"+listeLettres[number-27];
  }
}

function numberOfLetter(letter) {
  for (var i = 0; i < 26; i++) { //indexes from A to Z
    if (letter == listeLettres[i]) {
      return i+1;
    }
  }
  for (var j = 0; j < 26; j++) { //indexes from AA to AZ
    if (letter == "A"+listeLettres[j]) {
      return 26+j+1;
    }
  }
}

transfertUTI主函数 #

function transfertUTI(range) {
//Part 1: Checking if no more than 3 cells are empty so that it doesn't execute if it's not filled properly
  var rangeArray = rangeToArray(range); //Converts range to array of cells
  var emptyCells = 0;
  for (var q = 0; q < rangeArray.length; q++) {
    if (sheetUTI.getRange(rangeArray[q]).isBlank()) {
      emptyCells++; // counts how many cells in the range are empty
    }
  }
  if (emptyCells <= 2) {
//Part 2: Actualy do the program
    gatherIntoListIndexes(); //This generates listIndexes which is an array with the coordonates of the topleftmost cell of all tables in the document which is used for nearly everything
    var AvailableLine = searchBlank(sheetUTI, listIndexes[4], listIndexes[5], 2); //this looks for the topmost empty line in the table it's transfering to, and returns the corresponding range at the specified range length, in that case 3 columns long
    sheetUTI.getRange(range).copyTo(sheetUTI.getRange(AvailableLine), {contentsOnly:true}); //the copy function of app script which copies from the first table to the second table
    sheetUTI.getRange(rangeShift(AvailableLine, 3, 1)).uncheck(); //rangeShift takes in a range, shifts it by as many columns as specified (here 3), and reduces its length as specified (here 1), which corresponds to the 2 checkboxes to uncheck next to where the data was just copied
    sheetUTI.getRange(range).clearContent(); //Empties the data on the initial table

  }
}

rangeShift函数 #

function rangeShift(range, shift, length) {
  var posColon = range.indexOf(":");
  var posSecondLetter = 0;
  var posFourthLetter = posColon+1;
  var posTemp1 = -1;
  var posTemp2 = -1;
  for (var k = 0; k < 26; k++) {
    posTemp1 = range.indexOf(listeLettres[k], 1);
    postemp2 = range.indexOf(listeLettres[k], posdp+2);
    if (posTemp1 != -1 && posTemp1 < posColon) {
      posSecondLetter = posTemp1;
    }
    if (posTemp2 != -1) {
      posFourthLetter = posTemp2;
    }
  }
//1st cell's column index
  return letter(numberOfLetter(range.slice(0, posSecondLetter+1))+shift)+
//1st cell's row index
range.slice(posSecondLetter+1, posColon-posSecondLetter)+
":"+
//2nd cell's column index
letter(numberOfLetter(range.slice(posColon+1, posFourthLetter+1))+shift-length)+
//2nd cell's row index
range.slice(posFourthLetter+1, range.length);
}

优化方案 #

核心优化方向：减少Sheet API调用次数（每次API交互都是性能瓶颈）+ 用内置API替代手动A1范围解析（避免复杂易出错的字符串操作）

1. 空单元格检查逻辑优化：一次性读取范围值，本地统计 #

不需要拆分单元格逐个调用isBlank()，直接读取整个范围的数值数组，在本地统计空值，把N次API调用压缩为1次：

function countEmptyCellsInRange(sheet, rangeStr) {
  const range = sheet.getRange(rangeStr);
  const values = range.getValues()[0]; // 单行范围，直接取第一行数据
  let emptyCount = 0;
  for (const val of values) {
    if (val === "" || val === null) {
      emptyCount++;
    }
  }
  return emptyCount;
}

2. 替代rangeShift：直接用Range对象操作 #

放弃手动解析A1字符串，用Google Apps Script内置的Range对象方法直接计算目标范围，速度更快且无解析bug：

function getShiftedRange(sheet, rangeStr, colShift, lengthAdjust) {
  const originalRange = sheet.getRange(rangeStr);
  const startCol = originalRange.getColumn() + colShift;
  const endCol = originalRange.getLastColumn() + colShift - lengthAdjust;
  const row = originalRange.getRow();
  // 返回Range对象，若需要A1字符串可调用.getA1Notation()
  return sheet.getRange(row, startCol, 1, endCol - startCol + 1);
}

3. 废弃rangeToArray、letter、numberOfLetter函数 #

空单元格检查已用countEmptyCellsInRange实现，范围解析用内置Range对象替代，这三个函数完全不需要保留。

4. 优化后的主函数transfertUTI #

function transfertUTI(range) {
  // 优化空单元格统计逻辑
  const emptyCells = countEmptyCellsInRange(sheetUTI, range);
  
  if (emptyCells <= 2) {
    gatherIntoListIndexes();
    const AvailableLine = searchBlank(sheetUTI, listIndexes[4], listIndexes[5], 2);
    
    // 复制数据
    sheetUTI.getRange(range).copyTo(sheetUTI.getRange(AvailableLine), {contentsOnly:true});
    
    // 优化范围移位操作
    const shiftedRange = getShiftedRange(sheetUTI, AvailableLine, 3, 1);
    shiftedRange.uncheck();
    
    // 清空原范围
    sheetUTI.getRange(range).clearContent();
  }
}

// 新增空单元格统计辅助函数
function countEmptyCellsInRange(sheet, rangeStr) {
  const range = sheet.getRange(rangeStr);
  const values = range.getValues()[0];
  let emptyCount = 0;
  for (const val of values) {
    if (val === "" || val === null) {
      emptyCount++;
    }
  }
  return emptyCount;
}

// 新增范围移位辅助函数
function getShiftedRange(sheet, rangeStr, colShift, lengthAdjust) {
  const originalRange = sheet.getRange(rangeStr);
  const startCol = originalRange.getColumn() + colShift;
  const endCol = originalRange.getLastColumn() + colShift - lengthAdjust;
  const row = originalRange.getRow();
  return sheet.getRange(row, startCol, 1, endCol - startCol + 1);
}

优化效果说明 #

• 空单元格检查从N次API调用压缩为1次getValues()，耗时可降低80%以上；
• 范围解析用内置API替代手动字符串操作，不仅速度更快，还能支持超过AZ的列名（如BA、BB等）；
• 整体执行时间可降至0.5s以内，符合文档内其他宏的耗时区间。

内容的提问来源于stack exchange，提问作者UnderTrack