The Prince Academy株式会社 | Performance Optimization Techniques | Advanced Scripting Techniques and Best Practices | Introduction to Google Apps Script: Automate Your Workflows and Boost Productivity

As your Google Apps Script projects grow in complexity and data volume, performance becomes a critical factor. Slow scripts can lead to frustration, timeouts, and a general decrease in productivity. Fortunately, Google Apps Script provides several techniques to optimize your code and ensure it runs efficiently. This section will explore some of the most impactful strategies you can employ.

Minimize API Calls: Every interaction with Google services (like Sheets, Docs, Calendar, etc.) incurs a cost in terms of time and resources. The most significant performance gains often come from reducing the number of these calls. Instead of fetching data row by row or cell by cell, aim to retrieve and set data in bulk whenever possible. This significantly reduces latency.

function inefficientGetSheetData() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
  var lastRow = sheet.getLastRow();
  var data = [];
  for (var i = 1; i <= lastRow; i++) {
    data.push(sheet.getRange(i, 1, 1, 5).getValues()[0]); // Inefficient: Many getValues() calls
  }
  return data;
}

function efficientGetSheetData() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
  var range = sheet.getDataRange(); // Get all data at once
  var values = range.getValues();
  return values;
}

Use getValues() and setValues() for Ranges: As demonstrated above, retrieving and setting data in chunks using getValues() and setValues() with a defined Range object is far more efficient than iterating and calling getValue() or setValue() repeatedly.

function processSheetEfficiently() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Data');
  var dataRange = sheet.getDataRange();
  var values = dataRange.getValues();

  // Process the 'values' array in memory (much faster than interacting with the sheet)
  var processedData = values.map(function(row) {
    return row.map(function(cell) {
      if (typeof cell === 'string') {
        return cell.toUpperCase();
      }
      return cell;
    });
  });

  // Set all processed data back to the sheet in one go
  dataRange.setValues(processedData);
}

Cache Data: If you need to access the same data multiple times within a single script execution, consider caching it in a variable. This avoids redundant API calls to fetch the same information repeatedly. Be mindful of the script's execution environment; cached data is only available for the duration of that specific script run.

var scriptProperties = PropertiesService.getScriptProperties();

function readAndCacheData() {
  var cachedData = scriptProperties.getProperty('MY_APP_DATA');
  if (cachedData) {
    Logger.log('Using cached data.');
    return JSON.parse(cachedData);
  } else {
    Logger.log('Fetching and caching data.');
    // Simulate fetching data from a slow source or an API
    var fetchedData = fetchExternalData(); 
    scriptProperties.setProperty('MY_APP_DATA', JSON.stringify(fetchedData));
    return fetchedData;
  }
}

function fetchExternalData() {
  // This is a placeholder. Replace with your actual data fetching logic.
  Utilities.sleep(1000); // Simulate a delay
  return ["item1", "item2", "item3"];
}

Avoid Global Variables When Possible: While global variables can be convenient, they can also lead to unintended side effects and make code harder to debug, especially in larger scripts. Local variables are generally preferred as they limit scope and can aid in performance by being garbage collected when they are no longer needed.