AppsScriptPulse

Visually display status when looping through Google Sheets data with Google Apps Script

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 SpreadsheetApp.flush() to force write updates to a Google Sheet. As this method relies on .setValue() instead of .setValues() it’s worth keeping in mind for bigger data sets that using HTMLService and a custom dialog or Speadsheet.toast()

If the .setValue() / .flush() Al from coda has provided this gist

Source: visually display status when looping through google sheets data

Github as an Apps Script cache platform – Desktop Liberation

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

Plotting Points on Image using Google Apps Script · tanaike

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

Taking Away the Pain from Unit Testing in Google Apps Script

Unit testing is an extremely important practice. It helps developers avoid bugs easily and lets you know when something is broken immediately. … There are numerous libraries that exist for unit testing in JavaScript, including Jest and Mocha; however, they aren’t necessarily well-adapted to run under the Google Apps Script environment. … For that reason, I built my own library with Google Apps Script in mind.

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 UnitTestingApp 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

How to Unsubscribe from Mailing Lists and Junk Newsletters in Gmail with Google Apps Script – Digital Inspiration

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

3 Favourite things in one article – Apps Script, Redis and GraphQL – Desktop Liberation

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

Replace text in Google Spreadsheets with Apps Script using TextFinder

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 TextFinder .

Usual post highlighting the TextFinder 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

Submit a Google Form to a Slack channel via a Webhook

Send a Google Form submission to a specific Slack channel as a message, via a Webhook.

Messages can be posted in to a Slack channel via the Google Form

Screenshot of message when posted in Slack channel

Source: The Gift of Script: Submit a Google Form to a Slack channel via a Webhook

Redeploying Web Apps without changing the URL in the new Google Apps Script IDE

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

Apps script library with plugins for multiple backend cache platforms – Desktop Liberation

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

How to programmatically schedule weekly time triggers between two dates in Google Apps Script – Yagisanatode

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

Paginating API Calls in Google Apps Script

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

Create membership expiration in Google Groups for Google Workspace

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

YouTube Email Alerts – Monitor Videos around your favorite Topics [using Google Apps Script] – Digital Inspiration

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

Using Design Patterns in Google Apps Script – Introduction to façade and proxy patterns

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

Subscribe to Apps Script Pulse...