AppsScriptPulse

Pseudo OnEdit Trigger for Google Document using Google Apps Script

In the current stage, there is not OnEdit trigger for Google Document. But I sometimes have the case that I want to use OnEdit trigger. So, as the current workaround, I created this sample script. I think that this workaround can be also used for Google Slides, Google Form and also Google Spreadsheet. In the case of Spreadsheet, what the existing OnEdit trigger and the time-driven trigger cannot do can be achieved.

Source: Pseudo OnEdit Trigger for Google Document using Google Apps Script

How to Automatically Add Teachable Students to Google Drive Files and Folders when they Enroll in your Course – Yagisanatode

Learn how to instantly share newly enrolled teachable students to Google Drive files and folders with Google Sheets and Apps Script.

Scott ‘Yagi’ Donald provides a very thorough walk-through of how he has connected a webhook from a third party service to Google Sheets to share specific Google Drive folders and files with named users. This post is well worth a visit as it contains a number of tips and tricks like using Google Forms to follow-up with user email addresses that are not associated with a Google account.

Source: How to Automatically Share Teachable Students to Google Drive Files and Folders when they Enroll in your Course – Yagisanatode

How to get Google Lighthouse reports using PageSpeedInsights API and Google Apps Script

Lighhouse

Image: CC-BY Google

Lighthouse is an open-source, automated tool for improving the quality of web pages. You can run it against any web page, public or requiring authentication. It has audits for performance, accessibility, progressive web apps, SEO and more.

On StackOverflow there is a nice question/answer from Mert Dökümcü on How to send Google Lighthouse reports to Slack using PageSpeedInsights API and Google Apps Script. You can read the full solution there and below is an excerpt from Mert’s post (copied here CC-BY-SA Mert Dökümcü) of the bit that really interested me, getting results from PageSpeedInsights .

First and foremost, get…

Once you have these, go to https://script.google.com/home and create a new script. The following code should do the trick:

var mobileData = fetchDataFromPSI('mobile');
var desktopData = fetchDataFromPSI('desktop');
function pageSpeedApiEndpointUrl(strategy) {
const apiBaseUrl = 'https://www.googleapis.com/pagespeedonline/v5/runPagespeed';
const websiteHomepageUrl = ''; // Your website
const apikey = ''; // Your API key
const apiEndpointUrl = apiBaseUrl + '?url=' + websiteHomepageUrl + '&key=' + apikey + '&strategy=' + strategy;
return apiEndpointUrl;
}
function fetchDataFromPSI(strategy) {
const pageSpeedEndpointUrl = pageSpeedApiEndpointUrl(strategy);
const response = UrlFetchApp.fetch(pageSpeedEndpointUrl);
const json = response.getContentText();
const parsedJson = JSON.parse(json);
const lighthouse = parsedJson['lighthouseResult']
const originLoadingExperience = parsedJson['originLoadingExperience']
const result = {
'overall_performance': originLoadingExperience['overall_category'],
'score': lighthouse['categories']['performance']['score']*100,
'firstContentfulPaint': lighthouse['audits']['first-contentful-paint']['displayValue'],
'speedIndex': lighthouse['audits']['speed-index']['displayValue'],
'timeToInteractive': lighthouse['audits']['interactive']['displayValue'],
'firstMeaningfulPaint': lighthouse['audits']['first-meaningful-paint']['displayValue'],
}
return result; 
}

Checking Google Groups membership recursively using Google Apps Script

Learn how to check indirect membership of users or groups in the context of the Google Groups Service using Apps Script.

This is a great post if you want to learn about how to programmatically navigate the hierarchy of Google Groups, which may also come in handy when you are processing other tree like structures often found in JSON. All the code needed to run this is provided and clearly explained.

Source: Getting Google Groups membership recursively using Apps Script

How to Write Google Apps Script Code Locally in VS Code and Deploy It With clasp

Google Apps Script Power Up

Why Write GAS Code Locally?

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.

The bare minimum that you would need is :

  • A VS Code installation (duh!)
  • Node.js + NPM : a JavaScript runtime with a built-in module installer;
  • Autocomplete;
  • clasp: a CLI utility to sync your GAS code

Let’s get into it!

Source: How to Write Google Apps Script Code Locally in VS Code and Deploy It With clasp

How to Delete Blank Rows from Tables in Google Documents – Digital Inspiration

How to remove all blank rows from one or more tables in a Google Docs document with Google Apps Script. You may also delete blank rows from tables in Google Slides.

Source: How to Delete Blank Rows from Tables in your Google Documents – Digital Inspiration

Mergo: Designing a template gallery in a Google Workspace add-on

Mergo is a mail merge add-on for Gmail. With the new gallery, users can easily create, share & reuse templates.

