AppsScriptPulse

Adding an unsubscribe link to the Google Workspace Solution Gallery ‘Mail merge using Gmail and Google Sheets’

Recently I came across a nice post from Ravgeet Dhillon, which looked at how to Add Unsubscribe link in emails using Google Apps Script. The solution uses a Google Sheet and a Google Apps Script web app to add an unsubscribe link to emails. As I noted when shared via AppsScriptPulse this is a useful addition if your mailing needs comply with PECR or similar electronic communications regulation compliance.

Having recently updated my ‘Create a mail merge using Gmail and Google Sheets’ in the Google Workspace Solution Gallery to handle inline images I thought it would be useful to show how this example can be extended to include a variation on Ravgeet’s post.

Source: Adding an unsubscribe link to the Google Workspace Solution Gallery ‘Mail merge using Gmail and Google Sheets’

Create a custom automated Table of Contents for your Google Sheets tabs with Google Apps Script – Yagisanatode

In this tutorial, we will cover creating a custom Table of Contents that lists your Google Sheets tabs on its own tab using Google Apps Script. This Table of Contents (TOC) will update whenever you open your sheet or choose to update it with a button.

As always a very detailed explanation of how this solution works from Scott ‘Yagi’ Donald.

Source: Create a custom automated Table of Contents for your Google Sheets tabs with Google Apps Script – Yagisanatode

Creating a book recommendation engine using Google Apps Script — Thoughts from a Technologist turned School Librarian

Don’t have time to get to all your library duties? Need more staff? Create them using code! I work in a school library and could definitely do with some help around here, so let’s write some programs to streamline a few library duties and add some “virtual staff”.

In this post, we’ll create a small tool to help get word out to the school community about book recommendations and new releases/purchases in the library. The plan is to make use of a Google Sheet to pre-populate a bunch of data as time goes on and then have a Google Apps Script pull that data periodically, display it on our digital displays using Google Slides as well as to send email updates to the community.

Nice example for creating Google Slides from Google Sheets for digital signage. The post has a useful explanation of what is going on in the code for those interested to learn more.

Source: Creating a book recommendation engine using Google Apps Script — Thoughts from a Technologist turned School Librarian

Add unsubscribe link in emails using Google Apps Script

Provide your subscribers an option to opt-out of mailing lists by adding unsubscribe link using Google Apps Script.

A useful post on creating a system to allow users to indicate they wish to opt-out of further mailings (useful for PECR or similar electronic communications regulation compliance). The post has the broad steps and code needed for this solution and you will need to fill in some of the blanks like how to record the unsubscribe_hash in the Google Sheet.

Source: Add Unsubscribe link in emails using Google Apps Script

Tweet formula for Google Sheets – Twitter Developer Blog

The new Twitter API brings a much better way to get a list of publicly available Tweets in Google Sheets to retrieve Tweets and their details, including the username of the author.

Always nice to see when other web services feature Google Apps Script. This is a great example from Daniele Bernardi (‎@i_am_daniele‎), DevRel at Twitter, highlighting how Google Sheets custom functions can be used to interact with the Twitter API. The post has everything you need to know plus a link to a gist with all the code.

Source: The Tweet formula for Google Sheets

Create custom prefilled Google Forms links in custom emails with Google Apps Script – Yagisanatode

Recently I raised a support ticket with a tech company I was subscribed to where we were trying to resolve an integration issue I had with their service. Once we had it all resolved they followed up with a feedback form. That feedback form just happened to be a Google Form.

In this tutorial, I’ll walk you through accessing the prefill tool in Google Forms. Then, if you are keen on doing some coding, we’ll create a little custom feedback form for unique users that we will deliver via email.

Nice tutorial from Scott ‘Yagi’ Donald on using prefilled Google Form links. For beginners the post includes everything you need to know. For more experienced developers Scott includes some nice code for handling Google Sheet data.

Source: Create custom prefilled Google Forms links in custom emails with Google Apps Script – Yagisanatode

Tracking Email Opens with Gmail, Sheets, and Apps Script

This walkthrough will let you build a tracker for email opens using Gmail, Google Sheet, and Google Apps Script.

