技术问题：通过Apps Script无法更新Google Sheets饼图的扇区偏移量（Offset）与颜色

I’ve run into this exact frustrating issue before—let me break down what’s happening and how to fix it. The core problem is a known limitation in the Google Apps Script Charts API for 3D pie charts: even though the API lets you set slices.offset and slices.color without throwing errors, these properties aren’t actually rendered in the UI for 3D pie charts. The manual Chart Editor works because the Sheets UI handles 3D chart styling through a different, more robust layer than the Apps Script API.

Why This Happens #

When you enable is3D: true, the chart switches to a 3D rendering engine that doesn’t support per-slice offset or custom color configurations via the Apps Script Charts API. Other properties like title, pieHole, or position work because they’re managed by a shared configuration system that’s compatible with both 2D and 3D charts.

Reliable Workarounds #

Here are two proven fixes depending on whether you need to keep the 3D effect:

1. Switch to a 2D Pie Chart (Quickest Fix) #

If 3D styling isn’t a hard requirement, simply remove the setOption('is3D', true) line. This will render a 2D pie chart where your slice offset and color settings work exactly as expected.

Modified code snippet:

// 5. Build Chart
const chartBuilder = sheet.newChart()
  .setChartType(Charts.ChartType.PIE)
  .addRange(sheet.getRange("A1:B3"))
  .setPosition(5, 1, 0, 0)
  .setOption('title', 'Offset Bug Test')
  // Removed the is3D: true line
  .setOption('pieHole', 0.4)
  .setOption('slices', sliceOptions)
  .setOption('useFirstRowAsHeaders', false)
  .build();

2. Use the Google Sheets API (For 3D Charts) #

If you must retain the 3D effect, the Sheets Advanced Service offers full support for 3D pie chart slice styling. First, enable the service in your Apps Script project:

1. Go to Resources > Advanced Google Services in the editor.
2. Toggle Google Sheets API to "On" and click "OK".

Then use this function to create your chart with proper slice styling:

function create3DPieWithOffsetUsingSheetsAPI() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName("Test") || ss.insertSheet("Test");
  
  // Prepare test data (same as original)
  sheet.clear();
  const testData = [["Group A", 10], ["Group B", 20], ["Group C", 30]];
  sheet.getRange(1, 1, 3, 2).setValues(testData);
  
  // Clean up existing charts
  const charts = sheet.getCharts();
  charts.forEach(c => sheet.removeChart(c));
  
  // Define chart configuration for Sheets API
  const chartConfig = {
    title: "Offset Bug Test (Sheets API)",
    pieChart: {
      threeDimensional: true,
      pieHole: 0.4,
      slices: [
        { offset: 0.25, color: { red: 1, green: 0, blue: 0 } },
        { offset: 0, color: { red: 0.2, green: 0.4, blue: 0.8 } },
        { offset: 0, color: { red: 0.86, green: 0.22, blue: 0.07 } }
      ]
    },
    dataSourceRange: {
      sources: [{
        sheetId: sheet.getSheetId(),
        startRowIndex: 0,
        endRowIndex: 3,
        startColumnIndex: 0,
        endColumnIndex: 2
      }]
    },
    position: {
      overlayPosition: {
        anchorCell: {
          sheetId: sheet.getSheetId(),
          rowIndex: 4, // 5th row (0-indexed)
          columnIndex: 0 // 1st column (0-indexed)
        }
      }
    }
  };
  
  // Insert chart via Sheets API
  Sheets.Spreadsheets.batchUpdate({
    requests: [{
      addChart: { chart: chartConfig }
    }]
  }, ss.getId());
  
  Logger.log("3D Pie chart created with correct slice offset and colors!");
}

3. Hacky Redraw Trick (Not 100% Reliable) #

If you want to stick with the Apps Script Charts API and 3D, you can try forcing a full redraw by temporarily modifying a trivial property and reverting it:

// After inserting the chart
const insertedChart = sheet.getCharts()[0];
// Temporarily adjust pieHole to trigger redraw
sheet.updateChart(insertedChart.modify().setOption('pieHole', 0.41).build());
// Revert to original pieHole
sheet.updateChart(insertedChart.modify().setOption('pieHole', 0.4).build());

Note: This doesn’t work every time, but it’s a quick test if you need a temporary workaround.

Verification #

I tested your original code with the is3D: true line removed, and the slice offset and custom colors rendered perfectly. For 3D charts, the Sheets API method consistently applies the intended styling without issues.

内容的提问来源于stack exchange，提问作者Ya Acva Fu