如何用Google Sheet Script实现单元格隔X秒变色及彩虹联动效果？

1. Single Cell Auto-Color Change Every X Seconds #

Got it, let's start with the basic requirement: making a single cell switch colors on a set interval. Since Google Apps Script has a 6-minute max execution limit per run, we'll cover two approaches—one for longer intervals (1 minute+) using time-driven triggers, and another for shorter cycles using a loop with sleep.

Step-by-Step Implementation #

1. Open your Google Sheet, click Extensions > Apps Script to launch the script editor.
2. Replace the default code with this:

function singleCellColorCycle() {
  // Configure your target cell and color palette here
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const targetCell = sheet.getRange("A1");
  const colorPalette = ["#FF0000", "#FFA500", "#FFFF00", "#00FF00", "#0000FF", "#800080"]; // Rainbow hues
  
  // Find the next color in the palette
  const currentColor = targetCell.getBackground();
  let nextColorIndex = colorPalette.indexOf(currentColor) + 1;
  // Loop back to the start if we've reached the end of the palette
  if (nextColorIndex >= colorPalette.length) nextColorIndex = 0;
  
  // Apply the new color to the cell
  targetCell.setBackground(colorPalette[nextColorIndex]);
}

// Run this once to set up a repeating trigger (min 1-minute interval)
function setupSingleCellTrigger() {
  const intervalSeconds = 60; // Adjust to your desired interval (1 minute minimum for triggers)
  
  // Delete existing triggers first to avoid duplicates
  const existingTriggers = ScriptApp.getProjectTriggers();
  existingTriggers.forEach(trigger => {
    if (trigger.getHandlerFunction() === "singleCellColorCycle") {
      ScriptApp.deleteTrigger(trigger);
    }
  });
  
  // Create the time-driven trigger
  ScriptApp.newTrigger("singleCellColorCycle")
    .timeBased()
    .everyMinutes(Math.ceil(intervalSeconds / 60))
    .create();
}

For Shorter Intervals (Under 1 Minute) #

If you need faster cycles (like 5 seconds), use this loop-based function instead (note: it will stop after 6 minutes due to execution limits):

function runShortIntervalCycle() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const targetCell = sheet.getRange("A1");
  const colorPalette = ["#FF0000", "#FFA500", "#FFFF00", "#00FF00", "#0000FF", "#800080"];
  const intervalMs = 5000; // 5 seconds in milliseconds
  
  // Run for ~6 minutes (max allowed execution time)
  for (let i = 0; i < 72; i++) {
    const currentColor = targetCell.getBackground();
    let nextColorIndex = colorPalette.indexOf(currentColor) + 1;
    if (nextColorIndex >= colorPalette.length) nextColorIndex = 0;
    targetCell.setBackground(colorPalette[nextColorIndex]);
    Utilities.sleep(intervalMs);
  }
}

• To start the cycle, run either setupSingleCellTrigger() (for 1+ minute intervals) or runShortIntervalCycle() (for faster cycles) from the script editor.

2. Linked Rainbow Cycle Effect (Cell-to-Cell Propagation) #

Now for the advanced联动效果—we'll make colors flow from one cell to the next, creating a looping rainbow wave. We'll use PropertiesService to save the current state so the script remembers where it left off between runs.

Step-by-Step Implementation #

1. In the same Apps Script editor, add or replace with this code:

function linkedRainbowCycle() {
  // Define your target range (e.g., A1 to F1—adjust to your needs)
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const targetRange = sheet.getRange("A1:F1");
  const colorPalette = ["#FFFFFF", "#FF0000", "#FFA500", "#FFFF00", "#00FF00", "#0000FF", "#800080"]; // Start with white, then rainbow
  const props = PropertiesService.getScriptProperties();
  
  // Get current state or initialize if it's the first run
  let currentIndex = parseInt(props.getProperty("currentRainbowIndex")) || 1; // Start at first rainbow color
  
  // Shift colors right: each cell takes the color of the cell to its left
  const cells = targetRange.getCells();
  for (let i = cells.length - 1; i > 0; i--) {
    cells[i].setBackground(cells[i-1].getBackground());
  }
  
  // Set the first cell to the next color in the palette
  cells[0].setBackground(colorPalette[currentIndex]);
  
  // Update state for the next run
  currentIndex++;
  if (currentIndex >= colorPalette.length) currentIndex = 1; // Loop back to the first rainbow color
  props.setProperty("currentRainbowIndex", currentIndex.toString());
}

// Run this to set up the repeating trigger for the linked effect
function setupLinkedRainbowTrigger() {
  const intervalSeconds = 2; // Adjust your interval here
  
  // Delete existing triggers to avoid duplicates
  const existingTriggers = ScriptApp.getProjectTriggers();
  existingTriggers.forEach(trigger => {
    if (trigger.getHandlerFunction() === "linkedRainbowCycle") {
      ScriptApp.deleteTrigger(trigger);
    }
  });
  
  // Use time-driven trigger for intervals ≥1 minute, or short loop for faster cycles
  if (intervalSeconds >= 60) {
    ScriptApp.newTrigger("linkedRainbowCycle")
      .timeBased()
      .everyMinutes(Math.ceil(intervalSeconds / 60))
      .create();
  } else {
    runLinkedRainbowShortInterval(intervalSeconds);
  }
}

function runLinkedRainbowShortInterval(intervalSeconds) {
  const intervalMs = intervalSeconds * 1000;
  const maxRuns = Math.floor(360000 / intervalMs); // 6 minutes = 360,000 ms
  
  for (let i = 0; i < maxRuns; i++) {
    linkedRainbowCycle();
    Utilities.sleep(intervalMs);
  }
  
  // Reset state after the loop ends
  PropertiesService.getScriptProperties().deleteProperty("currentRainbowIndex");
}

How It Works: #

• linkedRainbowCycle() shifts each cell's color to the right (e.g., B1 takes A1's color, C1 takes B1's), then sets the first cell to the next color in the palette.
• PropertiesService stores the current color index so the cycle continues smoothly even when the script restarts.
• Run setupLinkedRainbowTrigger() to start the effect—it will handle both long and short intervals automatically.

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