如何每次运行脚本时递增引用单元格？Google Sheets脚本需求

Your current script gets the job done, but we can make it dynamic, maintainable, and far more efficient by adding state tracking (to remember how many times it’s been run) and using column indices instead of hardcoded cell references. Here’s the polished implementation:

function dataTransfer() {
  // Track how many times the script has run (persists between sessions)
  const props = PropertiesService.getScriptProperties();
  let runCount = parseInt(props.getProperty('runCount')) || 0;

  // Define base positions (easy to adjust if your sheet structure changes later)
  const BASE_SOURCE_COL1 = 7; // Column G
  const BASE_SOURCE_ROW1 = 18;
  const BASE_SOURCE_COL2 = 32; // Column AF
  const BASE_SOURCE_ROW2 = 8;
  const BASE_TARGET_COL = 4; // Column D
  const BASE_TARGET_ROW1 = 8;
  const BASE_TARGET_ROW2 = 18;

  // Calculate dynamic columns based on run count
  const dynamicSourceCol1 = BASE_SOURCE_COL1 + (runCount * 2);
  const dynamicTargetCol = BASE_TARGET_COL + runCount;

  // Fetch spreadsheet and sheets once (cuts down redundant API calls)
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const backlogSheet = ss.getSheetByName('BACKLOG');
  const amCompSheet = ss.getSheetByName('AM COMP');

  // Get values from source cells
  const value1 = backlogSheet.getRange(BASE_SOURCE_ROW1, dynamicSourceCol1).getValue();
  const value2 = backlogSheet.getRange(BASE_SOURCE_ROW2, BASE_SOURCE_COL2).getValue();

  // Set values to target cells
  amCompSheet.getRange(BASE_TARGET_ROW1, dynamicTargetCol).setValue(value1);
  amCompSheet.getRange(BASE_TARGET_ROW2, dynamicTargetCol).setValue(value2);

  // Update run count for next execution
  props.setProperty('runCount', runCount + 1);
}

// Optional: Reset the run count if you need to start over (e.g., back to January)
function resetRunCount() {
  const props = PropertiesService.getScriptProperties();
  props.setProperty('runCount', 0);
  SpreadsheetApp.getUi().alert('Run count reset to 0 — ready to start fresh!');
}

Key Improvements & Breakdown: #

• Persistent State Tracking: Uses PropertiesService to store the run count in Google’s cloud, so it won’t get lost if you edit the sheet or close the tab. No messy hidden cells required!
• Dynamic Calculation: Instead of hardcoding cell addresses, we compute positions using base values plus an offset from the run count:
  • First source column jumps by 2 each run (BASE_SOURCE_COL1 + runCount *2)
  • Both target columns increment by 1 each run (BASE_TARGET_COL + runCount)
• Efficiency: Your original script called getActiveSpreadsheet() 4 times — we do it once, which reduces unnecessary API calls and speeds up execution.
• Maintainability: All base positions are grouped at the top, so you can tweak starting rows/columns in seconds if your sheet layout changes.

How It Matches Your Example: #

• 1st Run (runCount=0): BACKLOG G18 → AM COMP D8; BACKLOG AF8 → AM COMP D18
• 2nd Run (runCount=1): BACKLOG I18 → AM COMP E8; BACKLOG AF8 → AM COMP E18
• 3rd Run (runCount=2): BACKLOG K18 → AM COMP F8; BACKLOG AF8 → AM COMP F18

Perfectly aligns with the monthly pattern you described!

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