随机字符串生成器频繁重算问题求助

Hey there, let's tackle this problem you're facing. The core issue here is that using RANDBETWEEN formulas in your script causes automatic recalculation every time any script runs—including when you hit the "Save" button. That's why the saved string never matches what you see on screen; by the time saveString() executes, the formulas have already spit out a new random value.

Here's a straightforward fix that replaces formula-based generation with script-calculated static values, so you'll always save exactly what you see:

Step 1: Rewrite the Generate Function to Use Static Values #

Instead of writing formulas into cells, we'll handle the random selection directly in the script and write plain text to the cells. This eliminates automatic recalculation entirely.

function generateString() {
  const ss = SpreadsheetApp.openById('1b9rP39sgZDOZqu7AmZhOxX9J8CMukmUw7NPY3Qzuq78');
  const sheet = ss.getSheetByName('Randomizer');
  
  // Grab all non-empty words from column A (optimized to only used range)
  const wordList = sheet.getRange(1, 1, sheet.getLastRow(), 1)
    .getValues()
    .flat()
    .filter(word => word !== '');
  const listLength = wordList.length;
  
  // Helper function to pick a random word
  const pickRandomWord = () => wordList[Math.floor(Math.random() * listLength)];
  const word1 = pickRandomWord();
  const word2 = pickRandomWord();
  const word3 = pickRandomWord();
  
  // Write static text values to cells (no formulas!)
  sheet.getRange('D4').setValue(word1);
  sheet.getRange('E4').setValue(word2);
  sheet.getRange('F4').setValue(word3);
  
  // Combine and write the full string to P4
  const combinedString = `${word1}${word2}${word3}`;
  sheet.getRange('P4').setValue(combinedString);
}

Step 2: Simplify the Save Function #

Now that cells hold static text, the save function just needs to read the current value and append it to your saved sheet—no more unexpected recalculations:

function saveString() {
  const ss = SpreadsheetApp.openById('1b9rP39sgZDOZqu7AmZhOxX9J8CMukmUw7NPY3Qzuq78');
  const sourceSheet = ss.getSheetByName('Randomizer');
  const targetSheet = ss.getSheetByName('Saved Strings');
  
  // Get the exact string displayed in P4
  const stringToSave = sourceSheet.getRange('P4').getValue();
  
  // Append to the next empty row in the Saved Strings sheet
  const nextEmptyRow = targetSheet.getLastRow() + 1;
  targetSheet.getRange(nextEmptyRow, 1).setValue(stringToSave);
}

Why This Works #

• No more unwanted recalculations: Cells D4, E4, F4, and P4 now contain plain text, so their values only change when you explicitly run generateString().
• What you see is what you save: When you click "Save", you're capturing the exact string displayed on screen, not a newly recalculated one.
• Faster performance: We're only fetching the used range of column A instead of the entire column, which speeds up script execution.

Bonus Optimization #

If you want to avoid picking the same word multiple times in one generated string, adjust the random selection logic to shuffle the list and pick unique entries:

// Modified to pick 3 unique words
const shuffledList = [...wordList].sort(() => 0.5 - Math.random());
const word1 = shuffledList[0];
const word2 = shuffledList[1];
const word3 = shuffledList[2];

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