如何在Google Apps Script中每日调用API且不覆盖历史数据？

The core issue is that you're inserting a live formula (=fetchAdData()) into the sheet. Every time Google Sheets recalculates (which happens automatically on edits, time triggers, or manual refreshes), this formula runs again and returns the latest data—overwriting the historical values you intended to preserve.

To fix this, call fetchAdData() directly in your script to get static values, then write those values to the sheet instead of using a formula. Here's the corrected DailyAdSpendFetch function:

function DailyAdSpendFetch() {
  const ss = SpreadsheetApp.getActive();
  const sheet = ss.getSheetByName('ADSPEND');
  const adData = fetchAdData(); // Get static data from your API
  
  // Handle empty data case
  if (adData.length === 0) {
    sheet.appendRow(['No ad spend data available for yesterday']);
    return;
  }
  
  // Calculate the next empty row to write to
  const nextRow = sheet.getLastRow() + 1;
  
  // Write all fetched rows to the sheet in one operation (efficient)
  sheet.getRange(nextRow, 1, adData.length, adData[0].length).setValues(adData);
}

Why Your Original Code Failed #

• When you use appendRow(['=fetchAdData()']) or set a cell to the formula, Sheets treats this as a dynamic calculation. Every recalculation triggers fetchAdData() again, replacing the cell's value with the latest API response.
• Even if you append new rows with the formula, all existing formula cells will update to show the current day's data, making it look like previous entries were overwritten.

Notes #

• Your fetchAdData() function works correctly as-is—it returns the formatted, static data we need. We're just calling it directly in the script instead of via a sheet formula.
• Using setValues() instead of appending rows one by one is more efficient, especially if you have multiple ad campaigns to record.
• If your serverless API ever returns historical data (not just the previous day), you may want to add a check to avoid duplicate entries (e.g., compare the date of the fetched data against existing rows in the sheet).

内容的提问来源于stack exchange，提问作者Bethany Clifford-Rigg