求助：通过Apps Script基于百分比设置Google Sheets单元格背景色

Hey there! I get that transitioning from conditional formatting to scripts can feel tricky when you're just starting out—let’s walk through this step by step with code you can adapt easily.

Step 1: Auto-Update Column W When Columns F-T Are Edited #

First, we’ll set up a script that recalculates Column W whenever someone edits cells in F-T. I’ll include a sample calculation, but you can swap it out for your actual percentage logic.

function onEdit(e) {
  const sheet = e.source.getActiveSheet();
  const editedRange = e.range;
  
  // Check if the edited cell is in columns F-T (columns 6 to 20)
  if (editedRange.getColumn() >= 6 && editedRange.getColumn() <= 20) {
    const row = editedRange.getRow();
    // Skip header row if you have one (change 1 to your header row number)
    if (row === 1) return;
    
    // Get values from columns F-T for this row
    const ftValues = sheet.getRange(row, 6, 1, 15).getValues()[0]; // F=col6, T=col20: 15 columns total
    
    // Replace this with your actual percentage calculation for W
    // Example: W = sum of F-T divided by a target value, converted to percentage
    const total = ftValues.reduce((sum, val) => sum + (val || 0), 0);
    const target = 1000; // Replace with your target number
    const percentage = total > 0 ? (total / target) * 100 : 0;
    
    // Update Column W (col23) with the percentage
    sheet.getRange(row, 23).setValue(`${percentage.toFixed(1)}%`);
    
    // Immediately apply color formatting after updating W
    setRowColors(sheet, row);
  }
}

Step 2: Set Background Colors for Columns C & W Based on W's Percentage #

This function checks the percentage in Column W and applies a background color to both Column C and W in the same row. You can tweak the color codes and percentage thresholds to match your needs.

function setRowColors(sheet, row) {
  const wCell = sheet.getRange(row, 23);
  const percentageText = wCell.getValue();
  // Extract the numeric value from the percentage string (e.g., "75.2%" → 75.2)
  const percentage = parseFloat(percentageText.replace('%', ''));
  
  let bgColor;
  // Define your color rules here
  if (percentage < 50) {
    bgColor = '#ffcccc'; // Light red
  } else if (percentage >= 50 && percentage < 80) {
    bgColor = '#fff2cc'; // Light yellow
  } else {
    bgColor = '#d9ead3'; // Light green
  }
  
  // Apply color to Column C (col3) and Column W (col23)
  sheet.getRange(row, 3).setBackgroundColor(bgColor);
  wCell.setBackgroundColor(bgColor);
}

// Optional: Run this once to apply colors to all existing rows
function applyColorsToAllRows() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const lastRow = sheet.getLastRow();
  
  // Skip header row (change 1 to your header row number)
  for (let row = 2; row <= lastRow; row++) {
    setRowColors(sheet, row);
  }
}

How to Set This Up #

1. Open your Google Sheet, click Extensions > Apps Script to launch the script editor.
2. Delete any default code in the editor, then paste the two code blocks above.
3. Adjust these parts to match your sheet:
   • In onEdit, update the percentage calculation to match how you compute Column W’s value.
   • In setRowColors, tweak the percentage thresholds and color hex codes to your preference.
   • If your sheet has a header row, update the row === 1 check to match your header row number (e.g., row === 2 if header is row 2).
4. Click the save button (💾) and name your project (e.g., "Sheet Auto-Update & Color").
5. When you first run applyColorsToAllRows (click the play button ▶️), you’ll need to authorize the script—follow the prompts to allow access (it’s safe, you’re granting permission to your own sheet).

Testing It Out #

• Edit any cell in columns F-T: Column W should update automatically, and columns C & W in that row will change color based on the new percentage.
• Run applyColorsToAllRows once to apply the color rules to all existing rows in your sheet.

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