如何基于现有Discord机器人代码实现Google Sheets消息计数

我基于Node.js开发的Discord机器人已实现基础功能：当用户发送消息时，机器人会检查Google Sheets中的用户ID列表，若用户ID不存在，则将用户信息（用户名、ID、初始消息数1、语音时长、入服时间）添加至列表。现需扩展消息计数功能：当检测到用户ID已存在时，定位到对应行并将「Nachrichten（消息）」列的数值加1，求基于现有代码的最优实现方案。

现有代码：

const fs = require('fs').promises;
const path = require('path');
const process = require('process');
const {authenticate} = require('@google-cloud/local-auth');
const {google} = require('googleapis');
const axios = require('axios');
const moment = require('moment');
const SCOPES = ['https://www.googleapis.com/auth/spreadsheets.readonly'];

// The file token.json stores the user's access and refresh tokens, and is
// created automatically when the authorization flow completes for the first
// time.

const TOKEN_PATH = path.join(process.cwd(), 'token.json');
const CREDENTIALS_PATH = path.join(process.cwd(), 'credentials.json');

module.exports = client => {



client.on('messageCreate', async message => {
  const member = message.member

  async function loadSavedCredentialsIfExist() {
    try {
      const content = await fs.readFile(TOKEN_PATH);
      const credentials = JSON.parse(content);
      return google.auth.fromJSON(credentials);
    } catch (err) {
      return null;
    }
  }
  
  /**
   * Serializes credentials to a file comptible with GoogleAUth.fromJSON.
   *
   * @param {OAuth2Client} client
   * @return {Promise<void>}
   */
  async function saveCredentials(client) {
    const content = await fs.readFile(CREDENTIALS_PATH);
    const keys = JSON.parse(content);
    const key = keys.installed || keys.web;
    const payload = JSON.stringify({
      type: 'authorized_user',
      client_id: key.client_id,
      client_secret: key.client_secret,
      refresh_token: client.credentials.refresh_token,
    });
    await fs.writeFile(TOKEN_PATH, payload);
  }
  
  /**
   * Load or request or authorization to call APIs.
   *
   */
  async function authorize() {
    let client = await loadSavedCredentialsIfExist();
    if (client) {
      return client;
    }
    client = await authenticate({
      scopes: SCOPES,
      keyfilePath: CREDENTIALS_PATH,
    });
    if (client.credentials) {
      await saveCredentials(client);
    }
    return client;
  }
  
  /**
   * 
   * @see https://docs.google.com/spreadsheets/d/12e_460eFvxYS5m8AMRg17uBlfEEzjpOfPqgbX0YIeEE/edit
   * @param {google.auth.OAuth2} auth The authenticated Google OAuth client.
   */
  async function listMajors(auth) {
    const sheets = google.sheets({version: 'v4', auth});
    const res = await sheets.spreadsheets.values.get({
      spreadsheetId: '12e_460eFvxYS5m8AMRg17uBlfEEzjpOfPqgbX0YIeEE',
      range: 'Liste!B2:B',
    });
    const rows = res.data.values;
     const rowsdata = res.data.values.join(', ');
    if (rowsdata.includes(`${member.id}`)) {
      // Here i need the bot edit the Message Line
     return;
    } else 
    axios.post('https://sheetdb.io/api/v1/gfycyawty283n', {
      data: {
        Name: `${member.user.tag}`,
        ID: `${member.id}`,
        Nachrichten: `1`,
        VoiceTime: "00:00:00:00",
        ServerBeigetreten: moment.utc(member.joinedAt).format('DD/MM/YY HH:MM:SS')
      }
    })
    
    
  }
  
  authorize().then(listMajors).catch(console.error);
  

  })  


};

1. 调整API权限 #

要修改表格内容，必须将只读权限改为读写权限：

const SCOPES = ['https://www.googleapis.com/auth/spreadsheets']; // 移除readonly后缀

2. 优化用户ID查找逻辑 #

原代码用字符串拼接后判断包含的方式容易出现误判（比如ID包含其他ID片段），改为直接遍历行数据定位用户ID的位置：

• 获取完整的用户数据行（从A2开始，覆盖所有列），而非仅ID列
• 遍历找到用户ID所在的索引，计算对应的工作表行号（因为从第2行开始，行索引+2）

3. 实现消息数递增更新 #

