Google Apps Script两函数单独正常，合并后XLS导出滞后问题

Hey there, let's break down why your XLS export is lagging when you run test() to call both functions, and how to fix it.

The Problem Recap #

Your workflow works perfectly when running newValuesToDatabase() or updateALLITEMS() alone, but when combining them:

• First export is fine
• Second export uses the first update's values
• Every subsequent export is one step behind, even with a 10-second delay

Why This Happens #

Google Apps Script's services (especially SpreadsheetApp and DriveApp) use batch processing and caching to optimize performance. When you run your two functions back-to-back:

1. newValuesToDatabase() makes changes to your Google Sheet, but these changes are held in a local cache instead of being immediately synced to Google's servers.
2. updateALLITEMS() runs before the cache is flushed, so it exports the old, unupdated version of the sheet.
3. Fixed delays like Utilities.sleep(10000) don't solve this because they don't wait for the actual sync to complete—they just pause the script.

Solutions to Fix the Lag #

1. Force Spreadsheet Changes to Sync Immediately

The most critical fix is adding SpreadsheetApp.flush() at the end of newValuesToDatabase(). This method forces all pending spreadsheet operations (like clearing content or writing data) to be sent to Google's servers right away.

Modify newValuesToDatabase():
Add these lines right before you trash the CSV file:

// Force all pending spreadsheet changes to sync to the cloud
SpreadsheetApp.flush();
// Close the spreadsheet to ensure no lingering connections
addSpreadSheet.close();

csvFile.setTrashed(true); //Then trashes the csv file in the end

2. Add a Short, Targeted Delay (Optional but Safe)

Even with flush(), giving Drive a tiny window to update the file metadata can prevent edge cases. Update your test() function to include a short sleep after the first function:

function test() {
  newValuesToDatabase();
  // Give Drive 2 seconds to sync the updated sheet (adjust if needed)
  Utilities.sleep(2000);
  updateALLITEMS();
}

3. Optimize the Export URL for Fresh Data

In updateALLITEMS(), tweak the export URL to ensure you're grabbing the latest version of the sheet. Replace your existing URL line with:

const url = `https://docs.google.com/spreadsheets/d/${checkID}/export?format=xlsx&exportFormat=xlsx`;

This explicitly specifies the export format and helps avoid any cached versions of the sheet.

4. Verify File Sync with Last Modified Time (For Edge Cases)

If you still see issues, you can add a check to wait until the sheet's last modified time updates, ensuring Drive has synced the changes. Update test() like this:

function test() {
  newValuesToDatabase();
  
  // Get the target sheet file to check sync status
  const workFolder = DriveApp.getFolderById("(workFolderid)");
  const checkSSFile = workFolder.getFilesByName("Update Items (ikke rør)");
  const targetSheet = checkSSFile.next();
  const initialUpdateTime = targetSheet.getLastUpdated().getTime();
  
  // Wait up to 30 seconds for the sheet to sync
  let waitAttempts = 0;
  while (waitAttempts < 30) {
    Utilities.sleep(1000);
    // Refresh the file's metadata
    targetSheet.setTrashed(false);
    const currentUpdateTime = targetSheet.getLastUpdated().getTime();
    
    if (currentUpdateTime > initialUpdateTime) {
      break; // Sync complete, move to export
    }
    waitAttempts++;
  }
  
  updateALLITEMS();
}

Full Modified Code Snippets #

Here's how your key functions will look after applying the fixes:

Updated newValuesToDatabase() Ending

} else {
    console.log("Could not find a mode in the form submission");
  }

  // Force sync and close the spreadsheet
  SpreadsheetApp.flush();
  addSpreadSheet.close();

  csvFile.setTrashed(true); //Then trashes the csv file in the end
}

Updated test() Function

function test() {
  newValuesToDatabase();
  Utilities.sleep(2000); // Short sync delay
  updateALLITEMS();
}

Why These Fixes Work #

• SpreadsheetApp.flush() eliminates the local cache issue by pushing all changes to Google's servers immediately.
• A short delay gives Drive time to update the file's status after the flush.
• The optimized export URL ensures you're not pulling a cached version of the sheet.
• The last modified check adds a safety net for slower syncs.

内容的提问来源于stack exchange，提问作者Didrik.B