How to use Google Apps Script to process large amounts of data

Senbei is a traditional Japanese cracker made of rice. It is a very tasty cracker that goes well with Japanese tea, but there are also huge Senbeis as big as your face. What do you do when you eat such a big cracker? Usually, you would crush them into small pieces before eating them. 😁

Quotas for Google Services

The same can be said for processing huge data with Google Apps Script. This is especially important because there are some restrictions on the use of Google Services that you should be aware of. What restrictions are in place are described on this page.

There are many restrictions, but the following two restrictions are the most frequent problems.

1. Script runtime: 6 min / execution
2. Triggers total runtime: 90 min / day (6 hr / day if you have a Google Workspace account)

Script runtime indicates that the execution time of one Script must be less than 6 minutes. And Triggers total runtime indicates the maximum value of trigger execution time based on the total script execution time of the Google Apps account. If you try to process large amounts of data with Google Apps Script, you will soon find yourself trapped by these limitations.

So how can we get around these limitations and process large amounts of data? The key is to divide the process into smaller parts and to use multiple accounts for processing.

Divide the process into smaller parts

The first thing you can consider is to break your process into several smaller parts.

For example, suppose a process consists of 10 parts and takes 10 minutes to process. In that case, you could divide the process into two functions, each containing five pieces, and execute them one by one.

In another case, a process that takes one minute to complete may be looped over 10 targets. Again, the loop can be divided into two parts, the first five (0 to 4) and the next five (5 to 9), both of which can be completed within six minutes.

However, if you have a large number of targets to process, you may find it difficult to divide them into the right number and then execute them all manually one by one. This is where the LongRun class I created comes in!

The LongRun class is a helper class that can complete all loop processes that take a long time by automatically interrupting the loop before the processing time is exceeded and registering the next process in the Trigger service of Google Apps Script. In the simplest usage of the LongRun class, you can achieve long-time processing by simply calling the executeLongRun() function after writing the main processing for each one. If you are interested, please take a look at this article.

Use multiple accounts for processing

Now, You have managed to get past the 6-minute limit wall by splitting up the process! But you’ll soon run into the next wall of limitations: Triggers total runtime must be less than 90 minutes.

It is not uncommon to run into this wall when using the LongRun class or other means to perform long-time processing that makes full use of triggers. For example, if it takes one minute to process one target, a simple calculation shows that no matter how hard you try, you will only be able to process 90 targets.

The only way to solve this problem is to prepare multiple execution users and assign processes to each of them.

For example, in the above example, if you want to process 180 targets (for a total of 180 minutes), you can prepare account A and account B, and have A process the first 90 and B the second 90. In this case, input data (e.g. spreadsheets) and output locations (e.g. folders on Google Drive) need to have permissions set so that both users can see them.

In fact, in my Google Apps Script system, I use four accounts and the LongRun class to achieve a variety of long-time processing.

Conclusion

Even though it has a limited execution time, if you handle it well, you will be able to use Google Apps Script to build various systems. The possibilities are endless! I hope this article will help you with your ideas. 😊