如何本地缓存GOOGLEFINANCE查询结果，实现#N/A时fallback并自动更新？

Absolutely, you can build a local cache system to handle those frustrating #N/A errors from GOOGLEFINANCE—here are two practical approaches depending on your needs:

Option 1: Pure Formula Solution (Quick & Simple) #

This uses iterative calculation to keep a cached value that updates only when GOOGLEFINANCE returns a valid result.

1. Set up your query cell: Let’s say you’re using cell A1 for your GOOGLEFINANCE call, e.g.:

   =GOOGLEFINANCE("USDGBP", "price", DATE(2024,1,1))
   

2. Enable iterative calculation:
   • Go to File > Settings > Calculation
   • Check "Enable iterative calculation" and set the maximum number of iterations to 1
3. Create your cache cell: Pick a cell (e.g., B1) and enter this formula:

   =IF(NOT(ISNA(A1)), A1, B1)
   

4. Update dependencies: Point all cells that need the exchange rate to B1 instead of A1.

How it works: When A1 returns a valid number, B1 updates to match it. If A1 throws #N/A, B1 sticks to its last valid value.

⚠️ Note: Iterative calculation can cause unexpected behavior in complex spreadsheets, so this is best for simple setups.

Option 2: Google Apps Script Solution (Reliable & Automated) #

For a more robust system that avoids iterative calculation and auto-updates the cache when GOOGLEFINANCE works, use a script with time-based triggers.

1. Open the script editor: Go to Tools > Script Editor
2. Paste this code:

   // Updates cache with valid GOOGLEFINANCE results
   function updateCache() {
     const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
     const queryCell = sheet.getRange("A1"); // Replace with your query cell
     const cacheCell = sheet.getRange("B1"); // Replace with your cache cell
     const queryValue = queryCell.getValue();
     
     // Only update cache if query returns a valid number (not #N/A)
     if (typeof queryValue === "number" && !isNaN(queryValue)) {
       cacheCell.setValue(queryValue);
     }
   }
   
   // Creates a time-based trigger to check for updates
   function createCacheTrigger() {
     // Run every 10 minutes (adjust interval as needed)
     ScriptApp.newTrigger("updateCache")
       .timeBased()
       .everyMinutes(10)
       .create();
   }
   
   // Manual update button function
   function manualCacheUpdate() {
     updateCache();
     SpreadsheetApp.getUi().alert("Cache updated successfully!");
   }
   

3. Customize cell references: Replace "A1" and "B1" with your actual query and cache cells.
4. Set up the trigger: Run the createCacheTrigger function once (click the play button next to it) to enable auto-updates. You’ll need to grant basic permissions when prompted.
5. Add a manual update button (optional):
   • Go back to your sheet, click Insert > Drawing
   • Draw a button shape, add text like "Update Cache"
   • Click the three dots on the drawing > Assign script > enter manualCacheUpdate

How it works: The script checks your GOOGLEFINANCE cell on a schedule. If it finds a valid rate, it updates the cache cell. All dependent cells use the cache cell, so they’ll never show #REF or #N/A when GOOGLEFINANCE fails.

Final Tips #

• For historical rates that rarely change, you could even set the trigger to run once daily instead of every 10 minutes.
• If you have multiple currency pairs, extend the script to handle multiple query/cache cell pairs.

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