Google Sheets Apps Script日期变量不更新问题求助

Great question! I’ve run into this exact problem with Google Sheets custom functions before—let’s break down why it’s happening and walk through a few solid fixes.

Why This Happens #

Google Sheets only recalculates custom functions when their input parameters change. Your current spentByThisDay function grabs the current date using new Date() internally, but since that value isn’t passed in as a parameter, Sheets has no way to detect that the "trigger" (today’s date) has changed. For past month sheets, where your date and spent columns don’t update daily, the function stays stuck until you manually re-enter it.

Solution 1: Make the Function Auto-Update with a TODAY() Parameter #

The simplest fix is to modify your function to accept today’s date as an argument, which you’ll feed it using Sheets’ built-in TODAY() function. Since TODAY() updates automatically every midnight, Sheets will trigger a recalculation of your custom function daily.

Updated Function Code

function spentByThisDay(date, spent, currentDay) { 
  // Extract the day of the month from the passed currentDay (from TODAY())
  var currentDate = new Date(currentDay).getDate(); 
  var currDate; 
  var sum = 0; 
  
  for(var i=0; i<date.length; i++){ 
    currDate = new Date(date[i]); 
    if(currDate.getDate() <= currentDate) {
      sum += parseFloat(spent[i]); 
    }
  } 
  return sum; 
}

How to Use It

In your sheet’s cell, call the function like this:

=spentByThisDay(A2:A100, B2:B100, TODAY())

Now every day at midnight, TODAY() will update, and Sheets will automatically recalculate the function for all your sheets—including past months.

Solution 2: Force Recalculation with a Time-Driven Trigger #

If you don’t want to modify your original function, you can set up an automated script to refresh the cells containing spentByThisDay once per day.

Step-by-Step Setup

1. Open your Google Sheet, go to Extensions > Apps Script.
2. Paste this script alongside your existing spentByThisDay function:

function refreshSpentCalculations() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var allSheets = spreadsheet.getSheets();
  
  // Loop through every sheet in your spreadsheet
  allSheets.forEach(function(sheet) {
    var dataRange = sheet.getDataRange();
    var formulas = dataRange.getFormulas();
    
    // Check each cell for your custom function and re-set the formula to trigger recalculation
    formulas.forEach(function(row, rowIndex) {
      row.forEach(function(formula, colIndex) {
        if (formula.includes("spentByThisDay")) {
          var cell = sheet.getRange(rowIndex + 1, colIndex + 1);
          cell.setFormula(formula);
        }
      });
    });
  });
}

3. Set up the daily trigger:
   • In the Apps Script editor, click the clock icon (Triggers) in the left sidebar.
   • Click Add Trigger.
   • Choose refreshSpentCalculations as the function to run.
   • Set the event source to Time-driven.
   • Select Day timer and pick a window (like midnight to 1 AM) when the sheet isn’t being used.

Solution 3: Ditch Custom Scripts Entirely (Use Built-in Functions) #

If you’d rather avoid custom scripts altogether, you can get the same result using a combination of SUMIF and DAY()—which will update automatically without any extra work:

=SUMIF(ARRAYFORMULA(DAY(A2:A100)), "<="&DAY(TODAY()), B2:B100)

This formula checks if the day of the month in your date column (A) is less than or equal to today’s day, then sums the corresponding amounts in column B. It’ll refresh on its own every day.

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