如何集成MQTT与Apps Script将设备返回数据写入Google Sheets

云原生阿岚

2026-8-27

可行性结论

完全可以通过Google Apps Script实现该集成需求，不需要额外部署中转服务，脚本可直接完成MQTT连接、指令发送、返回数据接收、Google Sheets单元格写入全流程。

具体操作步骤

打开你需要写入数据的目标Google Sheets文件，点击顶部菜单栏「扩展程序」-「Apps Script」，进入绑定当前表格的脚本编辑页，清空默认生成的空函数代码。
将下面的代码粘贴到脚本编辑器中，替换配置项里的打码内容为你在MQTTBox中测试通过的真实参数，同时指定你要写入数据的工作表名称和单元格位置：

// 替换以下配置为你的真实参数
const CONFIG = {
  mqttHost: 'node02.myqtthub.com',
  mqttPort: 1883,
  clientId: '你的真实MQTT Client ID',
  username: '你的真实MQTT用户名',
  password: '你的真实MQTT密码',
  pubTopic: 'GETTO/304470****rx', // 替换为你实际的发布主题
  subTopic: 'GETTO/304470****tx', // 替换为你实际的订阅主题
  sendPayload: '$ST',
  sheetName: 'Sheet1', // 替换为你要写入的工作表名
  targetCell: 'A1' // 替换为你要写入的目标单元格，例如B2、C5等
}

function fetchMqttDataToSheet() {
  // 建立TCP连接
  const socket = Utilities.newTcpClient(CONFIG.mqttHost, CONFIG.mqttPort);
  if (!socket.isConnected()) throw new Error('MQTT服务器连接失败，请检查网络和配置');

  // 构造MQTT CONNECT报文
  const clientIdBuf = Utilities.newBlob(CONFIG.clientId).getBytes();
  const usernameBuf = Utilities.newBlob(CONFIG.username).getBytes();
  const passwordBuf = Utilities.newBlob(CONFIG.password).getBytes();
  let connectPayload = [];
  // 协议名MQTT
  connectPayload.push(0,4,...Utilities.newBlob('MQTT').getBytes());
  // 协议等级4（对应MQTT 3.1.1），开启用户名密码标志
  connectPayload.push(4, 0xC2);
  // 保持连接10秒
  connectPayload.push(0, 10);
  // 写入Client ID
  connectPayload.push((clientIdBuf.length >> 8) & 0xFF, clientIdBuf.length & 0xFF, ...clientIdBuf);
  // 写入用户名
  connectPayload.push((usernameBuf.length >> 8) & 0xFF, usernameBuf.length & 0xFF, ...usernameBuf);
  // 写入密码
  connectPayload.push((passwordBuf.length >> 8) & 0xFF, passwordBuf.length & 0xFF, ...passwordBuf);
  // 组装CONNECT报文
  const connectPacket = [0x10, connectPayload.length, ...connectPayload];
  socket.write(connectPacket);

  // 读取CONNACK连接响应
  const connAck = socket.read(4);
  if (connAck[3] !== 0) throw new Error('MQTT认证失败，请检查用户名、密码、Client ID配置');

  // 构造SUBSCRIBE报文，订阅返回主题，QoS=0
  const subTopicBuf = Utilities.newBlob(CONFIG.subTopic).getBytes();
  let subPayload = [0, 1]; // 报文标识符
  subPayload.push((subTopicBuf.length >> 8) & 0xFF, subTopicBuf.length & 0xFF, ...subTopicBuf, 0);
  const subPacket = [0x82, subPayload.length, ...subPayload];
  socket.write(subPacket);
  // 读取SUBACK订阅响应
  socket.read(5);

  // 构造PUBLISH报文，发送查询指令$ST
  const pubTopicBuf = Utilities.newBlob(CONFIG.pubTopic).getBytes();
  const sendPayloadBuf = Utilities.newBlob(CONFIG.sendPayload).getBytes();
  let pubPayload = [];
  pubPayload.push((pubTopicBuf.length >> 8) & 0xFF, pubTopicBuf.length & 0xFF, ...pubTopicBuf, ...sendPayloadBuf);
  const pubPacket = [0x30, pubPayload.length, ...pubPayload];
  socket.write(pubPacket);

  // 读取设备返回的PUBLISH消息
  let returnData = '';
  const maxWait = 5000; // 最长等待5秒，可根据设备响应速度调整
  const startTime = Date.now();
  while (Date.now() - startTime < maxWait) {
    const available = socket.available();
    if (available > 0) {
      const data = socket.read(available);
      let ptr = 1;
      // 解析MQTT报文剩余长度
      let multiplier = 1;
      let remLen = 0;
      let byte;
      do {
        byte = data[ptr++];
        remLen += (byte & 127) * multiplier;
        multiplier *= 128;
      } while ((byte & 128) != 0);
      // 解析主题长度，跳过主题段
      const topicLen = (data[ptr] << 8) | data[ptr+1];
      ptr += 2 + topicLen;
      // 提取payload内容
      const payloadBytes = data.slice(ptr);
      returnData = Utilities.newBlob(payloadBytes).getDataAsString();
      break;
    }
    Utilities.sleep(100);
  }
  socket.close();

  if (!returnData) throw new Error('未收到设备返回数据，请检查主题配置和设备在线状态');

  // 将返回结果写入指定单元格
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(CONFIG.sheetName);
  sheet.getRange(CONFIG.targetCell).setValue(returnData);
}

代码粘贴替换完成后，点击脚本编辑器顶部的函数选择下拉框，选中fetchMqttDataToSheet函数，点击运行按钮。第一次运行会弹出Google权限授权提示，按照提示点击「高级」-「前往（你的项目名称）」，授予脚本操作表格、访问外部网络的权限即可，这是Apps Script的正常安全校验流程。
如果需要定时自动查询设备数据并写入表格，点击脚本编辑器左侧的「触发器」图标，添加新触发器，选择fetchMqttDataToSheet函数，设置你需要的触发周期（比如每分钟、每小时执行一次），保存后就会自动运行。

常见问题说明

如果运行提示连接失败，先确认你使用的MQTT服务没有拦截Google出口IP段，同时所有配置参数和你在MQTTBox中测试成功的参数完全一致，注意Client ID是MQTT服务端的强校验项，必须和你注册设备时分配的完全一致。
如果需要把返回的逗号分隔字符串拆分到不同单元格，只需要拿到returnData后，用returnData.split(',')拆分为数组，再调用setValues方法写入对应单元格区域即可。
如果你的设备响应较慢，可以把代码里maxWait的数值调大，单位是毫秒。

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