如何自动刷新Google Data Studio中Google Sheets数据源并免改连接更新数据？

Great question! Let’s break this down into two practical parts to solve your workflow pain points:

Part 1: Update Excel Data Without Changing Data Studio Connection #

The core trick here is to keep your target Google Sheets file (the one Data Studio links to) completely intact—instead of swapping out the file, you overwrite the data inside it. Here’s how to do it:

• Manual, no-coding method:

  1. Open the exact Google Sheets file that your Data Studio project is connected to.
  2. Navigate to the specific worksheet (e.g., named "Raw Data") that’s mapped in your Data Studio connection.
  3. Go to File > Import, then select your updated Excel file (from your computer or Google Drive).
  4. In the import window, choose "Replace current sheet" (double-check you’re selecting the correct sheet to overwrite) and click "Import data".
  5. This replaces old data with new Excel data while keeping the file and worksheet path identical—so Data Studio’s connection stays valid, no edits required.

• Automated method (with Google Apps Script):
  If you want to skip manual imports, use a simple script to pull data from an Excel file stored in Google Drive and overwrite your target sheet:

  1. Open your target Google Sheets file, go to Extensions > Apps Script.
  2. Paste this script (adjust file IDs and sheet names to match your setup):

     function updateFromExcel() {
       // Replace with your Excel file's Drive ID
       const excelFileId = "YOUR_EXCEL_FILE_ID";
       // Replace with your target sheet name in Google Sheets
       const targetSheetName = "Raw Data";
       
       const excelFile = DriveApp.getFileById(excelFileId);
       const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
       const targetSheet = spreadsheet.getSheetByName(targetSheetName);
       
       // Clear existing data (optional, for full overwrite)
       targetSheet.clearContents();
       
       // Import Excel data to the target sheet
       const data = SpreadsheetApp.open(excelFile).getSheets()[0].getDataRange().getValues();
       targetSheet.getRange(1, 1, data.length, data[0].length).setValues(data);
     }
     

  3. Save the script, then set up a Time-driven trigger (in Apps Script, go to Edit > Current project's triggers) to run this script on your preferred schedule (e.g., daily, hourly) whenever your Excel data gets updated.

Part 2: Enable Automatic Refresh for Google Sheets Data in Data Studio #

Google Data Studio (now Looker Studio) doesn’t sync with Google Sheets in real-time by default, but you can configure automatic refreshes or trigger them manually:

• Built-in automatic refresh:

  1. Open your Data Studio project, go to the Data Sources tab.
  2. Select your Google Sheets data source, click the pencil icon to edit it.
  3. Scroll down to the Refresh settings section.
  4. Pick your desired frequency: options include Every 1 hour, Every 6 hours, Every 12 hours, or Once a day.
  5. Save the settings—Data Studio will now automatically refresh data at your chosen interval.

• Manual refresh (for immediate updates):

  • In the Data Sources tab: Click the refresh icon (↻) next to your data source.
  • In the report editor: Click the refresh icon in the top-right corner, or right-click the data source in the sidebar and select "Refresh data".

• Frequent automatic refresh (using Apps Script):
  If you need refreshes more often than 1 hour (the minimum built-in interval), use the Looker Studio API to trigger refreshes via script:

  1. Enable the Looker Studio API in your Google Cloud Project.
  2. Write a script that calls the datasources.refresh method, then set up a time-driven trigger to run it every X minutes (note: free tier has quota limits, so check Google’s documentation for restrictions).

Note: Even if your Google Sheets data updates immediately, Data Studio will only reflect changes after a refresh (automatic or manual) because it caches data to improve performance.

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