如何让Google Sheet自动从Google Drive替换后的CSV导入并替换数据？

Great question—this is a super common workflow for teams using Google Workspace with automated reporting tools. Let’s break down two key solutions to solve this: automatically updating your Sheet when the CSV is replaced in Drive, and manually/scheduled imports from an existing CSV in Drive.

This uses Google Apps Script to listen for changes to your CSV file and overwrite your Sheet with fresh data automatically. Here’s how to set it up:

• First, open your target Google Sheet, go to Extensions > Apps Script to launch the script editor.
• Replace the default code with this custom script (update the placeholder values to match your setup):

  function updateSheetFromCSV() {
    // Replace these with your own details
    const csvFileId = "YOUR_CSV_FILE_ID"; // Grab this from the CSV's Drive URL
    const sheetName = "Sheet1"; // Name of the sheet you want to update
    const targetSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
    
    try {
      // Fetch and parse the CSV file from Drive
      const csvFile = DriveApp.getFileById(csvFileId);
      const csvContent = csvFile.getBlob().getDataAsString();
      const csvData = Utilities.parseCsv(csvContent);
      
      // Clear existing data (remove this line if you want to keep custom headers)
      targetSheet.clearContents();
      
      // Write the new CSV data to the sheet
      if (csvData.length > 0) {
        targetSheet.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);
      }
      
      console.log("Sheet updated successfully with latest CSV data");
    } catch (error) {
      console.error("Error updating sheet:", error);
    }
  }
  

• Next, set up a trigger to run this script when the CSV is modified:
  1. In the Apps Script editor, click the clock icon (Triggers) on the left sidebar.
  2. Click Add Trigger, then configure:
     • Choose function: updateSheetFromCSV
     • Deployment type: Head
     • Event source: Google Drive
     • Event type: When a file is changed
     • Select the file to monitor: Pick your specific CSV file
  3. Click Save and authorize the script when prompted (you’ll need to grant access to your Drive and Sheets).

If you don’t need real-time updates (or want a backup option), use one of these methods:

Option A: IMPORTDATA Function (Quick, but with caveats) #

• In your Sheet, enter this formula in the cell where you want data to start (e.g., A1):

  =IMPORTDATA("YOUR_CSV_DIRECT_LINK")
  

  To get the direct CSV link: Right-click the CSV in Drive > Share > Copy link, then replace open?id= in the URL with export?format=csv&id=.

• Caveat: IMPORTDATA caches data for ~1 hour. To force a refresh, add a random parameter:

  =IMPORTDATA("YOUR_CSV_LINK&refresh="&RANDBETWEEN(1,1000))
  

  This will refresh whenever the sheet recalculates (e.g., when you edit a cell).

Option B: Scheduled Apps Script (Reliable, full control) #

• Use the same updateSheetFromCSV script from Solution 1, but set a time-driven trigger instead:
  1. Go to the Triggers menu in Apps Script.
  2. Add a new trigger with:
     • Event source: Time-driven
     • Trigger type: Pick Week timer or Month timer to match your report schedule, or Day timer for daily checks
     • Time zone: Match your local time zone
  3. This will automatically pull the latest CSV data into your Sheet on your chosen schedule.

• Permissions: Ensure the CSV file in Drive is shared with the same Google account used for the Sheet/Apps Script, or set to "Anyone with the link can view" (if security allows).
• CSV Format: Make sure the CSV’s column structure matches your Sheet—if columns change, the script will still write data, but you may need to adjust your Sheet layout.
• Header Preservation: If you want to keep custom headers in your Sheet, remove the targetSheet.clearContents() line and modify the getRange to start writing data from row 2 (e.g., targetSheet.getRange(2, 1, csvData.length, csvData[0].length)).

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