Instead of starting your email from scratch, you can open the gallery and select a template: it will appear as a draft in Gmail.

We recently had Romain Vialard join us on Totally Unscripted to talk about some of his add-ons. This post is another example of Romain’s continual exploration of Apps Script, this time looking the new Grid view for the CardService. You an find out more in the source link.

Source: Mergo: Designing a template gallery in a Google Workspace add-on

Sending Outlook Emails using Microsoft Account with Google Apps Script

This is a sample script for sending Outlook emails using Microsoft account with Google Apps Script. Before you use this script, please install OnedriveApp which is Google Apps Script library. And, please authorize your Microsoft account for using Microsoft Graph API.

The author of this contribution, Kanshi Tanaike, always comes up with interesting Google Apps Script solutions. This particular example uses a OnedriveApp library which makes it easy to interact with other part of the Microsoft Graph API expanding the possibilities.

Source: Sending Outlook Emails using Microsoft Account with Google Apps Script

Sending Multiple Emails using Batch Request with Gmail API using Google Apps Script

This is a sample script for sending multiple emails using the batch request with Gmail API using Google Apps Script. When multiple emails are sent using “ GmailApp.sendEmail ” and “ MailApp.sendEmail ”, a loop is used. But in this case, the process cost becomes high. In this post, I would like to introduce the sample script for reducing the process cost under this situation. Gmail API can be requested with the batch request. The batch request can be processed with the asynchronous process. By this, I thought that the process cost for sending multiple emails. So, this sample script sends multiple emails using the batch request with Gmail API.

I imagine many in the Apps Script developer community have a script or two that sends email. This post highlights a solution using the Gmail API to send emails in a batch, keeping in mind the limitations highlighted in the post.

Source: Sending Multiple Emails using Batch Request with Gmail API using Google Apps Script

Announcing the Google Forms API

Google is proud to announce the Google Forms API! The Forms API is currently available in Restricted Beta, with Open Beta expected to follow in Q4. … The new Google Forms API provides programmatic access for managing forms and acting on responses, empowering developers to build powerful integrations on top of Forms.

The Forms API is currently in Restricted Beta. We encourage you to apply here to be an early adopter to get started with the API today! We’ll also send you important updates about Open Beta and improvements to the API. To keep up to date with all the APIs of your favorite Google Apps, please subscribe to the Google Workspace Developer Newsletter.

On Totally Unscripted we were delighted to have Christian Schalk (Developer Advocate, Google) and Hannah Pho (Software Engineer, Google) as well as early Forms API partners Charles Kemp (Strategic Alliances Manager, Zapier) and Charles Wiles (CEO, Zzish), talk about the new Forms API. A recording of the show is on YouTube and show notes will soon be added to the Totally Unscripted website.

Source: Announcing the Google Forms API

Executing Function with Minutes timer in Specific Times using Google Apps Script [and tips on creating triggers in V8]

This is a sample script for executing a function with the minutes timer in the specific times using Google Apps Script. For example, when this sample script is used, the following situation can be achieved: Execute a function every 10 minutes only in 09:00 – 12:00 and 15:00 – 18:00 for the weekday.

A clever bit of coding from Kanshi Tanaike for running a Google Apps Script on a defined schedule. The part I found particularly interesting was the solution for programmatically setting triggers in the V8 runtime:

This sample script used Web Apps. Because when v8 runtime is used, when the trigger is set from the function executing by a trigger, the trigger is disabled. This is the recognized bug. But unfortunately, this has still not been resolved. (September 21, 2021) But, when the trigger is installed by Web Apps, no issue occurs. So, please deploy Web Apps.

For the sample code and explanation visit the source link.

Source: Executing Function with Minutes timer in Specific Times using Google Apps Script

How to Make Your NPM Package Available in Google App Script

Boboss74, CC BY-SA 4.0, via Wikimedia Commons

I recently came across GAS — Google App Script and immediately the first hurdle I had to overcome to was make my NPM packages available on there. I had to hop through a couple loops to make this happen. I wanted to call out the steps in this blog so it’ll hopefully help someone out there as well.

In Pulse we’ve previously highlighted a couple of approaches for using NPM libraries in Google Apps Script. The linked source post come from, recent guest on Totally Unscripted, Nima Poulad, Senior Software Engineer at DocuSign. Nima highlights a quick and dirty approach of using eval() on hosted NPM libraries, but goes on to show how Browseify can be used to convert NPM libraries, highlighting some considerations for this approach.

Source: How to Make Your NPM Package Available in Google App Script

Multiple Sheets Action. Use Macro Recorder 🔴

Image credit: Max Makhrov

