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?
Have you heard about ChatGPT? It’s the latest buzz in the world of AI and everyone’s eager to unleash its true powers. As I delved deeper, I wondered — is it possible to integrate ChatGPT with Google Sheets? Can we pass prompts from Google Sheets and get a response from ChatGPT?
After some research, I discovered that it’s indeed possible to integrate ChatGPT with Google Sheets using the Open AI API. In this blog, we’ll be exploring how to bring AI to your spreadsheets using the Open AI API and Google Apps Script.
This blog post discusses how to integrate AI in Google Sheets using Google Apps Script by passing prompts to ChatGPT, a large language model. It provides step-by-step instructions on how to set up the integration and use it to generate responses to prompts.
Aryan Irani is a Google Developer Expert for Google Workspace. He is a writer and content creator who has been working in the Google Workspace domain for three years.
If you are playing around with Sheet colors with Apps Script, you sometimes find yourself with font colors that don’t go well with the background colors you’ve chosen. However, we can use Yiq values to decide whether the luminance of the background color would be best with a light or a dark foreground font color. Here’s a small Apps Script library to figure it out for you.
Nice little helper library from Bruce Mcpherson if you’ve like to automatically apply contrasting colours to Google Sheets ranges. The source post from Bruce provides the background to the YIQ colour system as well as how to use the library. Follow the source link for more details.
you can use the following Apps Script to create folders in Google Drive for students and share those folders with their email addresses based on data from a Google Sheet
Whilst this post from Amit Agarwal looks at the automatic creation of Google Drive folders, it’s also worth having a look at it at for an Apps Script pattern for iterating over and processing data from Google Sheets. In particular there is a getStudentData()function that turns the 2D array return from .getValues() into an object array which includes a rowIndex. This makes it easy to update your Google Sheet when each row is processed.
If you monitor important websites and require advanced features, tools such as UptimeRobot is the solution for you. However, if you only have a few personal websites and want a simple, customizable, and free alternative for monitoring them, this may be a better option.
In this tutorial, I will guide you through setting up a website monitor using Google Apps Script and Google Sheets. This monitor will periodically check the status of your websites and notify you via email and Google Chat (optional) if any issues are detected.
We’ve featured other website uptime solutions using Google Apps Script, this one is nice because it also includes an optional Google Chat integration allowing to send alerts your preferred Chat Space. Another useful feature, used by a number of paid for uptime monitors, is defining a keyword which must be found in the page. Follow the link to the source post for all the code and instructions on getting started.
In this article I’ll show you how to setup reminder emails that will get sent automatically each day if there are any pending tasks in your spreadsheet. Stay tuned until the end, where I’ll show you an easier way to accomplish this using Coda.
Eric Koleda must be the undisputed master of the Apps Script demo. The ‘random cat’ Add-on, is one of my favourite examples where Eric finds a really simple hook to get you engaged in what can often be a complex project. Eric is now Developer Advocate at Coda and he’s not lost is eye. In this post Eric gives the gift of script with a basic example of how you can handle email notifications from Google Form responses in batches with a timed trigger (cats being replaced for cakes). Eric also shows how a no-code alternative can be accomplished in Coda.
Let’s jump off the new tech bandwagon and use 40 year old technology. VisiCalc, arguably the first spreadsheet software, is now available inside of Google Sheets. Just like it was installed on an Apple II. Enjoy the 80’s era, Chiptune-esque, 8 bit-ish VisiCalc emulator inside of Google Sheets.
For those not around in 1979 VisiCalc was the first spreadsheet computer program for PCs. There is a great Wikipedia article with information about VisiCalc which highlights:
VisiCalc is one of the earliest examples of metaphor-driven user interface design, due to its resemblance with paper spreadsheets. Compared to paper spreadsheets, VisiCalc freed users to change numbers without having to recalculate the whole spreadsheet by hand, which, according to Steven Levy, “changed the perception of a spreadsheet from a document of hard costs into a modelling tool by which one tested business scenarios”.
Having a play with VisiCalc 2023 my immediate thought is why doesn’t Google Sheets have a native dark mode on desktop devices. Just one of the many things they got right is 1979. Andrew has included some other lovely touches including a product serial number (remember to keep this in a safe place), and a print command.
If you are on Twitter and would like to share your VisiCalc memories or reflections headover to this thread.
Image credit: Full diagram of invoice automation in Gmail with Document AI and Apps Script – Stéphane Giron
Tired of manually managing your invoices in Gmail? Say goodbye to the hassle and hello to efficiency with Document AI and Apps Script.
There is a lot of buzz around generative AI, but it’s also worth remembering Google have a well established service offer in other forms of AI, the Document AI being one example of this. This post from Stéphane Giron highlight an Apps Script based workflow for analysing invoices attached to Gmail messages using the Document AI. There is a bit to setup in a Cloud project to get this working and as a paid for service costs to consider. Further information on this and all the code you need to get started are included in the source post.
In Google Workspace gam is probably the ‘go to’ tool command line tool which allows administrators to easily manage domain and user settings. Recently I was asked about how you can keep gam generated reports up-to-date using Google Apps Script. It’s worth remember that gam uses public Google Workspace APIs when it performs actions and reports. In this post I show you a pattern for building script to keep gam reports fresh.
For read-heavy applications that don’t involve components external to Apps Script accessing the cache and don’t exceed the CacheService limits, use CacheService. For write-heavy applications or for when some external parts require access to the same cache, use Spreadsheet App.
An interesting report from Ignacio Lopezosa Serrano on the relative performance of CacheService and SpreadsheetApp for reading/writing data with Google Apps Script. Some surprising results and something I think to be kept in mind is how these tests compare to ‘real world’ conditions. As also pointed out in the post there are some service limitations of CacheService to keep in mind particularly around storage size limits.
