求助修复Airtable季度写作目标计算脚本的报错问题

核心问题分析 #

原脚本报错的主要原因是变量作用域混乱和函数逻辑未正确执行：

• 关键计算变量（如daysPerArticle、articlesPerWeek）在函数内部声明，外部无法访问
• totalWeekdaysLeft被定义为函数但未调用，且内部使用了未初始化的变量
• 部分全局变量未被合理利用，导致计算逻辑断裂

修复后的完整脚本 #

/*This function does the following: 
1. Calculates total articles in a view
2. Calculates progress towards quarterly goal
3. Returns articles left to achieve goal */

let table = base.getTable("Writing Assignments");
let view = table.getView("My Production Count");

// 统计已完成文章数并计算季度目标进度
let result = await view.selectRecordsAsync({ fields: ["Article count"] });
let runningTotal = 0;
let minGoal = 18;
let maxGoal = 24;

for (let record of result.records) {
  const count = record.getCellValue("Article count");
  // 处理字段为空的情况，避免NaN
  runningTotal += count ? count : 0;
}

const progress = Math.round((runningTotal * 100) / minGoal);
// 计算剩余需完成文章数，确保数值不为负
const remaining = Math.max(minGoal - runningTotal, 0);

// 计算季度剩余工作日、周数的函数，返回计算结果
const getTimeUntilEndOfQuarter = () => {
  const now = new Date();
  // 定义各季度结束日期
  const Q1 = new Date(`April 1 ${now.getFullYear()}`);
  const Q2 = new Date(`July 1 ${now.getFullYear()}`);
  const Q3 = new Date(`October 1 ${now.getFullYear()}`);
  const Q4 = new Date(`January 1 ${now.getFullYear() + 1}`);

  let currentQuarter;
  let quarterLabel;

  // 判断当前所属季度
  if (now > Q3) {
    currentQuarter = Q4;
    quarterLabel = "Q4";
  } else if (now > Q2) {
    currentQuarter = Q3;
    quarterLabel = "Q3";
  } else if (now > Q1) {
    currentQuarter = Q2;
    quarterLabel = "Q2";
  } else {
    currentQuarter = Q1;
    quarterLabel = "Q1";
  }

  // 计算剩余总天数（向上取整）
  const timeLeft = currentQuarter.getTime() - now.getTime();
  const totalDaysLeft = Math.ceil(timeLeft / (1000 * 3600 * 24));

  // 计算剩余周数和零散天数
  const weeksLeft = Math.floor(totalDaysLeft / 7);
  const daysLeft = totalDaysLeft % 7;

  // 计算剩余工作日数的核心逻辑
  let totalWeekdaysLeft = 0;
  for (let i = 0; i < totalDaysLeft; i++) {
    const tempDate = new Date(now);
    tempDate.setDate(now.getDate() + i);
    const dayOfWeek = tempDate.getDay();
    // 排除周日(0)和周六(6)
    if (dayOfWeek !== 0 && dayOfWeek !== 6) {
      totalWeekdaysLeft++;
    }
  }

  // 计算每周需完成文章数和每篇文章所需工作日数
  // 避免除以0的情况（剩余文章数为0时）
  const articlesPerWeek = remaining > 0 ? (remaining / weeksLeft).toFixed(2) : 0;
  const daysPerArticle = remaining > 0 ? (totalWeekdaysLeft / remaining).toFixed(2) : 0;

  // 处理复数形式
  const weeksPlural = weeksLeft === 1 ? "week" : "weeks";
  const daysPlural = totalWeekdaysLeft === 1 ? "day" : "days";

  // 返回所有需要的计算结果
  return {
    quarterLabel,
    weeksLeft,
    totalWeekdaysLeft,
    articlesPerWeek,
    daysPerArticle,
    weeksPlural,
    daysPlural
  };
};

// 执行函数并获取结果
const quarterData = getTimeUntilEndOfQuarter();

// 输出结果到Airtable扩展界面
output.markdown(`
# 写作进度状态

## 季度目标进度：  

![进度条](https://geps.dev/progress/${progress})

${progress} % 完成 ${minGoal} 篇文章的目标

## 剩余任务拆解：
- 每篇文章需投入：${quarterData.daysPerArticle} ${quarterData.daysPlural}
- 每周需完成：${quarterData.articlesPerWeek} 篇/周

当前季度：${quarterData.quarterLabel}
剩余工作日：${quarterData.totalWeekdaysLeft} ${quarterData.daysPlural}
剩余周数：${quarterData.weeksLeft} ${quarterData.weeksPlural}
`);

// 每15分钟刷新一次数据
setInterval(() => {
  // 重新执行所有逻辑并更新输出
  (async () => {
    let result = await view.selectRecordsAsync({ fields: ["Article count"] });
    let runningTotal = 0;
    for (let record of result.records) {
      const count = record.getCellValue("Article count");
      runningTotal += count ? count : 0;
    }
    const progress = Math.round((runningTotal * 100) / minGoal);
    const remaining = Math.max(minGoal - runningTotal, 0);
    const quarterData = getTimeUntilEndOfQuarter();

    output.markdown(`
# 写作进度状态

## 季度目标进度：  

![进度条](https://geps.dev/progress/${progress})

${progress} % 完成 ${minGoal} 篇文章的目标

## 剩余任务拆解：
- 每篇文章需投入：${quarterData.daysPerArticle} ${quarterData.daysPlural}
- 每周需完成：${quarterData.articlesPerWeek} 篇/周

当前季度：${quarterData.quarterLabel}
剩余工作日：${quarterData.totalWeekdaysLeft} ${quarterData.daysPlural}
剩余周数：${quarterData.weeksLeft} ${quarterData.weeksPlural}
`);
  })();
}, 15 * 60 * 1000);

关键修复点说明 #

• 变量作用域优化：将getTimeUntilEndOfQuarter改为返回包含所有计算结果的对象，解决外部无法访问内部变量的问题
• 工作日计算逻辑修复：替换原错误的函数赋值逻辑，改用循环遍历每一天判断是否为工作日，确保计算准确
• 边界情况处理：处理字段为空、剩余文章数为0的情况，避免NaN和除以0的报错
• 刷新逻辑完善：原定时刷新只调用函数未重新计算文章进度，修复后重新执行完整逻辑并更新输出
• 代码可读性提升：统一变量命名风格，添加中文注释，拆分复杂逻辑块

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