The idea: perform the same action on multiple sheets. The idea is not new, my approach was to use macro each time to see the correct code snippet. It was useful and saved me hours on my current project.

A clever solution from Max Makhrov with some boilerplate script that makes it easy to use a recorded macro across multiple sheets. To make this solution even better I’ve suggested an addition which activates the sheet which makes it easier to reference recorded macro functions. To see in action here is a copy of Max’s Google Sheet with updated code.

Source: Multiple Sheets Action. Use Macro Recorder 🔴

How to call Google Cloud Run (or Cloud Functions) from Google Apps Scripts

I struggled how to make an authenticated call from Apps Scripts to Cloud Run even though I had owner rights on the GCP project. This post describes how to do it without reinventing the wheel.

Some very useful setup tips for using Cloud Run with Google Apps Scripts including additional references if you are keen to explore more.

Source: How to call Google Cloud Run (or Cloud Functions) from Apps Scripts

[Testing] Concurrent writing to Google Sheets using Google Form and HTML form

When the users try to write to Spreadsheet using a form, the developers have to consider to the concurrent submission from the form. For example, when the multiple users submit the data with the form simultaneously, all data are possibly not to be saved to the Spreadsheet. So it is considered that it is important to know the information about the concurrent writing to Google Spreadsheet using a form. In this report, such situation was investigated.

A very useful analysis to see how many form submissions can be handled in Google Sheets at the same time. The experiment looks at both submissions using Google Forms and as a Web App.

Source: Concurrent Writing to Google Spreadsheet using Form

How to Request Payments with Stripe Checkout and Google Sheets – Digital Inspiration

How to use Google Sheets to generate Stripe payment links and request payments in any currency from customers anywhere in the world!

We’ve previously featured Amit Agarwal’s Razorpay and Google Sheets payment solution. This latest example from Amit highlights how to integrate Stripe payments with Google Sheets. The source post contains the code you’ll need, how to setup and also automation options.

Source: How to Request Payments with Stripe Checkout and Google Sheets – Digital Inspiration

How to Use Service Accounts and OAuth2 in Google Apps Script

Image: Dmitry Kostyuk

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.

Source: How to Use Service Accounts and OAuth2 in Google Apps Script

Create Google Calendar events from sending yourself a Gmail on a mobile device using Google Apps Script

This Google Apps Script allows you to create a Google Calendar event by sending yourself an email in Gmail. Unlike Gmail’s current feature to create Google Calendar events, this script allows you to be on a mobile device.

We’ve featured a couple of contributions from Al Chen in the past and this is another great one. For some context Al has also posted Productivity hack for creating a Google Calendar event by sending yourself an email. For the source code and setup follow the link at the end of the post. If you use Product Hunt and find this solution you can upvote here.

Source: GitHub – al-codaio/events-from-gmail: Create Google Calendar events from sending yourself a Gmail on a mobile device using Google Apps Script.

How to Validate Specific Users on a Web App in Google Apps Scripts – Yagisanatode

Learn how to validate specific users on a Web App, Google Workspace sidebar or dialogue box with Google Apps Scripts. In this tutorial, we will explore how to validate selected users to provide access to your web app. For our example, we validate users based on whether or not they have edit access to a Google Drive file ( a common occurrence). In the discussion, we will also look at alternative ways of validating emails.

Interesting solution for access control to a Apps Script web app by using a Google Doc share permissions as a proxy. The post contains all the code you need and a discussion of other approaches.

Source: How to Validate Specific Users on a Web App in Google Apps Scripts – Yagisanatode

How to Create a Telegram Bot for Sending Notifications using Google Apps Script – Digital Inspiration

Learn how to create your own Telegram bot with Google Apps Script and post notification messages from Google Sheets, Forms and other Google apps.

We’ve a couple of Telegram bot posts already shared in Pulse. The latest come from Amit Agarwal who shares his insight, highlighting some particularly useful functionality and use cases. More details in the source link.

Source: How to Create a Telegram Bot for Sending Notifications using Google Apps Script – Digital Inspiration

Bypassing the Maximum Script Runtime in Google Apps Script

Image: Dmitry Kostyuk

Google Apps Script is an amazing language that can automate a lot of your work. However, working with GAS also means that you have to learn to live with its built-in limitations and quotas. One such quota is the total script runtime. … I have experienced that the time required to complete tasks like copying or even simply listing files on a drive or in a directory can be quite long. Merging hundreds or thousands of documents can also take longer than both thresholds. Now let’s look into how we can build a solution

Another community contribution looking at handling script runtime and this one is also worth looking into for tips on structuring your code. The tutorial includes lots of useful information and an explanation of what is going on and might be a useful example to look if you are interested in moving your Apps Script coding abilities to the next level.

