如何将Google Sheet B的图表动态同步至主表Google Sheet A？

Hey there, I’ve tackled this exact problem before—let’s break down the solutions based on what kind of sync you need:

Solution 1: Sync Only Data-Driven Chart Updates #

If you just need the chart in Sheet A to reflect changes to Sheet B’s underlying data (not changes to the chart’s design like type, colors, or axes), this is the simplest approach:

• First, use IMPORTRANGE in Sheet A to pull in all the data from Sheet B’s "sheet 1". Create a new sheet in A (e.g., name it "Imported B Data") and enter this formula in cell A1:

  =IMPORTRANGE("YOUR_SPREADSHEET_B_ID", "sheet 1!A:Z")
  

  Replace YOUR_SPREADSHEET_B_ID with the actual ID of Sheet B (you can grab this from the URL of Sheet B). You’ll need to grant permission for A to access B’s data when prompted.
• Next, recreate the exact same chart from Sheet B in Sheet A, but use the imported data in "Imported B Data" as the source.
• Now, whenever Sheet B’s data updates, IMPORTRANGE will sync the data to A, and your chart in A will automatically reflect those changes.

Note: This won’t sync changes to the chart’s design (like switching from a bar chart to a line chart)—you’ll have to manually update the chart in A if you tweak the one in B.

Solution 2: Full Sync (Chart Design + Data) #

If you need every change to the chart in Sheet B (both data and design) to show up in Sheet A, you’ll need to use Google Apps Script to automate the sync. Here’s how:

1. Open Sheet A, go to Extensions > Apps Script to open the script editor.
2. Replace the default code with this script (make sure to update the placeholder values):

   function syncChartFromBToA() {
     // Replace these with your actual spreadsheet IDs
     const spreadsheetBId = "PASTE_SHEET_B_ID_HERE";
     const spreadsheetAId = "PASTE_SHEET_A_ID_HERE";
     
     // Get the target sheets
     const sheetB = SpreadsheetApp.openById(spreadsheetBId).getSheetByName("sheet 1");
     const sheetA = SpreadsheetApp.openById(spreadsheetAId).getSheetByName("sheet 1");
     
     // Get the chart from Sheet B (adjust if you have multiple charts)
     const chartsInB = sheetB.getCharts();
     if (chartsInB.length === 0) return;
     const chartToSync = chartsInB[0]; // Assumes first chart is the one you want
     
     // Delete the old synced chart in Sheet A (filter by title to target specific charts)
     const chartsInA = sheetA.getCharts();
     chartsInA.forEach(chart => {
       if (chart.getOptions().get('title') === chartToSync.getOptions().get('title')) {
         sheetA.removeChart(chart);
       }
     });
     
     // Insert the fresh chart from B into Sheet A (adjust position as needed)
     sheetA.insertChart(chartToSync.modify()
       .setPosition(10, 1, 0, 0) // Inserts at row 10, column 1
       .build());
   }
   

3. Update PASTE_SHEET_B_ID_HERE and PASTE_SHEET_A_ID_HERE with your actual spreadsheet IDs. You can also adjust the chart position (setPosition parameters) or add more specific logic to target the right chart if Sheet B has multiple.
4. Set up a trigger to run this script automatically:
   • In the Apps Script editor, click the Triggers icon (clock symbol) on the left sidebar.
   • Click Add Trigger, then configure it:
     • Choose syncChartFromBToA as the function to run.
     • Set the event source to Time-driven.
     • Pick a frequency that fits your needs (e.g., every hour, every day).
   • Save the trigger—you’ll need to grant the script permission to access your spreadsheets when prompted.

Now the script will regularly copy the latest version of the chart from B to A, keeping everything in sync.

Quick Troubleshooting: Why Didn’t Copy-Paste Work? #

If you tried copying the chart directly to A and it didn’t sync, it’s almost always a permission issue. Right-click the chart in A, select Data source, and check if it can access Sheet B. If you see a permission error, open Sheet B and make sure the editor of Sheet A has at least view access to Sheet B.

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