请求：为批量单元格function处理添加进度/完成状态指示器

Great question—this is such a common frustration when running lots of custom functions in Google Sheets! Let’s break down what’s possible:

Do Google Sheets have a built-in progress indicator for this? #

Short answer: No, not natively. That green "Running script" popup only tracks the initial script trigger phase, not the asynchronous execution of individual custom functions across cells. Once that popup disappears, Sheets doesn’t provide a built-in way to show how many cells have finished loading or when all functions will complete.

Alternative Solutions (Better Than Manual Scrolling!) #

Since there’s no out-of-the-box feature, here are practical workarounds that give you clear progress updates:

1. Switch to a Script-Driven Batch Process (Most Reliable) #

Instead of using cell-based custom functions, rewrite your logic to run via a button-triggered script that handles calculations in bulk. This lets you track progress directly:

• Track with a status cell: Create a dedicated cell (e.g., A1) to display progress. As your script calculates values for each cell, update this cell with a percentage like completed/total * 100 or a message like Finished 450/1000 cells.
• Use the Sheets status bar: Update the bottom status bar with real-time progress using:

  SpreadsheetApp.getActiveSpreadsheet().setStatus(`Processing: ${completed}/${total} cells done`);
  

• Alert when done: Once all calculations finish, pop up a clear notification:

  SpreadsheetApp.getUi().alert("All calculations are complete! You can now review the data.");
  

This approach avoids the "Loading.." chaos entirely because you’re controlling exactly when and how values are written to cells.

2. Improved "Scan for Loading Cells" Script #

Your original idea of checking for "Loading.." cells can work if you fix the sync issue:

• Instead of a one-time scan, use a looping script that checks at intervals. For example:

  function checkLoadingStatus() {
    const sheet = SpreadsheetApp.getActiveSheet();
    const targetRange = sheet.getRange("B2:Z1000"); // Adjust to your range
    const values = targetRange.getDisplayValues();
    let loadingCount = 0;
    
    values.forEach(row => {
      row.forEach(cell => {
        if (cell === "Loading..") loadingCount++;
      });
    });
  
    if (loadingCount === 0) {
      SpreadsheetApp.getUi().alert("All cells have finished loading!");
    } else {
      // Update a status cell with remaining count
      sheet.getRange("A1").setValue(`Loading remaining: ${loadingCount} cells`);
      // Check again in 5 seconds
      Utilities.sleep(5000);
      checkLoadingStatus();
    }
  }
  

Bind this script to a button, and it’ll keep checking until all cells are done—no manual scrolling needed.

3. Use a Custom "Progress" Function (Cell-Based Workaround) #

If you must keep using cell-based custom functions, you can add a lightweight tracking system:

• Have each custom function update a shared counter (stored via PropertiesService) when it finishes executing.
• Create a simple helper function like =GET_PROGRESS() that reads this counter and calculates a percentage based on the total number of functions you expect to run.
• Note: This can be tricky with variable execution counts (since users might add/remove functions), but it works if you can predefine or dynamically calculate the total number of functions.

Final Recommendation #

The batch script approach is the most robust—you get full control over progress tracking and avoid the ambiguity of cell-based custom functions. It eliminates the need to hunt for "Loading.." cells entirely.

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