Source: Bypassing the Maximum Script Runtime in Google Apps Script

List All Users in an Organisation’s Google Workspace Account with Google Apps Script – Yagisanatode

Note! This tutorial is for Google Workspace for organisations and not the free consumer account, unfortunately. While the Google Apps Script docs provide a great example of how to get a list of users in a Domain on a Google Workspace account, it is not in the scope of the documentation to go into the weeds and explain all the ways we can search for all users.

In this tutorial, we will cover how to access your Google Workspace organisation’s user data, what data you can retrieve and how it looks, who can retrieve it and a couple of ways to display what you need.

Scott ‘Yagi’ Donald gives another thorough post for Google Apps Script users. Whilst targeted at Google Workspace users this tutorial has some great explanations of common Apps Script patterns, such as pagination and query parameters, that are useful to know when interacting with other Google APIs.

Source: List All Users in an Organisation’s Google Workspace Account with Google Apps Script – Yagisanatode

Website Status Monitoring using Google Sheets [and Google Apps Script with optional notifications to Google Chat]

A Spreadsheet-bound apps script solution to conduct automated status monitoring on websites listed by the user in a Google Sheets management file. A separate status log file in Google Sheets will be created so that users can easily integrate data with BI services such as Google Data Studio. Notifications of changes in website status will be sent to the user’s Gmail. An optional setting to send notifications to Google Chat is available.

Nice example of using Google Chat for individual/group notification. The developer of this is also very responsive on Github if you encounter issues or have suggested changes.

Source: Website Status Monitoring using Google Sheets

Deliver asynchronous notifications in Google Chat using webhooks (Webhooks + Apps Script = Magic)

For community members receiving these timely updates, this “bot” may seem magical. In reality, it’s neither magic nor a traditional Chat bot, so the reference in the Chat UI calling it a “bot” is a bit of a misnomer. The Google Updates “bot” is in fact a simple Google Apps Script application that parses the RSS feed about new posts, and sends them asynchronously to the room via webhooks.

We’ve highlighted the ‘Wexbot’ before both in a Pulse post and in a Totally Unscripted episode, but nice to see it also feature in the official Google Developers blog.

Source: Deliver asynchronous notifications in Google Chat using webhooks

Get a list of Google Shared Drives by ID and Name in Google Apps Script – Yagisanatode

If your organisation is using Google Workspace Business Standard, Business Plus, Enterprise, or one of the other supported plans, you are likely taking advantage of the power of Google’s Shared Drives.

If you have decided to create a Google Apps Script project that needs to get a list of your Shared Drive (or a user’s shared drives in the case of a WebApp), then you might be scratching your head right now wondering how to get this list using the built-in DriveApp class.

Whelp, unfortunately, at the time of writing this article the DriveApp class does not have this functionality. However, it is pretty easy to access in a single line of code using an Advance API.

Source: Get a list of Google Shared Drives by ID and Name in Google Apps Script – Yagisanatode

How to Find and Replace Text in Google Docs with RegEx Search Patterns – Digital Inspiration

It is easy to search and replace text in Google Documents with the DocumentApp service of Google Apps Script. You can use use findText method with simple regular expressions to find text elements in the document that match a pattern and replace them with the specified text. All well and good but in some cases, this simple search and replace function may fail if the search text does not transform into a valid regular expression.

Some more regex goodness this time from Amit Agarwal who provides some very useful tips on escaping characters when using the DocumentApp service and findText method. Click through to the source link for more details.

Source: How to Find and Replace Text in Google Docs with RegEx Search Patterns – Digital Inspiration

String validation for Google Apps Script projects

String validation for Google Apps Script projects

Sourabh Choraria has packaged some of the validator.js methods into a Google Apps Script library. Ported validators currently include isUrl and isEmail , date validators and more. Sourabh has also posted the backstory behind this library.

Source: GitHub – validatorgs/validator.gs: String validation for Google Apps Script projects.

Using npm modules inside of Google Apps Script

Boboss74, CC BY-SA 4.0, via Wikimedia Commons

I recently was processing some data using Apps Script, and needed to parse out second-level domain info from a bunch of URLs. This is definitely not the job for regular expressions, but it’s perfect for an npm module, psl, that uses the public suffix list.

But while Apps Script has come a long way, and features lots of ES2015+ goodness nowadays, it’s not possible to pull in arbitrary code from npm and run it directly. To work around this…

Jeff Posnick provides some guidance on how esbuild can be used to bundle npm libraries for use in Google Apps Script. For an alternative approach to this problem you might also want to check Adam Morris’ appscriptsModules.gs solution on GitHub.

Source: Using npm modules inside of Apps Script

Subscribe to Apps Script Pulse...