Last week, we talked about the best way to build front-ends for Google Apps Script. Today, letās roll up our sleeves and dive in! š
As I mentioned inĀ my previous post, weāre going to create a super cool sidebar in Google Sheets thatāll help us select emojis with ease. š Google Docs already has this functionality natively: simply head over to the āInsertā menu, select āEmoji,ā and voilĆ ! Youāll see a neat dialogue window with all emojis organized by category, as well as a handy search function:
However, we want more, so, weāll be building our own version. š Unlike Docs, weāll have a full sidebar to work with, and clicking on an emoji wonāt automatically insert it. Instead, itāll copy it into our buffer, so we can paste it wherever we need it. š
That being said, letās dive into using our most important tool: Vite. ā”
Welcome to the Ultimate Google Ultimate Google Apps Script Front-End Development Guide!
If you are reading this post, then you likely have experience with Google Apps Script. Essentially, there are three types of front-ends that you can create with Apps Script:
Web apps
Modal/modeless dialogues
Sidebars
In this blog post series, we will only discuss HTML front-ends, as this is where you can create the most powerful and sophisticated user experiences. Unfortunately, the Card Service is outside the scope of this series, and it is not nearly as powerful as an HTML front-end. With that in mind, Google, how about allowing HTML front-ends for Workspace add-ons?
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.
A lot of users try and quickly learn GAS and use it to make their lives easier. Itās all great, however the code we sometimes tend to come across on StackOverflow and other sites lacks best practices, hence I thought it was time to start bringing them up and I will start today with design patterns.
I’m all for copy/paste coding and it one of the things I love about the Google Apps Script developer community, there are lots of great snippets out there and in Pulse we’ve now over 800 posts and counting. When you start going beyond quick script solutions into more complex projects investing time planning how you’ll structure your code can save you headaches and frustrations further down the line.
Using design patterns are one way to produce better code that is more readable which in turn is more maintainable and can lead to faster development. This post from Dmitry Kostyuk a nice opportunity to learn about a design pattern for a very common use case of maintaining data in a Google Sheet from a third party API.
ChatGPT is pretty much a universal API: one endpoint to get any data.
ChatGPT has recently taken the world by storm. Unless you have been living under a rock, then you have certainly seen a variety of content on this topic. One of its greatest advantages is its ease of use, and it is perfectly usable in Google Apps Script. With that in mind, here is one use case that we can implement: generating fake data.
Fake data is useful for testing purposes. At times, real data is too sensitive, and sometimes you do not have access to real data before production, but you need to begin working with something. Here is where fake data comes in.
We used to have a great library for fake data calledĀ Faker.js; however, Marak, the author, became so frustrated that he could not monetize the solution that heĀ pushed an update that broke everything, which led to him being blocked on GitHub and NPM. As a result, the library is no longer maintained. ChatGPT, however, is not going anywhere.
Illustration by ahmiruddinhidayat111198 on freepik.com https://www.freepik.com/author/fahmiruddinhidayat111198
TheĀ onEditĀ trigger is likely the most used trigger in Google Apps Script. It runs automatically with anĀ event objectĀ whenever you change a value in a spreadsheet ā programmatic changes excluded ā thus allowing you to execute a script based on context. When done properly, it can be extremely powerful. When done wrong, however, it can feel unreliable and messy.
In this article, we will learn to avoid three common pitfalls:
Not Exiting Early
Making a Single Function Do Everything
Expecting onEdit to Catch All Changes by Default
For this purpose, we will build a simple script to handle a task list in Google Sheets. It will do two things: add a checkbox next to new tasks and add a completion date when each task is checked as done.
According to Wikipedia, the Game of Life āis a cellular automaton devised by the British mathematician John Horton Conway in 1970.ā
It begins on a two-dimensional grid of square cells. Each cell can be either alive or dead. Every cell interacts with its eight immediate neighbors. A live cell only remains alive if it has two or three living neighbors. If it has fewer than two living neighbors, it dies as if by underpopulation. Conversely, if it has more than three, it dies as if by overpopulation. A dead cell remains dead unless it has exactly three living neighbors; otherwise, it becomes a live cell, as if by reproduction.
There is no immediate practical use for the Game of Life in a spreadsheet; however, it is a fun algorithmic challenge. Moreover, Google Sheets natively provides us with the perfect data structure: a two-dimensional array. This is all the more reason to work on those array skills!
No Google Apps Script SDK for your favorite API? No problem!
Iāve never seen a Google Apps Script SDK made available as part of an API, as, surprise surprise, itās not the most popular choice for professional development. Itās time to start changing that, so we will learn to build our own!
[Editor note: Really impressive work from Dmitry providing a framework you can use to develop your own service for interacting with 3rd party APIs. The post includes lots of clear instructions and guidance to help you understand and learn about approaches for structuring your Google Apps Script code projects]
Google Apps Script has some amazing built-in stuff. It gives us native access to all Google apps like Sheets and Gmail, seamlessly integrates with GCP services like BigQuery, allows for the building of interfaces with HTML and CardService, facilitates the creation of simple webhooks/APIs and web apps with simple and efficient client-server communication, can make use of any API through UrlFetchApp, and can be bundled into add-ons for efficient distribution. In my experience, itās enough for 99% of all Google Apps Script developers.
However, one thing that Google Apps Script is missing is modules. NPM has an extremely impressive database of JavaScript modules that donāt automatically integrate with Apps Script. Of course, in Apps Script we have libraries, but the selection is extremely limited and there is no marketplace for those. By the way, who wants to participate in creating one? Let me know in the comments! However, the very first warning on the libraries documentation page notes that libraries make Apps Script slow. Well, Apps Script is already far from being the fastest programming language on Earth, so slowing it down further is not an idea that Iām a fan of!
But what if I told you that there actually is a way to use some NPM modules in Apps Script? You just need to bundle them with Webpack. Not sure what I mean? Keep reading.
When it came out, the new Google Apps Script IDE was, of course, a big deal. Itās way better than the legacy one. It brought syntax highlighting, keyboard shortcuts, command palette and just a much better UI experience.
In fact the reason itās so good is that itās built on top of VS CodeĀ Monaco editor. But itās definitely still far away from what a real VS Code installation can do. You can get all the functionality of the online IDE and much more more: autocomplete, custom themes, installation of modules, linting, snippets, etc.
Give your scripts privileges that your users donāt have … In 99% of all cases, authorizations in Google Apps Script are extremely straightforward. When a user executes their script, they run it as themselves with their respective authorization scopes. … However, what if you need to give more rights to your app beyond what your intended users will have? … This is where service accounts come in.
A useful post to find out more about service accounts and how to use them with Google Apps Script. The tutorial put together by Dmitry Kostyuk includes example code for using a service accounts with Firestore, BigQuery and the Admin Directory API.
