One the powerful features of Google Apps Script is with a couple of lines of code you can quickly start automating and organising your inbox. For people just getting started with Apps Script Google provide a introductory codelab ‘Accessing Google Sheets, Maps, and Gmail in 4 lines of code!’ and for those wanting to do a bit more the Google Workspace Developer documentation includes a ‘create a mail merge‘ solution.
Mail merge solutions are bit or a reoccurring theme in the world of Google Apps Script. In 2011 the official G Suite Developers Blog featured 4 ways to do Mail Merge using Google Apps Script with community contributions from James Ferreira, Steve Webster and Romain Vialard. The post references Romain’s ‘Yet another Mail Merge’ script, which he went on to develop as a very successful YAMM add-on before refocusing on the Mergo Mail Merge. Even further back when Google Apps Script was officially launched in 2009 this included a introductory video with a mail merge example.
A well as Google official channels the ability to automate your Gmail inbox has regularly caught the attention of the wider tech press. In 2013, Computerworld highlighted Jonathan Kim’s ‘Gmail No Response’ script which goes through your inbox and finds recent emails where you were the last respondent. Jonathan’s blog post is no longer available but the Gmail No Response’ script is on GitHub where it has been forked 100 times.
runs through the emails in your inbox and checks your outgoing messages for a question mark. Once it finds these emails it checks to see if they are in a date range and then sees if you have had a response. If you have not had a reply to your email containing a “?” then it adds the label “No Response”. You can then quickly see all of the threads for which you are awaiting a reply.
If you are interested in more Gmail script solutions then I highly recommend you have a look at content shared by Amit Agarwal. This doesn’t just include Apps Script solutions but also a number of Gmail tips and tricks. As many of these feature the way you can search your Gmail inbox they can also be used with GmailApp.search() or if using the Gmail Advanced Service Gmail.Users.Messages.list. A nice example of where a Gmail user tip can be used in Apps Script is Amit’s post which includes how to Search Emails by Specific Time in Gmail.
In this blog, I am going to show you how to create a To-Do list app using Google AppSheet. A To-do list app lets you write organize and prioritize your tasks more efficiently. In this blog, we will be creating a To-Do list app using Google AppSheet that will allow you to do the same. Additionally, we will be using the automation features in Google AppSheet to send an email as soon a task is completed.
Aryan has written a great tutorial on how to get started and takes us through how we can connect Google Sheets to AppsSheet and how to create a simple to-do list app from it.
I’ve been using Apps Script since 2015 and absolutely love it. I share various projects and ideas on my site bazroberts.com and I have also written a number of books on Apps Script, taking people through step-by-step from the very basics to more complex areas whilst making practical applications. I also share news on Apps Script and on Google Workspace in general on Twitter @barrielroberts
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!
In this post, we’ll be going through a quick workaround so that you can get back to running your scripts. Note that this issue is still not entirely resolved, but you can follow any developments in Google’s issue tracker.
If you are a Google Apps Script developer using a consumer @gmail.com account for development/testing or sharing script projects for other users to use with their gmail.com account you may have encountered the “This app is blocked” issue. This issue appears to prevent a Google account from completing the Apps Script authentication flow even when using limited scopes.
This post from Aiman Fikri provides a solution for getting around this issue by associating an Apps Script project to a Google Cloud Platform (GCP) project. Google also provide documentation on setting up Standard Cloud Platform projects, but if you are supporting novice users directing them to Aiman’s post might be less daunting for them.
There are some benefits of using Standard GCP project particularly when you are developing scripts as it gives access to Cloud logs and Error Reporting. If you encounter “This app is blocked” on all your script projects you can group multiple scripts with a single Cloud Platform project to save having to go through the full setup process.
In Google Chat, Spaces serve as a central place for team collaboration—instead of starting an email chain or scheduling a meeting, teams can move conversations and collaboration into a space, giving everybody the ability to stay connected, reference team or project info and revisit work asynchronously.
Besides the new ability to programmatically create and populate Google Chat spaces outlined in the source post, if you are a member of the Google Cloud Partner Advantage program you may want to apply for the new Google Workspace Developer Preview Program. This program will give you access to this new Chat API functionality and other Google Workspace Developer preview developments.
Not a Cloud Partner Advantage member? You can apply to join
If you are interested in building on the Google Chat platform there is the What’s new in the world of Google Chat apps session at Google I/O (this session will be available on-demand from 12 May 2022).
Iterate through a Google Drive folder of Microsoft Excel files and convert them into individual Google Sheet files.
Bulk convert Excel files to Google Sheets
The following Google Apps Script is designed to iterate through a Google Drive folder of Microsoft Excel files and convert them all to individual Google Sheet files.
I use Google Apps Script to support staff and students in my job. I enjoy dabbling with creating tools to help with automation and I freely share my learning experiences on my blog, where I also have a number of useful Google Add-ons: www.pbainbridge.co.uk
Use Apps Script to normalize your tweet data into tabular format for easy visualization & analysis.
I’ve a personal interest in Twitter data, in particular, how it can be collected analysed in Google Sheets so it was nice to see this example from Nick Young (@techupover). The solution shared by Nick uses Google Apps Script to parse a downloaded archive from a Twitter account and write it to a Google Sheet. A nice weekend project if you are looking for something to do :)
You use Google Sheets as your own Podcast Manager that will automatically download your favorite podcasts to Google Drive and instantly sync across all your devices.
Amit Agarwal is always coming up with creative uses for Google Apps Script. His latest project is a great example of what can be achieved with a little code and a lot of know-how. Even if you aren’t a podcast fan this project is worth checking out as Amit is expert at writing concise and efficient code.
Some highlights to check out once you make a copy of the ‘Podcast Manager’ Google Sheet are use of CacheService for getting/putting a last update time and using the .filter(Boolean) trick for ignoring blank cells when using .getValues() on Google Sheets data. There is plenty more going on and worth spending some time using the Script Editor debugger and breakpoints to learn from a master.
Fortunately, Kanshi Tanaike has been exploring the impact the increased volume of data in Google Sheets has when using Google Apps Script and both SpreadsheetApp and Sheets API. The linked report contains a number of useful findings and strategies for handling large Google Sheets with Apps Script.
One of the benefits of Google moving Apps Script to the V8 engine is the possibility to drop in existing JavaScript libraries. Max Makhrov recently highlighted on Twitter how JSONata, which can be used to query and transform JSON data can be used in Google Apps Script:
JSONata is a lightweight query and transformation language for JSON data. Inspired by the ‘location path’ semantics of XPath 3.1, it allows sophisticated queries to be expressed in a compact and intuitive notation. A rich complement of built in operators and functions is provided for manipulating and combining extracted data, and the results of queries can be formatted into any JSON output structure using familiar JSON object and array syntax. Coupled with the facility to create user defined functions, advanced expressions can be built to tackle any JSON query and transformation task. – JSONata
JSONata is a solution better understood by trying it out, which you can do thanks to the script project shared by Max on Twitter or on the JSONata website. If you are interested in data query/manipulation solutions for Google Apps Script you might also want to check out our previous post on using AlaSQL for Apps Script.
