Debug errors in Airtable automation script for quarterly goal tracking and time calculations

Other Questions Airtable
1

I need help fixing bugs in my Airtable script. It’s supposed to track my writing progress and calculate deadlines but keeps throwing errors.

The script should:

  1. Count total articles from a database view
  2. Calculate how much work is left to reach my quarterly target
  3. Figure out weekly article requirements to meet the deadline
  4. Calculate weekday intervals needed per remaining article

Here’s my code:

// Script for tracking quarterly blog post targets
// 1. Gets total posts from view
// 2. Shows progress toward quarterly target
// 3. Calculates remaining work needed

let blogTable = base.getTable("Blog Posts");
let targetView = blogTable.getView("Published This Quarter");

// sum up posts and calculate goal percentage:
let records = await targetView.selectRecordsAsync({ fields: ["Post count"] });
let totalPosts = 0;
let targetMin = 15;
let targetMax = 20;
for (let entry of records.records) {
  totalPosts += entry.getCellValue("Post count");
}
const completionRate = Math.round((totalPosts * 100) / targetMin);

// find posts still needed:
var postsNeeded = targetMin - totalPosts;

// Function to find remaining time in current quarter

var weeksRemaining;
var daysRemaining;
var currentQ;

const calculateQuarterTimeLeft = () => {
  const today = new Date();
  
  const Quarter1End = new Date("March 31 " + today.getFullYear());
  const Quarter2End = new Date("June 30 " + today.getFullYear());
  const Quarter3End = new Date("September 30 " + today.getFullYear());
  const Quarter4End = new Date("December 31 " + today.getFullYear());

  let quarterEnd;
  if (today > Quarter3End) {
    quarterEnd = Quarter4End;
    currentQ = "Q4";
  } else if (today > Quarter2End) {
    quarterEnd = Quarter3End;
    currentQ = "Q3";
  } else if (today > Quarter1End) {
    quarterEnd = Quarter2End;
    currentQ = "Q2";
  } else {
    quarterEnd = Quarter1End;
    currentQ = "Q1";
  }

  const timeRemaining = quarterEnd.getTime() - today.getTime();
  var totalDaysRemaining = Math.ceil(timeRemaining / (1000 * 3600 * 24));

  var weeksRemaining = Math.floor(totalDaysRemaining / 7);
  var extraDays = totalDaysRemaining % 7;

  // Calculate weekdays only (no weekends)
  var calculateWeekdays = function removeWeekends(totalDays) {
    daysRemaining -= weeksRemaining * 2;
    
    var todayWeekday = today.getDay();
    var endWeekday = quarterEnd.getDay();
    
    if (todayWeekday - endWeekday > 1) {
      daysRemaining -= 2;
    }
    if (todayWeekday == 0 && endWeekday != 6) {
      daysRemaining--;
    }
    if (endWeekday == 6 && todayWeekday != 0) {
      daysRemaining--;
    }
  }

  // Calculate posting schedule needed:
  var daysPerPost = totalWeekdaysRemaining / postsNeeded;
  var postsPerWeek = weeksRemaining / postsNeeded;

  const weekText = weeksRemaining == 1 ? "week" : "weeks";
  const dayText = totalWeekdaysRemaining == 1 ? "day" : "days";
};

calculateQuarterTimeLeft();

output.markdown(`
# Writing Progress

## Quarterly Goal Status:

![](https://geps.dev/progress/${completionRate})

${completionRate}% toward ${targetMin} posts

## Schedule Needed:
${daysPerPost} ${dayText} per post

## Weekly Target:
${postsPerWeek} posts per ${weekText}

`);

The main problem is variable declaration issues that break my calculations. I’m still learning JavaScript so this combines Airtable examples, help from a friend, and Stack Overflow snippets. I’m sure there are other problems I haven’t spotted yet. Any help would be great!

2

I see several issues beyond just the variable scoping. Your biggest problem is that calculateQuarterTimeLeft declares variables inside it, but you’re trying to use those variables in the output below.

Move all your calculation variables outside the function or restructure it to return an object with the values you need.

Also spotted that totalWeekdaysRemaining doesn’t exist anywhere but you’re using it in calculations. You probably meant daysRemaining after removing weekends.

Your weekend calculation logic is way too complex. I just loop through each day from today to the end date and count weekdays. Much simpler and less error prone.

One more thing - your date parsing might break depending on locale settings. Use new Date(today.getFullYear(), 2, 31) format instead of string parsing for more reliable results.

This video walks through debugging JavaScript step by step and will help you catch these variable issues faster:

I’d add some validation too. What happens if postsNeeded is negative because you already hit your target? Your math will break.

3

Quick fix - you’re using daysRemaining in the calculateWeekdays function but never defined it. Plus you’re not calling that function, so the weekend logic never runs. Just add daysRemaining = totalDaysRemaining at the top and call calculateWeekdays() before your schedule calculations. The math at the end looks fine once the variables actually exist.

4

Your problem is variable scoping - you’re referencing daysRemaining and totalWeekdaysRemaining without ever initializing them.

I hit this same issue when I started with Airtable scripts. You’ve got calculateWeekdays declared but never called, and it’s trying to modify daysRemaining that doesn’t exist in that scope. Make your calculateQuarterTimeLeft function return values instead of depending on globals.

Your daysPerPost and postsPerWeek calculations are backwards too - you’re dividing weeks by posts when it should be posts by weeks. Use postsNeeded / weeksRemaining for posts per week.

The removeWeekends parameter isn’t being used, and the weekend logic looks broken. I’d calculate total weekdays first, then do the scheduling math. Throw in some console.log statements to see what your variables actually contain at each step.