Maximizing Google Apps Script Performance Now in REALITY Tech #109

Hello! I'm AL, a developer on the server team at REALITY. I have a passion for curry and seafood. Today, I'll be sharing my experience with Google Apps Script and how to use it effectively in enterprise environments.

What is Google Apps Script

Google Apps Script(or GAS in short) is a powerful scripting platform developed by Google. It allows users to automate tasks across Google Workspace apps like Sheets, Docs, Drive, and more. This cloud-based platform uses JavaScript as its programming language, making it suitable for both seasoned developers and beginners. 

How we use it

At REALITY, we leverage Google Apps Script to manage our 'master data', which is essential for updating and configuring certain aspects of our service. The data itself is managed through Google Sheets, while GAS scripts are used to export specific sheet data into JSON. The exported data is then imported into our database via server APIs. Google sheet already provides a flexible and familiar UI for manipulating data. Therefore, by directly exporting data from Google Sheets to JSON, we eliminate the need for a dedicated front-end UI for data entry. 

Here are some of the key features of Google Apps Script that we frequently use at REALITY

• Adding Menu Items: Additional menu items, such as buttons, enable operators to run GAS scripts on specific sheets.

• Performing Data Integrity Checks: Before data export, our scripts perform checks to ensure that the data meets certain quality standards. Checks that are usually performed are:

  • missing/empty columns

  • erroneous entries

  • date validation 

• Adding HTML UI element: We use UI elements like dialog boxes to increase the level of interactivity within Google Sheets

This combined usage of Google Sheets and Google Apps Script has helped us increase operational efficiency when it comes to updating large amount of data in our system. This in turn have reduced man hours for developers and non-developers alike.

Limitations & pitfalls

While Google Apps Script offers significant abilities, it also has some major limitations. Two such limitations that we experienced are

1. Execution time limits: A script run by a user can only execute for 6 minutes per execution.

2. Lack of True Concurrent Execution: Google Apps Script does not support true multithreading or parallel processing in the traditional sense. The environment is fundamentally single-threaded, reflecting its underlying JavaScript architecture

When working with a big dataset, these limitations can cause a script execution to either take a long time or fail entirely. On top of that, poor implementation of the script can make these problems worse. Therefore, it is helpful to following good practices and use workaround to overcome the limitations of Google Apps Script. 

Good practices

While Google Apps Scripting has some unique features and quirks, many fundamental principles of good JavaScript coding still hold true. Following some key practices that we have implemented and benefitted from at REALITY 

Reduce frequent API calls: Making frequent calls to Google Sheets API within loops can slow down a script due to the time taken in communicating back and forth over the network. It's more efficient to read once and re-use the data whenever possible.

For example

for (let i = 0; i < data.length; i++) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
}

This script is inefficient because it performs multiple operations (retrieving the active spreadsheet, getting the sheet by name) inside a loop. Instead, we can retrieve the sheet only once and reuse it, which would be much more efficient

const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
for (let i = 0; i < data.length; i++) {
  // use sheet
}

Reduce unnecessary checks : When working with Google Apps Script, minimizing unnecessary checks and iterations is crucial to achieve faster execution times. In the case of exporting master data, we need to iterate over every column in a sheet, but there are instances where early returns can help reduce redundant code executions.

In the context of a script which is used to export master data of a item inventory, active items should be prioritized over expired ones. Therefore, we can skip the validation of expired items.

for (var i = 0; i < data.length; i++) {
    var itemData = data[i];
    var expirationDate = new Date(itemData[2]); // Assuming expiration date is in the third column
    var today = new Date();
    if (expirationDate <= today) {
      continue;
    }
    // otherwise continue with further validations
}

Utilize UI elements for better UX : UI elements can be used to provide the users insights into the state of the script execution. For example, when a GAS script fails to export data due to validation errors, we can give the operator feedback regarding what went wrong using a modal dialog.
 

finish without error display 

finish with error display 

Conclusion

As we continue to explore its capabilities and optimize its use, Google Apps Script remains a critical tool for our teams at REALITY. By understanding the constraints and leveraging GAS scripts strategically, we have managed to enhance our productivity and meet business needs.