AppsScriptPulse

Snippet, Beginner, Docs, Drive

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

Tags:
| | 0 Comments |

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

Solutions, Sheets, Beginner, No Code

The complete guide to Smart Chips in Google Sheets

Tags:
| | 0 Comments |

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

Solutions, Beginner

Post and retrieve messages on Bluesky Social with Google Apps Script

Tags:
| | 0 Comments |

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

Snippet, Beginner, Forms

Manage Google Form onFormSubmit script executions with Script Lock

Tags:
| |

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

Solutions, AI, Forms, Sheets, Beginner

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

Tags:
| | 0 Comments |

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

Solutions, Sheets, Beginner

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

Tags:
| | 0 Comments |

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

Beginner, Sheets

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

Tags:
| | 0 Comments |

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

Snippet, Solutions, Sheets, Beginner

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

Tags:
| | 0 Comments |

 

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 📊

Snippet, Beginner, Sheets

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

Tags:
| | 0 Comments |

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

Snippet, Solutions, Sheets, Beginner

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

Tags:
| | 0 Comments |

 

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