Google Apps Script表格操作接口调用缓慢，寻求优化方案

问题背景 #

通过移动应用使用google.script.run调用以下Apps Script函数时，在1000行数据的表格中执行耗时2-3秒，客户端接收结果耗时5-6秒。经排查，性能瓶颈集中在SpreadsheetApp.openById(getSpreadsheetId())和sheet.getDataRange().getValues()方法，需针对性优化。

原函数代码 #

function addOrUpdateEntry(response) {
      
     let sheetName = "X";
    var lock = LockService.getDocumentLock(); //Use Document Lock as we can also be editting these fields. 
    var lockTimeoutMS = 5000; // 5,000 ms = 5 seconds

    var ss = SpreadsheetApp.openById(getSpreadsheetId())
    var sheet = ss.getSheetByName(sheetName);
     
    var data = JSON.parse(response);

    var a= data.a;
    var b= data.b;
    var c= data.c;
    var d= data.d;
    var f= data.f;
    var username = data.username;
    var todayDateDDMMYYYY = GetCurrentDateAsDDMMYYYY();
    var g= data.g;

    if (g< 0) {
        g= g* -1; //Make it positive.
    }

    if (lock) {
        var success = lock.tryLock(lockTimeoutMS);
        if (!success) {
            throw new Error("Unable to attain lock.");
        }
    }

    var dataRange;
    var values;

    try {
      
        dataRange = sheet.getDataRange();
        values = dataRange.getValues();
        var result = []

        /**
         * find list of rows to update
         */
        for (let i = 1; i < values.length; i++) {
            if (
                (values[i][TableToColumnLookup[sheetName]["a"]] == a) &&
                (values[i][TableToColumnLookup[sheetName]["b"]] == b) &&
                (values[i][TableToColumnLookup[sheetName]["c"]] == c) &&
                (values[i][TableToColumnLookup[sheetName]["d"]] == d)) {
                
                result.push(i);
            }
        }

        let newData = [a, b, c, d, e, g, username, todayDateDDMMYYYY];

        if (result.length > 0) {
          /**
           * update rows with new data
          */
            for (let j = 0; j < result.length; j++) {
                sheet.getRange(result[j] + 1, 1, 1, newData.length).setValues([newData]);
            }
        } else {
          /**
           * update rows with new data
           */
            sheet.appendRow(newData);
        }
        
        return JSON.stringify(true);

    }
    catch (err) {
        throw err;
    }
    finally {
        if (lock) {
            lock.releaseLock();
        }
    }
}

提速优化方案 #

1. 替换表格获取方式，减少网络开销 #

如果脚本是绑定在目标表格中的，直接用SpreadsheetApp.getActiveSpreadsheet()替代openById，避免通过ID打开表格的额外网络请求：

// 替换原表格获取代码
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName(sheetName);

2. 精准指定数据范围，避免全表扫描 #

getDataRange()会扫描整个表格寻找有数据的区域，当表格存在大量空行时会浪费资源。直接获取实际有数据的范围：

// 获取实际数据的最后一行
var lastRow = sheet.getLastRow();
// 假设表头在第1行，数据从第2行到lastRow，列数对应newData的长度（8列）
var dataRange = sheet.getRange(2, 1, lastRow - 1, 8);
var values = dataRange.getValues();

3. 使用TextFinder替代手动循环查找 #

Apps Script内置的TextFinder是底层优化的查找工具，比JavaScript手动循环快得多，适合多条件匹配场景：

// 转换列索引为1-based（TextFinder使用1索引）
var colA = TableToColumnLookup[sheetName]["a"] + 1;
var colB = TableToColumnLookup[sheetName]["b"] + 1;
var colC = TableToColumnLookup[sheetName]["c"] + 1;
var colD = TableToColumnLookup[sheetName]["d"] + 1;

// 分步筛选匹配行
var finder = sheet.createTextFinder(a).matchEntireCell(true).matchCase(false).findAll();
var matchingRows = [];
finder.forEach(result => {
  if (result.getColumn() === colA) {
    var row = result.getRow();
    if (sheet.getRange(row, colB).getValue() === b &&
        sheet.getRange(row, colC).getValue() === c &&
        sheet.getRange(row, colD).getValue() === d) {
      matchingRows.push(row);
    }
  }
});

4. 批量更新单元格，减少API调用次数 #

原代码循环调用setValues会产生多次网络请求，改为批量写入：

if (matchingRows.length > 0) {
  // 准备批量更新数据
  var updateData = matchingRows.map(row => newData);
  // 一次性写入所有匹配行
  var updateRange = sheet.getRange(matchingRows[0], 1, matchingRows.length, newData.length);
  updateRange.setValues(updateData);
} else {
  sheet.appendRow(newData);
}

5. 优化锁的持有时机 #

将锁的获取操作延后到实际需要操作表格时，减少锁的占用时间：

// 先处理参数和数据逻辑
var data = JSON.parse(response);
// ... 其他参数处理 ...

// 再获取锁
var lock = LockService.getDocumentLock();
var success = lock.tryLock(lockTimeoutMS);
if (!success) {
  throw new Error("Unable to attain lock.");
}

try {
  // 执行表格读写操作
} catch (err) {
  throw err;
} finally {
  lock.releaseLock();
}

6. 缓存常用数据 #

对列索引、最后行号等静态数据进行缓存，避免重复计算：

var cache = CacheService.getScriptCache();
var colLookupCacheKey = "col_lookup_" + sheetName;
var cachedColLookup = cache.get(colLookupCacheKey);
var colLookup;

if (cachedColLookup) {
  colLookup = JSON.parse(cachedColLookup);
} else {
  colLookup = TableToColumnLookup[sheetName];
  cache.put(colLookupCacheKey, JSON.stringify(colLookup), 300); // 缓存5分钟
}

额外注意事项 #

• 清理表格中的空列，减少getDataRange的扫描范围
• 确保GetCurrentDateAsDDMMYYYY()函数逻辑简洁，避免不必要的计算
• 检查TableToColumnLookup是否为静态数据，可提前定义为常量减少查找开销

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