Interesting solution that popped up in the Google Apps Script community for logging email opens using Google Apps Script. The post includes a detailed walkthrough for setting this up as well as some caveats about reliability.

Source: Tracking Email Opens with Gmail, Sheets, and Apps Script

Introducing LASTEDIT(), a Timestamp Formula for Google Sheets

“When was the last time this sheet was uploaded?” Probably one of the biggest requests I get is how do I add a timestamp to check when some particular range was changed. Well, [here] is a simple function you can add to your Google Sheets doc to add the formula LASTEDIT()

The question of recording when a cell/range has been edited has landed in my inbox before. I’ve not tested this solution myself but from the scan of the script worth noting that this solution will only work on one cell array per Google Sheet, so some modification might be required if you have more than one range you’d like to monitor.

Source: Introducing LASTEDIT(), a Timestamp Formula for Google Sheets

Create a Zoom meeting via the API using Google Apps Script

Create a meeting in Zoom via the API and OAuth, using Google Apps Script and a Web App. The details for the meeting will be collected from a Google Sheet.

Zoom meeting details in a Google Sheet

Zoom meeting details in a Google Sheet

Source: The Gift of Script: Create a Zoom meeting via the API

Setting Alternate Background Colors for Rows in Google Spreadsheet using Google Apps Script

This is a sample script for setting alternate background colors for rows in Google Spreadsheet using Google Apps Script.

It has already been known when the conditional formatting rule and custom function are used, this can be simply achieved. In this report, I would like to introduce the method for using Google Apps Script.

Source: Setting Alternate Background Colors for Rows in Google Spreadsheet using Google Apps Script

Get your Zoom meeting settings via the API and storing in a Google Sheet using Google Apps Script

Get your Zoom meeting settings via the Zoom API and OAuth, using a Web App in Google Apps Script. Here we will access user settings in your Zoom account, specifically the meeting settings, and log the results in a Google Sheet. This provides both a way of confirming we have made a successful connection and will allow us to create subsequent Zoom meetings using these settings.

This will log the results in a Google Sheet to help you understand what is happening and the type of data that is returned.

Source: The Gift of Script: Get your Zoom meeting settings via the API

Format specific word inside a Google Sheet cell using Google Apps Script

Format (bold) a single word or a group of words automatically in sheets, using google apps script.

This example from Sourabh Choraria is a nice example of how you can apply text formatting to parts of Google Sheet cell values. Reading Sourabh post got me thinking if having an Add-on or built in feature that used basic markdown for cell formatting would be useful? e.g. typing `I just love **bold text**` would result in the cell being displayed as `I just love bold text`.

Source: format specific word inside a google sheet cell using apps script

How to send Slack alerts from Google Sheets / Google Apps Script

We run a lot of things through Google Sheets and Slack here at August. It’s a way of connecting and sharing information quickly and transparently with our clients and our team. We’ve put together a cheat sheet of a few simple Google Apps Script functions, along with the Slack API, so you can do the same.

Great post if you are looking for a quick start for interacting with Slack using Google Apps Script. As well as a detailed ‘how-to’ the post also includes a video walkthrough to help if you get stuck with any of the steps.

Source: How to send Slack alerts from Google Sheets / Google Apps Script. | August

When the Best Google Sheet is the One You Don’t Make

How to build a more perfect workflow for data updates and changes by coding in Apps Script between the Sheets

Thought provoking post by Max Brawer exploring the utility and fragility of using spreadsheets as part of a decision making processes and how Apps Script can be used to overcome some of these shortcomings.

Source: When the Best Google Sheet is the One You Don’t Make

How to write a custom function library for Google Sheets

In this article I will showcase one of the many ways you can customize your Google Workspace environment. We will implement two custom functions in Google Sheets. With these functions as an example I will demonstrate how to use Google Apps Script Libraries to organise and distribute your solution. There are multiple ways to distribute your Apps Script code within the Google Workspace, and this article will help you decide if this library method is right for you.

Always nice to have some advanced techniques in your toolbox. In this example from Jasper Duizendstra he explores how custom functions in Google Sheets can be packaged and used in Libraries.
Source: How to write a custom function library for Google Sheets

Subscribe to Apps Script Pulse...