最佳做法

本文列出提升指令碼效能的最佳做法。

盡量減少對其他服務的呼叫

在指令碼中使用 JavaScript 作業，比呼叫其他服務更快。在 Google Apps Script 中完成任何工作，都比從 Google 伺服器或外部伺服器擷取資料更快，例如對 Google 試算表、文件、協作平台、翻譯和 UrlFetch 發出的要求。盡量減少服務呼叫，腳本執行速度就會更快。

使用共用雲端硬碟協作

如果與其他開發人員共同處理指令碼專案，請使用共用雲端硬碟協作。共用雲端硬碟中的檔案是由群組 (而非個人) 所有，因此更容易開發及維護專案。

使用批次作業

指令碼通常會從試算表讀取資料、執行計算，然後將結果寫回試算表。Apps Script 會使用預先查看和寫入快取等內建最佳化功能。

盡量減少讀寫作業，充分運用內建快取功能。交替讀取和寫入指令的速度很慢。如要加快指令碼執行速度，請使用一個指令將所有資料讀取至陣列，對陣列資料執行作業，然後使用一個指令寫出資料。

請避免交替讀取和寫入，如以下效率不彰的範例所示：

// DO NOT USE THIS CODE. It is an example of SLOW, INEFFICIENT code.
// FOR DEMONSTRATION ONLY
var cell = sheet.getRange('a1');
for (var y = 0; y < 100; y++) {
  xcoord = xmin;
  for (var x = 0; x < 100; x++) {
    var c = getColorFromCoordinates(xcoord, ycoord);
    cell.offset(y, x).setBackgroundColor(c);
    xcoord += xincrement;
  }
  ycoord -= yincrement;
  SpreadsheetApp.flush();
}

指令碼效率不彰，因為它會連續寫入 10,000 個儲存格，並在其中迴圈。雖然回寫快取有助於提升效率，但批次處理呼叫的效率更高：

// OKAY TO USE THIS EXAMPLE or code based on it.
var cell = sheet.getRange('a1');
var colors = new Array(100);
for (var y = 0; y < 100; y++) {
  xcoord = xmin;
  colors[y] = new Array(100);
  for (var x = 0; x < 100; x++) {
    colors[y][x] = getColorFromCoordinates(xcoord, ycoord);
    xcoord += xincrement;
  }
  ycoord -= yincrement;
}
sheet.getRange(1, 1, 100, 100).setBackgrounds(colors);

效率不彰的程式碼執行時間約為 70 秒，而效率高的程式碼只需 1 秒即可執行完畢。

避免在 UI 密集型指令碼中使用程式庫

程式庫方便重複使用程式碼，但會增加指令碼啟動時間。如果用戶端 HTML 服務使用者介面重複發出執行時間較短的 google.script.run 呼叫，就會明顯感受到這段延遲。在外掛程式中盡量少用程式庫，並避免在進行大量 google.script.run 呼叫的指令碼中使用程式庫。

使用 Cache 服務

使用 Cache 服務，在指令碼執行期間快取資源。快取可減少資料擷取頻率。 以下範例說明如何使用快取服務，加快存取速度緩慢的 RSS 資訊動態饋給。

function getRssFeed() {
  var cache = CacheService.getScriptCache();
  var cached = cache.get("rss-feed-contents");
  if (cached != null) {
    return cached;
  }
  // This fetch takes 20 seconds:
  var result = UrlFetchApp.fetch("http://example.com/my-slow-rss-feed.xml");
  var contents = result.getContentText();
  cache.put("rss-feed-contents", contents, 1500); // cache for 25 minutes
  return contents;
}

如果項目不在快取中，您仍需等待 20 秒，但項目到期前，後續存取速度會很快。

大型資料集和複雜計算

Google 試算表是功能強大的工具，但隨著資料集變大和計算變得更複雜，您可能會遇到試算表延遲、IMPORTRANGE錯誤和指令碼逾時等效能問題。

使用資料庫的時機

如果試算表即將達到1 千萬個儲存格的上限，或是您有許多連結的表單 (例如 10 個以上) 和複雜的跨工作表公式，建議使用專屬的資料庫解決方案。

  • Google Cloud SQL：適用於 MySQL、PostgreSQL 和 SQL Server 的全代管關聯資料庫服務。使用 JDBC 服務連線至 Cloud SQL 或其他外部資料庫，例如 Oracle 或 MongoDB (透過適當的橋接器)。
  • BigQuery：無伺服器、高擴充性的資料倉儲。您可以使用連結試算表，直接在試算表中分析大型 BigQuery 資料集，也可以使用 BigQuery 服務，透過 Apps Script 與資料互動。

公式效能最佳化

大量使用特定公式可能會導致試算表速度變慢：

  • ARRAYFORMULA：雖然實用，但大規模的 ARRAYFORMULA 計算可能很耗費資源。
  • VLOOKUP 和 OFFSET：這些函式處理大型資料集時可能會很慢。建議使用 INDEX 和 MATCH 或 Apps Script，更有效率地在記憶體中執行查閱作業。
  • IMPORTRANGE：如果來源工作表很大或負載過重，在許多工作表中頻繁使用 IMPORTRANGE 可能會導致「內部錯誤」。將資料整合至集中式來源，有助於解決這個問題。

處理指令碼逾時

Apps Script 有執行時間限制 (通常每次執行 6 分鐘，部分 Google Workspace 帳戶則為 30 分鐘)。如果指令碼經常因超出執行限制而當機：

  1. 使用批次作業：如「使用批次作業」一節所述，盡量減少對試算表和其他服務的呼叫次數。
  2. 分割工作：將大型工作分成多個小區塊，每個區塊都能在時間限制內完成。
  3. 使用續傳觸發條件：設定可安裝的以時間為準觸發條件，以繼續執行長時間執行的程序。您的指令碼可以使用 Properties 服務儲存目前狀態 (例如最後處理的資料列索引)，並在下次執行時從該處繼續。