Using Design Patterns in Google Apps Script

Dmitry Kostyuk
Mar 11 · 8 min read

Introduction

Google Apps Script is a JavaScript-based language that has access to Google Workspace-specific libraries for Gmail, Google Sheets, Google Forms, Google Drive, etc., and allows you to quickly and efficiently automate your tasks and program business applications.

What We Will Be Building

We will be building a Forex app that will pull data from Fixer.io and write it into a Google spreadsheet. Nothing complicated, yet we will try to build something robust and easy to read and to maintain.

Let’s Think This Through First

It’s a good habit to get into. Before coding, you usually want to spend some time planning your app. Depending on the difficulty of the task, this can take various lengths of time and in certain cases you may want to draw a flow chart or a UML diagram. I personally like to use diagrams.net for this.

Connecting to the Fixer API (Façade Design Pattern)

Register at Fixer, it’s easy. Get your API key. Now let’s look at the documentation. Let’s select our endpoints and our base currency. So our wanted behaviour is that if we are requesting today’s rates then we want to use the latest endpoint (that gives us current data and refreshes every hour) and for other days we want to use the historical report with date end points in YYYY-MM-DD format.

http://data.fixer.io/api/YYYY-MM-DD?access_key=YOUR_ACCESS_KEY&base=JPY&symbols = USD,AUD,CAD,PLN,MXN

Forex Façade Source Code

Let’s Make Use of Our Façade

11:19:27 AM Notice Execution started
11:19:27 AM Info Fetching external API data for endpoint latest
11:19:27 AM Info {"success":true,"timestamp":1614851767,"base":"EUR","date":"2021-03-04","rates":{"USD":1.202812,"AUD":1.545806,"CAD":1.521888,"PLN":4.55404,"MXN":25.119842}}
11:19:28 AM Notice Execution completed

Converting JSON to Array (Adapter Design Pattern)

We can pull JSON data, that’s great, however writing to Google Sheets requires that we convert our data to a 2D array if we want to have some practical use for it. Let’s get to it.

11:24:35 AM Notice Execution started
11:24:35 AM Info Fetching external API data for endpoint latest
11:24:35 AM Info [2021–03–04, 1.202812, 1.545806, 1.521888, 4.55404, 25.119842]
11:24:36 AM Notice Execution completed

Requesting Multiple Dates

Let’s build a dates generator and then loop through each one of those dates. I like to have a class that will just return an array of dates taking the number of days to go back as an argument. And of course we want to have those dates in the right YYYY-MM-DD format so that they work as endpoints in our API calls.

11:31:08 AM Notice Execution started
11:31:08 AM Info Fetching external API data for endpoint latest
11:31:09 AM Info Fetching external API data for endpoint 2021-03-03
11:31:09 AM Info Fetching external API data for endpoint 2021-03-02
11:31:09 AM Info Fetching external API data for endpoint 2021-03-01
11:31:09 AM Info Fetching external API data for endpoint 2021-02-28
11:31:09 AM Info [[Date, EUR/USD, EUR/AUD, EUR/CAD, EUR/PLN, EUR/MXN], [2021-03-04, 1.202812, 1.545806, 1.521888, 4.55404, 25.119842], [2021-03-03, 1.20503, 1.553294, 1.52696, 4.545716, 25.255725], [2021-03-02, 1.20851, 1.544452, 1.527201, 4.537775, 24.902455], [2021-03-01, 1.20496, 1.550459, 1.523425, 4.533241, 24.890299], [2021-02-28, 1.208897, 1.559459, 1.534943, 4.520672, 25.135473]]
11:31:09 AM Notice Execution completed

Reading and Writing to the Spreadsheet

Now that we are able to generate our 2D array, let’s also add a SpeadsheetApp Façade so we can write and read to our spreadsheet easily:

[Date, Sat Feb 27 18:00:00 GMT-05:00 2021, Fri Feb 26 18:00:00 GMT-05:00 2021, Thu Feb 25 18:00:00 GMT-05:00 2021, Wed Feb 24 18:00:00 GMT-05:00 2021, Tue Feb 23 18:00:00 GMT-05:00 2021]

Implementing the Proxy Design Pattern

Now that we are able to pull the data and convert it to an array and build out our 2D array, it’s time to build our Proxy so that we don’t query data that we already have. This is what the pattern looks like:

ForexProxy Source Code

How it works

The ForexProxy class has 2 methods: cache() and get(). Cache, well, caches the data we already have in our spreadsheet into our registry and get() returns a row of data. It first checks the registry for data that we had cached there; if it finds it, it returns the corresponding row from the cache, otherwise it queries the FixerAdapter. As you can see, our Proxy connects to FixerAdapter, this way when the Fixer API is queried, we already get the data in the array format. So the conversion is now being handled behind the scenes. Neat right?

11:46:24 AM Notice Execution started
11:46:24 AM Info Fetching external API data for endpoint latest
11:46:24 AM Info returning cached data for endpoint 2021-03-03
11:46:24 AM Info returning cached data for endpoint 2021-03-02
11:46:24 AM Info returning cached data for endpoint 2021-03-01
11:46:24 AM Info returning cached data for endpoint 2021-02-28
11:46:25 AM Notice Execution completed

Conclusion

This is it! We’ve got ourselves an efficient easy-to-read and maintain code that makes use of efficient and relevant design patterns. Let me know what you think in the comments and maybe try and implement some of the optional improvements below.

About the author

Dmitry Kostyuk, full-time GAS developer, founder of Wurkspaces.dev

Geek Culture

Proud to geek out.

Sign up for Geek Culture Hits

By Geek Culture

Subscribe to receive top 10 most read stories of Geek Culture — delivered straight into your inbox, once a week. Take a look.

By signing up, you will create a Medium account if you don’t already have one. Review our Privacy Policy for more information about our privacy practices.

Check your inbox
Medium sent you an email at to complete your subscription.

Dmitry Kostyuk

Written by

Full-time GAS developer, founder at Wurkspaces.dev

Geek Culture

A new tech publication by Start it up (https://medium.com/swlh).

Dmitry Kostyuk

Written by

Full-time GAS developer, founder at Wurkspaces.dev

Geek Culture

A new tech publication by Start it up (https://medium.com/swlh).

Medium is an open platform where 170 million readers come to find insightful and dynamic thinking. Here, expert and undiscovered voices alike dive into the heart of any topic and bring new ideas to the surface. Learn more

Follow the writers, publications, and topics that matter to you, and you’ll see them on your homepage and in your inbox. Explore

If you have a story to tell, knowledge to share, or a perspective to offer — welcome home. It’s easy and free to post your thinking on any topic. Write on Medium

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store