如何固定Google Finance货币转换结果？实现公式输出自动复制锁定

Got it, let's break down how to lock in that dynamic exchange rate calculation so it doesn't update automatically. The issue with VALUE() makes total sense—it still ties back to the live GOOGLEFINANCE data, so it'll keep changing whenever the rate updates. Here are two solid solutions:

1. Manual Paste Values (Quick & Simple for One-Time Use) #

If you just need to lock the value occasionally, this is the fastest way:

• First, let your formula =A1*GOOGLEFINANCE("currency:AUDGBP") calculate the current value in its cell (say, cell B1).
• Select cell B1, press Ctrl+C (Windows) or Cmd+C (Mac) to copy it.
• Go to the cell where you want the fixed value (e.g., C1), right-click, and choose Paste special > Paste values only (or use the shortcut Ctrl+Shift+V / Cmd+Shift+V).

This replaces any formula in the target cell with a plain, static number that won't change unless you edit it manually.

2. Google Apps Script (Automated or Repeatable Locking) #

If you need to automate this process (e.g., lock the value daily, or on demand), a small script will do the trick:

1. Open your Google Sheet, click Extensions > Apps Script to open the script editor.
2. Delete the default myFunction() code, then paste this:

function lockExchangeRateValue() {
  // Update these ranges to match your sheet:
  const sourceFormulaCell = SpreadsheetApp.getActiveSpreadsheet().getRange('B1'); // Cell with your GOOGLEFINANCE formula
  const targetStaticCell = SpreadsheetApp.getActiveSpreadsheet().getRange('C1');   // Cell where you want the fixed value
  
  // Grab the current calculated value and write it as static to the target
  const fixedRateValue = sourceFormulaCell.getValue();
  targetStaticCell.setValue(fixedRateValue);
}

3. Save the script (click the floppy disk icon) and give it a name like "LockExchangeRates".
4. To run it manually: Click the play button ▶️ in the script editor. The first time, you'll need to grant basic permissions for the script to access your sheet.

You can even set up a time-driven trigger (via the clock icon in the script editor) to run this automatically at specific intervals—like every morning to lock the day's starting rate.

Why VALUE() Didn't Work #

Just to clarify: =VALUE(GOOGLEFINANCE("currency:AUDGBP")) only converts the dynamic result to a numeric type, but it still references the live GOOGLEFINANCE feed. So whenever the exchange rate updates, the VALUE() function will recalculate with the new rate. It doesn't break the link to the dynamic data, which is why it wasn't fixing your issue.

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