show a processing status in a cell when looping through rows in google sheets using google apps script.
When working with small batches of data it can be useful to show the user that the data is being processed. Here is an example script from Sourabh Choraria which use
to force write updates to a Google Sheet. As this method relies on
it’s worth keeping in mind for bigger data sets that using HTMLService and a custom dialog or
Al from coda has provided this gist
Source: visually display status when looping through google sheets data
Use Github as a back end for caching large objects across platforms. It’s possible to use a github repo as the back end for a caching service, and by taking this approach we can share data between Apps script and multiple platforms. It also means you can retrieve the data with the git CLI in addition. It works in exactly the same way as all the other backends. … Using a regular git Repo means you can make it private and share it using the tools already built into Github.
Source: Github as an Apps Script cache platform – Desktop Liberation
This is a sample script for plotting the points on an image using Google Apps Script. Unfortunately, in the current stage, there are no methods for directly editing the image and putting the texts and some shapes in the methods for Google Apps Script. So in this case, it is required to use the workaround.
Clever workaround that uses Google Slides as a canvas for adding text to images.
Source: Plotting Points on Image using Google Apps Script · tanaike
We’ve featured a couple of community contributions around Google Apps Script Unit Testing and if this is a topic that interests you then the related posts section below will link to additional resources. This post from Dmitry Kostyuk is a great addition and the accompanying
library that is shared in the post has some very useful Google Apps Script related features such as a dedicated method to check your data is a 2D array (particularly useful in the context of handling spreadsheet values). Follow the source link for more details…
Source: Taking Away the Pain from Unit Testing in Google Apps Script
Christian Heilmann’s tweet – Feature request for Gmail: automatically find and follow the unsubscribe link in all highlighted emails – prompted me to build an automated system for unsubscribing your Gmail address from the bulk senders. Here’s how it looks…
This is an older example from Amit Labnol that recently pinged my Google Alerts from this Best Unroll.me Alternatives post. The source post from Amit has some nice details about how marketing emails are marked up and the code shared might be a nice starter for your own solution.
Source: How to Unsubscribe from Mailing Lists and Junk Newsletters in Gmail – Digital Inspiration
I’m a great fan of both Redis and GraphQL. You’ll find plenty of articles about them around on this site. Although I’ve showed many examples of GraphQL and Apps Script, it was never possible to connect up Apps Script to Redis, because redis doesn’t use HTTP to communicate between Client and Server. I’ve come across upstash.com (with a free tier), that fronts a redis database with a GraphQL API.
The upstash.com service looks like a nice find from Bruce Mcpherson and his post details how you can use this for cross platform caching.
Source: 3 Favourite things in one article – redis, apps script and graphQl – Desktop Liberation
Google Sheets has a powerful find and replace function. But you may want to use Google Apps Script to do the same job. StackOverflow has a question on this. The accepted answer would require you to loop through all cells, find the text, replace it and post the new text back to the cell. Google Apps Script has a more powerful, and simpler, method called
Usual post highlighting the
method which you can use to find and replace text in Google Sheets. The post has more information about this method and how to use it.
Source: Replace text in Google Spreadsheets with Apps Script
Send a Google Form submission to a specific Slack channel as a message, via a Webhook.
Screenshot of message when posted in Slack channel
Source: The Gift of Script: Submit a Google Form to a Slack channel via a Webhook
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: www.pbainbridge.co.uk
Redeploying Web Apps without Changing URL of Web Apps for new IDE
A ‘feature’ of the new Google Apps Script editor is managing your script project the deployments. A issue some developers are encountering when updating the published version of their web app is the published url changes. In this gist Kanshi Tanaike describes the problem and highlights how developers can update their published Web App without changing the URL.
Source: Redeploying Web Apps without Changing URL of Web Apps for new IDE
This library used to be part of my cUseful collection, but I’ve decided to pull out into a library in it’s own right. The idea is not only to be able to squeeze more into cache by compression, but also to spread across multiple cache entries. In addition, through the use of plugins, it also allows multiple backend cache stores, all accessed the same way, with the option of creating additional ones. This abstraction allows you to switch platforms as you outgrow them without any main code changes.
Source: Apps script library with plugins for multiple backend cache platforms – Desktop Liberation
A tool to schedule weekly time triggers between two dates in Google Apps Script. Grab the code and ready the quick-use guide or stick around for a deep dive.
Nice solution from Scott Donald to setup timed triggers for a fixed period so you don’t have to worry about manually adding/removing.
Source: How to programmatically schedule weekly time triggers between two dates in Google Apps Script – Yagisanatode
The featured image is by Brandi Redd on Unsplash
One of my favorite things about Google Apps Script is that I can get information from an API and write it directly to a shared document or spreadsheet…It’s not uncommon for these requests to come back in paginated form. Meaning, if your request has 100 responses, they’ll come in batches of n size instead of all at once.
Pagination can be tricky. This post has a video and sample script to help you abstract the process of working with paginated API responses in Apps Script.
Source: Paginating API Calls in Google Apps Script
I’m a public school teacher writing Apps Script projects to manage documentation and to make life easier in general. You can read more on my blog, blog.ohheybrian.com or follow me on Twitter, @bennettscience.
Manage Google Groups membership expiration with Google Apps Script and Cloud Identity API
As noted by Stéphane Giron Google Group member expiration is not available on all types of Google Workspace accounts, but this post is a useful reminder of what is now possible when it is. The most includes a full explanation and code to get started.
Source: Create membership expiration in Google Groups for Google Workspace
Learn how to setup email alerts for YouTube videos and get daily automatically notification when new videos are uploaded that match your search topics.
Very slick example from Amit Agarwal and for the more advanced Google Apps Script developer well worth checking out the source code to see how Amit handles setting up and managing script triggers.
Source: YouTube Email Alerts – Monitor Videos around your favorite Topics – Digital Inspiration
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.
As noted by the post author Dmitry Kostyuk the Google Apps Script V8 runtime opens up many new coding opportunities previously not available to Apps Script developers. Using the common use case, fetching data and writing it to a Google Sheet, Dmitry explains how the façade and proxy code patterns can be used to write efficient easy-to-read code.
Source: Using Design Patterns in Google Apps Script