Webhook同步Google Sheet：Particle IoT上报时替换而非追加行的实现咨询

解决方案 #

你当前代码行数持续增长是因为每次上报都直接调用appendRow追加新行，只需将追加逻辑替换为「先匹配设备ID再更新/追加」的逻辑即可，修改后完整代码如下：

function doPost(e) {
  // e.parameter.event
  // e.parameter.data
  // e.parameter.coreid
  // e.parameter.published_at "2016-04-16T13:37:08.728Z"

  var particleApiToken = '{{API Token Here}}';

  var publishedAt = new Date(e.parameter.published_at);
  var cacheKey = 'deviceName';
  var currentCoreId = e.parameter.coreid;

  var dataArray = [];
  try {
    dataArray = JSON.parse(e.parameter.data);
  }
  catch(e) {
  }
  
  var cache = CacheService.getScriptCache();
  var deviceNameCache = cache.get(cacheKey);
  
  if (!deviceNameCache) {
    // 设备名称未缓存，调用Particle云接口获取
    var result = UrlFetchApp.fetch('https://api.particle.io/v1/devices?access_token=' + particleApiToken);   
    var resultJson = JSON.parse(result.getContentText());
    
    deviceNameCache = {};

    for(var ii = 0; ii < resultJson.length; ii++) {
      deviceNameCache[resultJson[ii].id] = resultJson[ii].name;
    }
    cache.put(cacheKey, JSON.stringify(deviceNameCache));
  }
  else {
    deviceNameCache = JSON.parse(deviceNameCache);
  }
  
  // 优先使用设备名称，未知设备直接返回
  var deviceName = deviceNameCache[currentCoreId];
  if (!deviceName) {
    Logger.log('Unknown device ID');
    return;
  }

  var sheet = SpreadsheetApp.getActiveSheet();
  var row = [currentCoreId, deviceName, publishedAt];
  row = row.concat(dataArray);

  // 新增：查找匹配的设备ID行
  var targetRow = -1;
  // 读取第一列所有设备ID，默认第一行是表头，从第二行开始匹配
  var idColumn = sheet.getRange(1, 1, sheet.getLastRow(), 1).getValues();
  for (var i = 1; i < idColumn.length; i++) { // 数组下标从0开始，i=1对应表格第二行
    if (idColumn[i][0] === currentCoreId) {
      targetRow = i + 1; // 数组下标转表格行号
      break;
    }
  }

  if (targetRow > 0) {
    // 找到对应行，覆盖更新
    sheet.getRange(targetRow, 1, 1, row.length).setValues([row]);
  } else {
    // 未找到，追加新行
    sheet.appendRow(row);
  }

  var result = {};
  result.ok = true;

  return ContentService.createTextOutput(JSON.stringify(result))
    .setMimeType(ContentService.MimeType.JSON);
}

注意事项 #

• 如果你的表格没有表头，将循环逻辑里的i = 1改为i = 0即可
• 设备数量较多（大于1000台）时，可根据实际场景增加查询缓存优化运行效率
• 首次修改后建议测试前清理一次脚本缓存，避免旧缓存影响设备名称匹配

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