如何实现Google Sheets履约日期前7天自动发送WhatsApp提醒？

一、先改造日期筛选逻辑 #

现有代码会发送所有行的消息，我们需要先筛选出距离当前日期刚好7天的履约记录，只给这些记录发提醒。

1. 添加日期校验函数 #

在代码中新增一个函数，用来判断当前日期是否是履约日期的前7天（统一日期格式，忽略时分秒差异）：

function isSevenDaysBeforeFulfilment(fulfilmentDateStr) {
  const fulfilmentDate = new Date(fulfilmentDateStr);
  const today = new Date();
  
  // 重置时分秒为0，只比较纯日期部分
  today.setHours(0, 0, 0, 0);
  fulfilmentDate.setHours(0, 0, 0, 0);
  
  // 计算两个日期的天数差
  const timeDiff = fulfilmentDate.getTime() - today.getTime();
  const dayDiff = Math.floor(timeDiff / (1000 * 60 * 60 * 24));
  
  // 只保留刚好差7天的记录
  return dayDiff === 7;
}

2. 修改数据获取后的筛选逻辑 #

在获取表格数据后，先过滤出符合条件的行，再执行发送：

sheets.spreadsheets.values.get({
    spreadsheetId: secrets.spreadsheet_id,
    range: 'Sheet1!A2:E',
    auth: authClient
})
.then(function (response) {
    const rows = response.data.values || [];
    // 筛选出需要发送提醒的行
    const eligibleRows = rows.filter(row => isSevenDaysBeforeFulfilment(row[0]));
    if (eligibleRows.length) {
        sendMessage(eligibleRows);
        console.log(`共找到${eligibleRows.length}条需要提醒的记录`);
    } else {
        console.log("今日无需要发送的履约提醒");
    }
})
.catch(function (err) {
    console.log(err);
});

二、实现代码自动定时运行 #

要让代码每天自动执行，有两种常用方案：

方案1：用Node.js定时包（适合本地/云服务器部署） #

使用node-schedule包实现定时任务，比如每天上午9点自动运行：

1. 先安装依赖：

npm install node-schedule

2. 修改代码入口，把数据获取逻辑包裹在定时任务里：

const schedule = require('node-schedule');

// 每天上午9点执行任务（可根据需求调整cron表达式）
// cron格式：秒 分 时 日 月 周
const job = schedule.scheduleJob({ hour: 9, minute: 0, timezone: 'Asia/Shanghai' }, function() {
    console.log('启动履约提醒任务...');
    sheets.spreadsheets.values.get({
        spreadsheetId: secrets.spreadsheet_id,
        range: 'Sheet1!A2:E',
        auth: authClient
    })
    .then(function (response) {
        const rows = response.data.values || [];
        const eligibleRows = rows.filter(row => isSevenDaysBeforeFulfilment(row[0]));
        if (eligibleRows.length) {
            sendMessage(eligibleRows);
            console.log(`共发送${eligibleRows.length}条提醒`);
        } else {
            console.log("今日无需要发送的履约提醒");
        }
    })
    .catch(function (err) {
        console.log(err);
    });
});

console.log('定时任务已启动，每天上午9点自动执行');

方案2：用云函数+定时触发器（适合无服务器部署） #

如果不想维护服务器，可以把代码部署到云函数（比如Google Cloud Functions、AWS Lambda、Vercel Functions），然后配置定时触发器：

• 以Google Cloud Functions为例：把代码打包上传，创建Cloud Scheduler触发器，设置每天执行一次，触发云函数的HTTP端点。
• 优势：无需自己维护服务器，按需运行，成本低。

三、完整修改后的代码 #

const { google } = require('googleapis');
const schedule = require('node-schedule');

// 配置JWT认证客户端
const privatekey = require("./privatekey.json");
const authClient = new google.auth.JWT(
    privatekey.client_email,
    null,
    privatekey.private_key,
    ['https://www.googleapis.com/auth/spreadsheets.readonly']
);

// 认证
authClient.authorize()
.then(function () {
    console.log("认证成功\n");
})
.catch(function (error) {
    throw (error);
});

// 加载敏感信息
const secrets = require("./secrets.json");
const sheets = google.sheets('v4');

// Twilio配置
const accountSid = secrets.account_sid;
const authToken = secrets.auth_token;
const client = require('twilio')(accountSid, authToken);
const sandboxNumber = secrets.sandbox_number;

// 日期校验函数：判断是否为履约日期前7天
function isSevenDaysBeforeFulfilment(fulfilmentDateStr) {
  const fulfilmentDate = new Date(fulfilmentDateStr);
  const today = new Date();
  
  today.setHours(0, 0, 0, 0);
  fulfilmentDate.setHours(0, 0, 0, 0);
  
  const timeDiff = fulfilmentDate.getTime() - today.getTime();
  const dayDiff = Math.floor(timeDiff / (1000 * 60 * 60 * 24));
  
  return dayDiff === 7;
}

// 发送消息函数
function sendMessage(rows) {
   if (!rows.length) {
        console.log("---------------------------------");
        return;
    }

    const firstRow = rows.shift();
    const fulfilment_date = firstRow[0];
    const commodity = firstRow[1];
    const quantity = firstRow[2];
    const recipient = firstRow[3];
    const phone_number = firstRow[4];

    client.messages
        .create({
            from: 'whatsapp:' + sandboxNumber,
            to: 'whatsapp:' + phone_number,
            body: `履约提醒：距离${fulfilment_date}的履约还有7天\n商品：${commodity}，数量：${quantity}\n请${recipient}做好准备`
        })
        .then(function (message) {
            console.log(`已发送提醒，消息SID：${message.sid}\n`);
            sendMessage(rows);
        })
        .catch(function (err) {
            console.log(`发送失败：${err}\n`);
            sendMessage(rows);
        });
}

// 启动定时任务：每天上午9点执行
const job = schedule.scheduleJob({ hour: 9, minute: 0, timezone: 'Asia/Shanghai' }, function() {
    console.log('开始执行履约提醒任务...');
    sheets.spreadsheets.values.get({
        spreadsheetId: secrets.spreadsheet_id,
        range: 'Sheet1!A2:E',
        auth: authClient
    })
    .then(function (response) {
        const rows = response.data.values || [];
        const eligibleRows = rows.filter(row => isSevenDaysBeforeFulfilment(row[0]));
        if (eligibleRows.length) {
            sendMessage(eligibleRows);
            console.log(`任务完成：共发送${eligibleRows.length}条提醒`);
        } else {
            console.log("任务完成：今日无需要发送的履约提醒");
        }
    })
    .catch(function (err) {
        console.log(`任务失败：${err}`);
    });
});

console.log('定时任务已启动，每天上午9点自动执行');

注意事项 #

• 时区：确保代码中的时区设置和你的业务时区一致，避免日期判断出错。
• Twilio沙箱：正式使用前需要将接收号码移出沙箱，绑定正式的WhatsApp发送号码。
• 日志：可以添加日志记录功能，方便排查发送失败的情况。

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