如何实现Google Sheets每日自动向下复制公式？

Hey Simon, let's fix that manual formula copying hassle once and for all. Since you already have a daily script appending data to columns A-C, we can extend it to automatically add your change-tracking formulas to D-G right after the new data is added. Here's how to do it:

Step 1: Integrate Formula Setup into Your Existing Script #

Add this code block after the part of your script that appends the new data to your sheet. It will target the newly added rows and insert the exact formulas you need.

Single Row Append (If your script adds one row per day) #

function yourExistingDailyScript() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Your Sheet Name"); // Replace with your sheet name
  
  // --- Your existing data import code goes here ---
  // Example:
  // const sourceSheet = SpreadsheetApp.openById("SOURCE_SPREADSHEET_ID").getSheetByName("Source Sheet");
  // const newRow = sourceSheet.getRange("A:C").getValues(); // Grab the latest date, amount, percentage
  // sheet.getRange(sheet.getLastRow() + 1, 1, 1, 3).setValues(newRow);
  // --- End of existing code ---
  
  const lastRow = sheet.getLastRow();
  
  // Only set formulas if there's a previous row to compare against
  if (lastRow > 1) {
    // Column D: Absolute change in Amount (Current B - Previous B)
    sheet.getRange(`D${lastRow}`).setFormula(`=B${lastRow}-B${lastRow-1}`);
    
    // Column E: Percentage change in Amount ((Current B - Previous B)/Previous B)
    sheet.getRange(`E${lastRow}`).setFormula(`=IFERROR((B${lastRow}-B${lastRow-1})/B${lastRow-1}, 0)`);
    
    // Column F: Absolute change in Percentage (Current C - Previous C)
    sheet.getRange(`F${lastRow}`).setFormula(`=C${lastRow}-C${lastRow-1}`);
    
    // Column G: Percentage change in Percentage ((Current C - Previous C)/Previous C)
    sheet.getRange(`G${lastRow}`).setFormula(`=IFERROR((C${lastRow}-C${lastRow-1})/C${lastRow-1}, 0)`);
    
    // Optional: Format E and G as percentages
    sheet.getRange(`E${lastRow}:G${lastRow}`).setNumberFormat("0.00%");
  }
}

Multiple Rows Append (If your script adds several rows at once) #

If your import pulls in multiple rows per run, use this loop to apply formulas to every new row (skip the first row since there's no prior data to compare):

function yourExistingDailyScript() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Your Sheet Name");
  const sourceSheet = SpreadsheetApp.openById("SOURCE_SPREADSHEET_ID").getSheetByName("Source Sheet");
  
  // --- Your existing multi-row import code ---
  const newData = sourceSheet.getRange("A2:C").getValues(); // Grab all new data (adjust range as needed)
  const startRow = sheet.getLastRow() + 1;
  sheet.getRange(startRow, 1, newData.length, 3).setValues(newData);
  const endRow = sheet.getLastRow();
  // --- End of existing code ---
  
  // Loop through each new row and set formulas
  for (let row = startRow; row <= endRow; row++) {
    if (row > 1) { // Skip first row of the entire sheet
      sheet.getRange(`D${row}`).setFormula(`=B${row}-B${row-1}`);
      sheet.getRange(`E${row}`).setFormula(`=IFERROR((B${row}-B${row-1})/B${row-1}, 0)`);
      sheet.getRange(`F${row}`).setFormula(`=C${row}-C${row-1}`);
      sheet.getRange(`G${row}`).setFormula(`=IFERROR((C${row}-C${row-1})/C${row-1}, 0)`);
    }
  }
  
  // Batch-format percentage columns for efficiency
  sheet.getRange(`E${startRow}:G${endRow}`).setNumberFormat("0.00%");
}

Key Notes to Adjust for Your Setup #

• Replace "Your Sheet Name" with the exact name of your target sheet.
• Tweak the formulas if your change calculations need to work differently (e.g., use absolute references if needed, or adjust the IFERROR logic to your preference).
• Make sure your script has permission to access both your target sheet and the source sheet you're pulling data from (you'll be prompted for this when you first run the updated script).

This way, every time your daily script runs, the formulas will be added automatically—no more manual copying!

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