Cell Value Heatmap

The Cell Value Heatmap tool formats cell backgrounds as a heatmap based on their values, helping you visualize data patterns and identify trends at a glance.

---

How to Use Cell Value Heatmap

1. Select your range Select the cells you want to visualize. This tool uses your current selection (explicit helper text is shown in the panel).

2. Open the Cell Value Heatmap feature Navigate through the Google Workspace™ menu: Extensions > Text To Table Converter > Mega Tools > Cell Value Heatmap.

3. Choose color scheme Click the caret button to expand the color scheme options:

   • Stoplight - Red → Yellow → Green gradient
   • Blue → White → Red - Blue to white to red gradient
   • Custom - Enable min/max color pickers for custom gradients

4. Configure custom colors (if selected) If you chose “Custom”:

   • Min color picker - Color for minimum values
   • Max color picker - Color for maximum values
   • Colors are interpolated between min and max

5. Apply heatmap Click the “Apply” button to format cells with the heatmap colors. A status message appears in the panel confirming the operation.

---

How It Works

Numeric Cells

• Values are mapped across the gradient based on their position between min and max
• Minimum value gets the “min” color
• Maximum value gets the “max” color
• Values in between are interpolated

Text Cells (Planned Enhancement)

• Text cells will be sorted A→Z by first character (case-insensitive)
• Mapped across the gradient based on their alphabetical position
• Errors and empty cells remain unformatted

Current Behavior

• Currently ignores non-numeric cells
• Only numeric cells receive heatmap formatting
• Errors and empty cells stay unformatted

Astuce

The heatmap applies to background color only by default (no text color changes). This keeps your data readable while providing visual insights.

Large Ranges

For very large ranges, a warning will appear before applying the heatmap. You can choose to proceed or cancel the operation.

---

Color Schemes

Stoplight

• Red - Lowest values
• Yellow - Middle values
• Green - Highest values

Perfect for performance metrics, scores, or any data where you want to see “good” vs “bad” at a glance.

Blue → White → Red

• Blue - Lowest values
• White - Middle values
• Red - Highest values

Great for temperature data, deviations from average, or any data with a neutral midpoint.

Custom

• Choose your own min and max colors
• Colors are smoothly interpolated between your choices
• Perfect for brand colors or specific visual requirements

---

Use Cases

• Data analysis - Quickly identify high and low values in datasets
• Performance tracking - Visualize metrics across teams or time periods
• Financial analysis - Spot trends in revenue, expenses, or profits
• Quality control - Identify outliers in manufacturing or test data
• Sales data - See which products, regions, or periods perform best
• Survey results - Visualize response patterns and distributions

---

Example

If you have a range with values: 10, 25, 50, 75, 100

With a Stoplight scheme:

• 10 (min) → Red
• 25 → Red-Yellow blend
• 50 (middle) → Yellow
• 75 → Yellow-Green blend
• 100 (max) → Green

---

Note

This tool is part of the Mega Tools suite, available exclusively in Google Sheets™. The formatting uses SheetsApiUtils.updateCellFormat() to ensure proper application and undo support.