Google Sheets：基于单元格值终止循环的脚本问题求助

Hey there! I see you're stuck getting that recursive loop to work for waiting on your calculated source data before copying it over. Let's break down what's going wrong and fix it step by step.

First, the Issue in Your Current Code #

The biggest problem here is that your recursive call isn't actually running the function—you wrote looper; instead of looper();. Without those parentheses, you're just referencing the function, not triggering another iteration. That's why it stops after one run even when you manually set the trigger value.

Another critical thing to watch: Google Apps Script has a maximum execution time limit (around 6 minutes). A pure infinite recursive loop (or even a loop that runs too long) will hit that timeout error, just like your do/while attempt did. So we need to add a safety limit to prevent that.

Modified Working Code #

Here's an updated version of your script that fixes the recursive call, adds a retry limit, and cleans up the logic:

function looper(maxAttempts = 24) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName("example");
  const triggerValue = sheet.getRange("A1").getValue();
  const sourceRange = sheet.getRange("B1:J1");
  const targetRange = sheet.getRange("B2:J2");

  // Stop if we've hit max attempts to avoid timeout
  if (maxAttempts <= 0) {
    console.log("Max attempts reached—stopping loop without copying data.");
    return;
  }

  // Copy data if trigger is ready
  if (triggerValue === false) {
    const sourceData = sourceRange.getValues();
    targetRange.setValues(sourceData);
    console.log("Data copied successfully!");
    return;
  }

  // Wait and retry if not ready
  console.log(`Data not ready yet. Waiting... Attempts left: ${maxAttempts - 1}`);
  Utilities.sleep(5000);
  looper(maxAttempts - 1);
}

Key Improvements Explained #

• Fixed Recursive Call: Changed looper; to looper(maxAttempts - 1) so the function actually runs again.
• Max Attempts Safety: The default maxAttempts = 24 gives you 2 minutes of waiting (5 seconds per attempt)—adjust this number based on how long your data calculations typically take to finish.
• Clear Logging: Added console logs to track the script's progress (check the Executions tab in Google Apps Script to view these logs).
• Readable Variables: Renamed variables to make the code easier to follow at a glance.

Alternative: Time-Driven Trigger Approach #

If you're still worried about hitting execution time limits with recursion, a more robust long-term solution is using a temporary time-driven trigger:

1. Start with an initial function that creates a trigger to run every 5 minutes.
2. Each time the trigger runs, check the A1 value.
3. If A1 is false, copy the data and delete the temporary trigger.
4. If not, the trigger will automatically run again at the next interval.

This avoids tying up a single script execution and works better for longer wait times. Let me know if you need help setting this up!

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