如何为Google表格图表中含特定值的列设置样式

Hey there! Let's get that conditional formatting sorted for your Google Visualization table. You want color-coded statuses (active green, hold yellow, etc.) and red highlights for remaining days under 10—here's how to make it work, plus integrate it with your existing filter code.

We'll use Google Visualization's built-in ColorFormat class to handle the color coding, then tie it into your existing filter setup.

Step 1: Create the Color Formatters #

First, define formatters for both your status column and remaining days column. You can customize the colors to match your needs:

// Color formatter for the Status column
const statusColorFormatter = new google.visualization.ColorFormat();
// Set "active" to green background with white text
statusColorFormatter.addRange('active', 'active', '#ffffff', '#2ecc71');
// Set "hold" to yellow background with black text
statusColorFormatter.addRange('hold', 'hold', '#000000', '#f1c40f');
// Add formatting for other status values (e.g., "none")
statusColorFormatter.addRange('none', 'none', '#ffffff', '#95a5a6');
// Add more statuses here if you have additional values

// Color formatter for Remaining Days column (values <10 get red background)
const daysColorFormatter = new google.visualization.ColorFormat();
// Format all values from 0 up to 9 (less than 10)
daysColorFormatter.addRange(null, 9, '#ffffff', '#e74c3c');

Step 2: Apply Formatters to Your Data Table #

Next, apply these formatters to the correct columns in your data table. Using column names to get indices makes this more maintainable:

// Replace with your actual data table instance
const data = yourExistingDataTable;

// Get column indices by name (adjust names to match your table)
const statusColIndex = data.getColumnIndex('Status');
const remainingDaysColIndex = data.getColumnIndex('Remaining Days');

// Apply the formatters to the columns
statusColorFormatter.format(data, statusColIndex);
daysColorFormatter.format(data, remainingDaysColIndex);

Step 3: Integrate With Your Existing Filter Code #

Now plug this into your existing filter and table setup. Make sure to enable allowHtml: true in your table options—this is critical for the color formatting to render correctly.

Here's the full integrated code snippet (with your existing filter code included):

google.charts.load('current', {'packages':['table', 'controls']});
google.charts.setOnLoadCallback(drawYourTable);

function drawYourTable() {
  // --- Your existing data table setup goes here ---
  // const data = new google.visualization.DataTable();
  // data.addColumns([...]);
  // data.addRows([...]);

  // --- Add the formatting code here ---
  const statusColorFormatter = new google.visualization.ColorFormat();
  statusColorFormatter.addRange('active', 'active', '#ffffff', '#2ecc71');
  statusColorFormatter.addRange('hold', 'hold', '#000000', '#f1c40f');
  statusColorFormatter.addRange('none', 'none', '#ffffff', '#95a5a6');

  const daysColorFormatter = new google.visualization.ColorFormat();
  daysColorFormatter.addRange(null, 9, '#ffffff', '#e74c3c');

  const statusColIndex = data.getColumnIndex('Status');
  const remainingDaysColIndex = data.getColumnIndex('Remaining Days');
  statusColorFormatter.format(data, statusColIndex);
  daysColorFormatter.format(data, remainingDaysColIndex);

  // --- Your existing filter code ---
  const parent_w_div = document.getElementById('your-table-container-parent'); // Replace with your actual parent container ID
  const w_div = document.getElementById('your-table-container'); // Replace with your table container ID

  var f_div = document.createElement("div");
  f_div.id = "filter_div119_PlanName";
  parent_w_div.insertBefore(f_div,w_div);

  var PlanNamefilter = new google.visualization.ControlWrapper({
    "controlType": "CategoryFilter",
    "containerId": "filter_div119_PlanName",
    "options": {
      "filterColumnLabel": "PlanName", // Make sure this matches your column name
      "ui": {
        "labelStacking": "vertical",
        "allowTyping": false,
        "allowMultiple": false
        // Add any other filter options you need here
      }
    }
  });

  // --- Draw the table with HTML enabled ---
  const table = new google.visualization.Table(w_div);
  const tableOptions = {
    allowHtml: true, // Required for color formatting to display
    // Add any other table options (e.g., width, height, sort) here
  };

  // Connect filter and table with a dashboard
  const dashboard = new google.visualization.Dashboard(parent_w_div);
  dashboard.bind(PlanNamefilter, table);
  dashboard.draw(data, tableOptions);
}

Quick Tips #

• Customize Colors: Swap out the hex color codes for any shades you prefer (e.g., use #27ae60 for a darker green, #f39c12 for an orange-yellow).
• Column Names: Double-check that the column names you use in getColumnIndex match exactly what's in your data table.
• Additional Statuses: If you have more status values (like "completed" or "cancelled"), just add another addRange call to the status formatter.

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