求助：如何让单元格颜色匹配带条件格式的动态单元格颜色

Hey there! Let me walk you through fixing this issue and getting your cell color sync working just right. Since you mentioned the 4th line of the script throws an error, it’s likely a problem with how the source cell or target range is being referenced. Here’s a tailored script for your needs, plus step-by-step instructions:

Step 1: The Modified Script #

function syncCellColor() {
  // Replace with your spreadsheet ID (found in the URL bar)
  const ss = SpreadsheetApp.openById("YOUR_SPREADSHEET_ID");
  // Replace with your sheet name (e.g., "Sheet1")
  const sheet = ss.getSheetByName("YOUR_SHEET_NAME");
  
  // --- Update these two lines to match your setup ---
  const sourceCell = sheet.getRange("A1"); // This is the cell with dynamic color based on text
  const targetRange = sheet.getRange("B1:C10"); // This is the range you want to match the source color
  
  // Get the background color from the source cell
  const sourceColor = sourceCell.getBackground();
  
  // Apply that color to every cell in the target range
  targetRange.setBackground(sourceColor);
}

Step 2: How to Adjust It for Your Spreadsheet #

• Replace "YOUR_SPREADSHEET_ID" with the unique ID of your Google Sheet (you can find this in the URL, between /d/ and /edit).
• Replace "YOUR_SHEET_NAME" with the exact name of the sheet where your cells are located (it’s case-sensitive, so double-check spelling!).
• Update sourceCell to the cell reference of your dynamic color cell (like "D5" instead of "A1").
• Update targetRange to the range you want to color (like "E5:E20" or "F2:H15").

Step 3: Troubleshooting the 4th Line Error #

If you still get an error on line 4, double-check these common issues:

• Did you spell the sheet name correctly? Even a single wrong letter will break it.
• Is the spreadsheet ID complete? Make sure you didn’t cut off any characters from the URL.
• Are the cell/range references valid? For example, don’t use "A10000" if your sheet only has 500 rows.

Step 4: Automate Sync (Optional) #

If you want the color to update automatically whenever the source cell changes, set up an onEdit trigger:

1. In the script editor, click the clock icon (Triggers) in the left sidebar.
2. Click "Add Trigger".
3. Set these options:
   • Choose which function to run: syncCellColor
   • Choose which deployment to run: Head
   • Select event source: From spreadsheet
   • Select event type: On edit
4. Click "Save".

That should get your target range matching the source cell’s dynamic color perfectly!

内容的提问来源于stack exchange，提问作者Michael Van Eaton