使用Google Sheets官方API的values.update方法，先获取当前消息数，加1后更新到对应单元格。为保证原子性，也可以用values.batchUpdate，但单单元格更新用update更简洁。

4. 统一用官方API操作 #

原代码新增用户用了SheetDB第三方API，建议统一使用Google Sheets官方API，减少依赖，提升稳定性。

修改后的完整代码 #

const fs = require('fs').promises;
const path = require('path');
const process = require('process');
const { authenticate } = require('@google-cloud/local-auth');
const { google } = require('googleapis');
const moment = require('moment');
// 改为读写权限
const SCOPES = ['https://www.googleapis.com/auth/spreadsheets'];

const TOKEN_PATH = path.join(process.cwd(), 'token.json');
const CREDENTIALS_PATH = path.join(process.cwd(), 'credentials.json');
const SPREADSHEET_ID = '12e_460eFvxYS5m8AMRg17uBlfEEzjpOfPqgbX0YIeEE';
const SHEET_NAME = 'Liste';

module.exports = client => {
  client.on('messageCreate', async message => {
    // 忽略机器人自身消息，避免重复计数
    if (message.author.bot) return;
    const member = message.member;
    if (!member) return;

    async function loadSavedCredentialsIfExist() {
      try {
        const content = await fs.readFile(TOKEN_PATH);
        const credentials = JSON.parse(content);
        return google.auth.fromJSON(credentials);
      } catch (err) {
        return null;
      }
    }

    async function saveCredentials(client) {
      const content = await fs.readFile(CREDENTIALS_PATH);
      const keys = JSON.parse(content);
      const key = keys.installed || keys.web;
      const payload = JSON.stringify({
        type: 'authorized_user',
        client_id: key.client_id,
        client_secret: key.client_secret,
        refresh_token: client.credentials.refresh_token,
      });
      await fs.writeFile(TOKEN_PATH, payload);
    }

    async function authorize() {
      let client = await loadSavedCredentialsIfExist();
      if (client) {
        return client;
      }
      client = await authenticate({
        scopes: SCOPES,
        keyfilePath: CREDENTIALS_PATH,
      });
      if (client.credentials) {
        await saveCredentials(client);
      }
      return client;
    }

    async function handleUserMessage(auth) {
      const sheets = google.sheets({ version: 'v4', auth });
      // 获取所有用户数据行（从A2开始），列范围根据实际表格调整
      const res = await sheets.spreadsheets.values.get({
        spreadsheetId: SPREADSHEET_ID,
        range: `${SHEET_NAME}!A2:E`, // 假设列顺序：A=用户名, B=ID, C=消息数, D=语音时长, E=入服时间
      });
      const rows = res.data.values || [];
      const userId = member.id;
      // 查找用户所在行的索引
      const userRowIndex = rows.findIndex(row => row[1] === userId);

      if (userRowIndex !== -1) {
        // 用户存在，更新消息数
        const currentMsgCount = parseInt(rows[userRowIndex][2] || 0);
        const newMsgCount = currentMsgCount + 1;
        // 计算目标单元格：C列（消息数列），行号=索引+2（因为从第2行开始）
        const targetRange = `${SHEET_NAME}!C${userRowIndex + 2}`;
        
        await sheets.spreadsheets.values.update({
          spreadsheetId: SPREADSHEET_ID,
          range: targetRange,
          valueInputOption: 'RAW',
          requestBody: {
            values: [[newMsgCount]]
          }
        });
      } else {
        // 用户不存在，新增行数据
        const newRow = [
          member.user.tag,
          userId,
          1,
          "00:00:00:00",
          moment.utc(member.joinedAt).format('DD/MM/YY HH:MM:SS')
        ];
        await sheets.spreadsheets.values.append({
          spreadsheetId: SPREADSHEET_ID,
          range: `${SHEET_NAME}!A:E`,
          valueInputOption: 'RAW',
          requestBody: {
            values: [newRow]
          }
        });
      }
    }

    authorize().then(handleUserMessage).catch(console.error);
  });
};

关键说明 #

• 新增了忽略机器人消息的判断，避免无效计数
• 统一使用Google Sheets官方API完成新增和更新操作，移除第三方依赖
• 代码中假设了表格列顺序，若你的表格列布局不同，需调整对应索引和范围参数
• 使用values.append新增行、values.update更新消息数，逻辑清晰且符合官方API规范

内容的提问来源于stack exchange，提问作者Marc-Anthony