如何通过Google Apps Script实现图表数据区域垂直关联？

Got it, let's work through this problem— I’ve dealt with this exact frustration where the transpose setting vanishes when reloading data ranges, so using Apps Script is the perfect fix here. The setTransposeRowsAndColumns(true) gets us halfway, but to replicate the "Associate ranges Vertically" setting from the UI, we need to explicitly define our domain (X-axis) and series (Y-axis) ranges.

Step-by-Step Implementation #

Here’s a complete script that creates a chart using rows for X and Y axes, with the vertical association preserved even if data ranges update:

function createRowOrientedChart() {
  // Get your spreadsheet and target sheet
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const targetSheet = ss.getSheetByName("YourSheetName"); // Replace with your sheet name

  // Define which rows hold your X and Y axis data (row numbers start at 1)
  const xAxisRow = 2; // Example: X-axis data is in row 2
  const yAxisRow = 3; // Example: Y-axis data is in row 3

  // Grab the full range of data for each row (from column 1 to the last populated column)
  const xDataRange = targetSheet.getRange(xAxisRow, 1, 1, targetSheet.getLastColumn());
  const yDataRange = targetSheet.getRange(yAxisRow, 1, 1, targetSheet.getLastColumn());

  // Build the chart with transpose and vertical association
  const chartBuilder = targetSheet.newChart()
    .setChartType(Charts.ChartType.LINE) // Swap with BAR, COLUMN, etc. as needed
    .addRange(xDataRange)
    .addRange(yDataRange)
    .setTransposeRowsAndColumns(true) // Convert rows to columns for chart processing
    .setDomainRange(xDataRange) // Explicitly set X-axis domain (this = "Associate ranges Vertically")
    .setOption("title", "Row-Based Data Chart")
    .setPosition(6, 2, 0, 0); // Position the chart at row 6, column 2

  // Insert the chart into the sheet
  const finalChart = chartBuilder.build();
  targetSheet.insertChart(finalChart);
}

Key Details Explained #

• setTransposeRowsAndColumns(true): This tells Google Sheets to treat rows as columns, which is required since the chart defaults to column-based data.
• setDomainRange(xDataRange): This is the critical line that replicates the "Associate ranges Vertically" UI setting. It explicitly links your chosen row to the X-axis domain, ensuring the chart associates your Y-axis row data correctly against it— no more losing the setting when ranges reload.

Updating an Existing Chart #

If you already have a chart and want to modify it to use this setup, use this script:

function updateExistingRowChart() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const targetSheet = ss.getSheetByName("YourSheetName");
  const existingCharts = targetSheet.getCharts();

  // Target the first chart in the sheet (adjust index if needed)
  if (existingCharts.length > 0) {
    const chartToUpdate = existingCharts[0];
    const xAxisRow = 2;
    const yAxisRow = 3;
    const xDataRange = targetSheet.getRange(xAxisRow, 1, 1, targetSheet.getLastColumn());
    const yDataRange = targetSheet.getRange(yAxisRow, 1, 1, targetSheet.getLastColumn());

    const updatedChart = chartToUpdate.modify()
      .clearRanges() // Remove old data ranges
      .addRange(xDataRange)
      .addRange(yDataRange)
      .setTransposeRowsAndColumns(true)
      .setDomainRange(xDataRange)
      .build();

    // Replace the old chart with the updated version
    targetSheet.updateChart(updatedChart);
  }
}

Just replace "YourSheetName" with your actual sheet name, adjust the row numbers and chart type to match your data, and you’re good to go.

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