Google Apps Script:
Refactoring

How to:

In Google Apps Script, a common scenario that benefits from refactoring is the simplification of cumbersome scripts that interact with Google Sheets or Docs. Initially, scripts might be written in a quick-and-dirty way to get results fast. Over time, as the script grows, it becomes unwieldy. Let’s walk through an example of refactoring for better readability and efficiency.

Original Script:

function logSheetNames() {
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  for (var i = 0; i < sheets.length; i++) {
    Logger.log(sheets[i].getName());
  }
}

This function logs the name of each sheet in a Google Spreadsheet. While it works fine, it employs outdated JavaScript practices and lacks clarity.

Refactored Script:

function logSheetNames() {
  const sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  sheets.forEach(sheet => Logger.log(sheet.getName()));
}

In the refactored version, we’ve switched to using const for variables that don’t change, making our intent clearer. We’ve also used the forEach method, a more modern and concise approach to iterating over arrays, enhancing readability.

Sample Output (for both scripts):

The output in Logger will look something like this, assuming your Google Sheets document has two sheets named “Expenses” and “Revenue”:

[20-04-2023 10:00:00: INFO] Expenses
[20-04-2023 10:00:01: INFO] Revenue

The refactored script achieves the same result but is cleaner and easier to understand at a glance.

Deep Dive

Refactoring in Google Apps Script partially inherits its principles from the broader software engineering practice. It became more recognized and structured as a concept in the late 1990s, notably due to Martin Fowler’s seminal book “Refactoring: Improving the Design of Existing Code” (1999), which provided a comprehensive guide to various refactoring techniques. While the specifics of refactoring can vary across programming languages due to their syntactic and functional differences, the core goal remains the same: improving code without altering its external behavior.

In the context of Google Apps Script, a key aspect to consider during refactoring is the service quotas and limitations imposed by Google. Efficiently refactored code not only reads better but also runs faster and more reliably within these constraints. For example, batch operations (Range.setValues() instead of setting values one cell at a time) can significantly reduce execution time and quota consumption.

It’s important to note, however, that for certain complex projects, Google Apps Script might fall short due to these very limitations. In such cases, looking into alternatives like Google Cloud Functions or Apps Script’s newer sibling, AppSheet, might offer better scalability and functionality.

Ultimately, while refactoring is a critical skill in maintaining and improving Google Apps Script projects, understanding the environment’s limitations and considering alternative solutions is just as important for delivering efficient, robust, and maintainable code.