Retrieve comments with emoji reactions from Google Docs, Google Slides and Google Sheet using Google Apps Script

This report introduces the method for retrieving the Emoji reactions from the comments in Google Docs files (Google Documents, Google Slides, and Google Spreadsheets) using Google Apps Script.

Here’s a clever workaround by Kanshi Tanaike for retrieving comments with emoji reactions in Google Docs, Slides, and Sheets using Google Apps Script. The process to achieve this is a little convoluted in that Google Docs, Slides and Sheets are exported in Microsoft equivalent formats, then re-imported into Google formats. To remove some of the pain the post includes sample code snippets for achieving this, which can easily be adapted.

Source: Retrieve Comments with Emoji Reactions from Google Documents, Google Slides, and Google Spreadsheets using Google Apps Script

How to send Base64 images in Gmail with Google Apps Script

Gmail will not render base64 images embedded inside HTML emails but with Google Apps Script, you can use blobs to send the base64 encoded images

Base64 is a way of encoding binary data (like images) as a string of text. This makes it possible to embed images in webpages and HTML emails without hosting images on a server. An issue however, particularly for email, is that not all email services like Gmail support base64 encoded images.

Amit Agarwal’s latest blog post discusses a workaround for Gmail which involves using Google Apps Script to convert the base64 images to a blob and then embedding the blob in the email. There are some nice techniques in this post for handling/searching text strings with regular expressions as well as converting them to a Gmail friendly format.

Source: How to Send Base64 Images in Email with Google Apps Script – Digital Inspiration

Dynamic chart heading in Google Sheets 📊 with a little Apps Script


Image script: Ben Collins'

Today we’ll see how to link a chart title to a cell, so that the chart title automatically reflects whatever value is in the cell

Today’s Pulse snippet comes courtesy of Ben Collins’ excellent Google Sheets Tips Newsletter, Tip 272. It uses a very basic onEdit() trigger to update a chart title based on a cell value. Ben has a great way of highlighting solutions without getting readers lost in complexity. Hopefully this example highlights the how easily you can modify Google Sheet charts using Apps Script.

If you are an Apps Script novice and looking for an easy way to learn what else is possible my own tip is to start the macro recorder, modify an embedded Google Sheets chart and then look at the resulting macro code in the Script Editor.

Source: Sheets Tip 272: Dynamic chart heading in Sheets 📊

Apps Script Pattern. Stop Script Execution on conditions from a Google Sheet

The common pattern for checking the business logic before executing automation

Here’s a clever little snippet from Max Makhrov which combines Google Apps Script with conditional logic created using Google Sheets functions, the resulting cell value being used for the error message.

 * @param {String} rangeName
 * @returns {Boolean} toStopExecution
function getStopMessageBoxFromNamedRange_(rangeName) {
  var ss = SpreadsheetApp.getActive();
  var r = ss.getRangeByName(rangeName);
  var v = r.getValue();
  if (v === '') {
    return false;
  var stopHeader = 'The script was stopped';
  Browser.msgBox(stopHeader, v, Browser.Buttons.OK);
  return true;

If after reading Max’s post you are unsure how this works, here is an example Google Sheet with some test data and logic as well as a ‘My Menu’ open to test the bound script.

Source: Apps Script Pattern. Stop Script Execution on conditions from Sheet

Creating and using a settings section in Google Sheets for your Apps Script projects


Image credit: Dimitris Paxinos

Create a reusable settings page in Google Sheets, using Apps Script, where configurations are easily accessible, even to those without a coding background.

I’ve not seen the numbers but would imagine the majority of Google Apps Script projects are script bound to Google Sheets. Google Sheets provide a useful data canvas which is familiar to users, which can also be used to quickly interface your script solutions. You could of course use dialogs and sidebars combined with the Properties Service to collect and store settings, but coding these in HTML/JavaScript can be time consuming.

This post from Dimitris Paxinos includes a nice Apps Script code pattern for getting and setting user settings from a Google Sheets tab. This includes some nice features including in memory storage and methods to use Script Properties. The post includes an accompanying video which explains the code should you need additional help understanding this solution.

Source: Creating a Settings Section in Google Sheets Apps Script Projects

Creating your on preview ink Smart Chips in Google Docs: The Untold Potential of Apps Script


Alright, folks, we’re talking about something quite hot off the press in the realm of Google Apps Script — “Smart Chips.” This feature is available within Google Docs and can build previews of pasted links using the Card Service. Think of it as a little preview window of what lies beyond the link — a sneak peek, if you will. 🕵️‍♂️

Editor: Smart chips are a recent feature in Google Workspace that help you quickly insert information into your Docs and Sheets. They can be used to insert people, places, dates, and more. It’s also possible for Google Workspace developers to publish their on ‘Preview links’ smart chips as Workspace Add-ons. In this post from Dmitry Kostyuk you can learn about publishing your own smart chips and some creative ways to get the data you need using Google Apps Script.

Source: Previewing Links with Smart Chips: The Untold Potential of Apps Script

Retrieving and putting values for PDF forms with Google Apps Script (and other PDF solutions)

This is a sample script for retrieving and putting values for PDF Forms using Google Apps Script.

We’ve featured a couple of posts from Kanshi Tanaike on Pulse with solutions for handling/manipulating PDF Documents with Google Apps Script. This post on retrieving values from PDF Documents is just one in a series of recent contributions from Kanshi looking at how Google Apps Script can be used with PDFs:

All these examples use the PDF-LIB JavaScript library and as previously noted in the Pulse post Merging multiple PDF files as a single PDF and converting all the pages in a PDF to PNG images using Google Apps Script , with minor modification to can load this library into the Apps Script editor.

Source: Retrieving and Putting Values for PDF Forms using Google Apps Script

How to transcribe audio and video files from Google Drive with Google Apps Script

Learn how to automatically transcribe audio and video files in Gmail messages with the help of OpenAI speech recognition API and Google Apps Script

Amit Agarwal highlights a no-code solution for transcribing audio in Gmail attachments available in his ‘Save Gmail to Google Drive’ Google Sheets Add-on. As part of this he shares the code and tips for transcribing audio files using OpenAI’s Whisper API. When you look at the snippet provided you’ll see once the audio file is in Google Drive it is very easy to use the Whisper API to get a transcript.

Text-to-speech services aren’t new, but approaches and larger training datasets are improving accuracy and if you prefer to work in the Google ecosystem you can find out more about Cloud Speech-to-Text.

Source: How to Transcribe Audio and Video Attachments in Gmail – Digital Inspiration

Auto close Google Form after X responses with Google Apps Script

The following Google Apps Script is designed to automatically close a Google Form once it has reached the number of responses you specify.

Use Apps Script code to automatically close a Google Form

Use Apps Script code to automatically close a Google Form

Do you ever create a Google Form and then forget to close it? Or maybe you want to limit the number of responses that a form can receive? If so, you can automatically close a Google Form once it has reached the number of responses you specify with this example Google Apps Script.

Source: The Gift of Script: Auto close Google Form after X responses

Handling Google Forms checkbox responses with Google Apps Script

Get Google Form responses for checkbox-type questions and perform further actions depending on their values.

Get all checkbox responses from a Form

Get all checkbox responses from a Form

The following Google Apps Script is an example of one way to get the responses from a Checkbox-type question on a Google Form and how you might go about differentiating them.

This came up for a project I was working on where I need to put a Yes/No value into 3 separate Google Sheet cells based on 3 options in a question. The slight challenge is that all of the responses come out as a single array for this question, containing the strings of the values that have been ticked only.

Source: The Gift of Script: Google Form Checkbox responses