AppsScriptPulse

Essential Apps Script: a Practical Guide from the University of York

Welcome to the Essential Apps Script course. This guide is designed so you can work through an introductory course in Google Apps Script, designed so you do not need any knowledge of coding to get started.

As part of our latest episode of Totally Unscripted we chatted to Phil Bainbridge about the work he does at the University of York supporting and creating Google Apps Script projects.

One of the resources the University has developed is the Essential Apps Script course which is designed to lead beginners through the first steps in learning Google Apps Script. The guide is shared under a Creative Commons CC-BY-NC-SA which allows anyone to reuse course materials for non-commercial use if shared with the same licence and attributing the University of York.

This course is a perfect starting point for both beginners and those tasked with helping others learn Google Apps Script. Its step-by-step approach and beginner-friendly language make it easy for anyone with no prior coding experience to grasp the fundamentals. For those supporting others, the course provides a clear and structured resource to guide colleagues through the process, saving valuable time and ensuring everyone is on the same page from the beginning.

Source: Subject Guides: Essential Apps Script: a Practical Guide: Essential Apps Script

Replace text in a Google Doc with an image from Google Drive with Google Apps Script

Search the body of a Google Doc for a specific string/pattern and insert an image in place of it.

In this example the code is designed to sit behind the Google Doc so it is bound to it. There are 4 pieces of information to complete in order to setup the script:

  1. searchText – this is the unique string/pattern in the Doc that you want to replace with an image e.g. “<<keyword>>
  2. imageURL – this is the direct link to the image in Google Drive that you wish to use in the Doc.
  3. size – a numerical value representing the number of pixels for the image’s width/height.
  4. hyperlinkURL – if you want the image to be clickable then provide a link for it.

Source: The Gift of Script: Replace text in a Google Doc with an image

The complete guide to Smart Chips in Google Sheets

Image credit: Ben Collins

Learn how to use Smart Chips in Google Sheets to take full control of your data. Smart chips bring extra information to your Sheets.

Unleash the hidden power of Google Sheets with Smart Chips! Here’s a nice primer for our next episode of Totally Unscripted from Google Sheets magician, Ben Collins. You’ll have to tune in to the show to get the developer angle on Smart Chips, this post instead focusing on out-of-the-box features of Smart Chips in Google Sheets. Ben’s post is still incredible useful and in particular it was very interesting to read about data extraction from Smart Chips including the dot syntax when using Google Sheets formula/functions.

Follow the source link to find out more!

Source: The Complete Guide to Smart Chips in Google Sheets

Post and retrieve messages on Bluesky Social with Google Apps Script

Use Google Apps Script to automate actions on Bluesky Social using their API.

I’ve largely given up on X (formerly Twitter) and there are now numerous alternatives including Bluesky Social. If you would like to automate posting and getting messages from Bluesky Stéphane Giron provides a guide on how to communicate with Bluesky Social API using Google Apps Script. It includes detailed steps on how to obtain an app password, authenticate with Bluesky, retrieve messages for a user, and post a new message. The post also includes code snippets for each step, making it easy if you want to modify this integration to your own needs.

Source: Post and Get messages with Bluesky Social API and Google Apps Script

Manage Google Form onFormSubmit script executions with Script Lock

Use the Apps Script Lock Service to control Form submissions and prevent data loss

Lock Service code snippet

Lock Service code snippet

The following Google Apps Script is a one example of how the Lock Service can be used to prevent concurrent running of code. Here we have a Google Form that can be submitted by users at any point, the code then takes some of those details and appends them to another Google Sheet row. In normal circumstances this will happen relatively quickly and without clashes, but what if multiple people submit the Form at the same time!?

[Editor note: An alternative approach to tryLock() is waitLock(). The only different with a waitLock() is it will throw an exception after the set number of milliseconds. An example of waitLock() with onFormSubmit is included in the reference documentation]

Source: The Gift of Script: Control Form submissions with Script Lock

How you can use Google Forms, AI, and Apps Script automation to analyze 1,700 survey responses (and the 1,000th AppsScriptPulse post)

This post describes how I designed and ran an audience survey with over 1,700 responses, using Google Forms, Sheets, Apps Script, and ChatGPT. I’ll show you the entire process from end-to-end, including how I:

  • Created a survey with Google Forms
  • Used Apps Script to automatically say thank you to 1,700 respondents
  • Analyzed the response data in Google Sheets
  • Used AI to help me understand the qualitative data
  • Presented the results in Google Docs

It’s rather fitting that the 1,000th Pulse post features content by the one and only Ben Collins! Back in late 2019, when I was thinking about creating a new community site for Google Workspace developers, Ben’s encouragement was the spark that ignited AppsScriptPulse.

And today’s post by Ben is a nice example of Apps Script’s power to automate repetitive tasks. As part of this he shows how to craft personalised “thank you” emails for Google Form survey response with Google Apps Script. Ben’s insights go beyond ‘thank-you’s as he outlines how he administers and analyses customer surveys, highlighting his design choices for Google Forms and data analysis using built-in Google Sheets functions.

To take things a step further, Ben also highlights how he used ChatGPT to categorize qualitative survey responses. With Google’s recent announcement of their new AI model, Gemini, which outperforms ChatGPT  in a number of academic benchmarks, it would be interesting to see how these two platforms compare for this type of analysis.

Raising a glass (or an espresso :) to Ben and this 1,000-post milestone!

Source: How To Analyze Google Forms Survey Data with AI and Apps Script

Google Sheets [🔧Fixed]. Prevent users from deleting formulas with Google Apps Script

You’ve made a formula, but someone deleted it. It may be annoying to restore your formula. You are not always able to protect a range with formulas, as protection will also forbid users from sorting range, adding new rows, hiding rows, etc.

Let’s create our formula protection with the help of a few formulas and app script code.

Google Sheets are fantastic for collaboration, the downside however can be that other people can break stuff. The Protected Sheets and Ranges can help with this, but there might be times when you need an alternative solution. If you find yourself in this situation Max Makhrov has come to the rescue with a solution to rewrite formula if they are accidentally deleted. You can find out more in the source post link. As a bonus Max includes a named Google Sheets function, FormulasMap, which can be used to export functions in a range to another sheet.

Image credit:
Max Makhrov

 

Source: Google Sheets [🔧Fixed]. Prevent users from deleting formulas

Handling date objects between Google Sheets with different timezones using Google Apps Script

This is a sample script for copying the date object between Google Spreadsheets with the different time zones using Google Apps Script.

As I’ve previously mentioned working with dates, times and time zones can often be a bit of a headache. If you’d like to learn more about some of the challenges of dealing with ‘big balls of wibbly-wobbly, timey-wimey… stuff’ I recommend watching Comptuerphile’s Problem with Time & Timezones.

This post from Kanshi Tanaike highlights a couple of approaches for handling date/time objects in Google Apps Script when you are using Google Sheets.

Source: Copy Date Object between Google Spreadsheets with Different Timezone using Google Apps Script

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