AppsScriptPulse

Modern Angular in Google Workspace Editor Add-ons

I intend this post for established Add-on developers who want to use Angular in Google Workspace editor Add-ons and Google Apps Script webapps. This does not attempt to show how to create Add-ons or Angular apps, but the tooling and build process to use Angular apps in Add-ons. I am also assuming familiarity with installing and using CLI tools in your development workflow.

We recently had the author of this post, Spencer Easton, on an episode of Totally Unscripted to talk about this topic. This companion post provides an overview of using Angular for Add-on development. There is a cost associated with this solution has separate hosting is required for the Webpacks, but as covered by Spencer the cost is low (for a 100K+ users add-on the cost is $8/month).

Source: Modern Angular in Google Workspace Editor Add-ons

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’

Maintain Google Apps Script in an Enterprise environment – Google Apps Script deployment with Cloud Build

In this article I will showcase the way I deploy Google Apps Script code in an Enterprise environment. I will share the actual build file and the necessary steps and tools to reproduce my workflow. This approach does require Google Cloud Platform knowledge and a credit card. The instructions are high level, this article is not a codelab.

For those interested in scaling up their Google Apps Script development Jasper Duizendstra outlines how you can use Google Cloud Build for version control and Continuous Deployment (CD). As Jasper notes:

When the CD pipeline is in place it becomes easy to deploy the script, separate the responsibilities and support multiple versions and implementations of the code … the most important advantage of using CD is the mindset that it enables. It provides an environment where I feel confident and safe to create small incremental changes in my applications.

Source: Google Apps Script deployment with Cloud Build

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

Use Google Cloud AI to find the right answers from the Calendar TV contest

I have programmed an application that searches for (or rather knows) the correct answers to the competition questions from the Kalendárium program, all using Google Cloud AI. I decided to write the entire application in Google Apps Script technology, which offers an online IDE editor, application operation in the cloud and the possibility of a quick connection to Google services. [auto-translated]

You’re going to have to hit Google Translate to read this post from Ivan Kutil, but it’s worth it is a nice example of how Google Apps Script can be used for orchestration. All the details are in the post and it’s also a nice reminder of the Parser library Ivan released in 2016, which makes it easy to scrape data from websites using UrlFetchApp (read more about the web scraping).

Source: Využití Google Cloud AI pro hledání správných odpovědí z televizní soutěže Kalendárium

Everything you ever wanted to know about Gmail draft inline images and Google Apps Script but were afraid to ask

Image order in Gmail draft

A common workflow solution in Google Apps Script is to use a Gmail draft message as a template for sending emails. For the user the benefit is they can draft a message in a familiar environment adding formatting without having to worry about writing HTML. A problem often encountered is the inclusion of inline images. This post looks at solving the issue of missing inline images.

Source: Everything you ever wanted to know about Gmail draft inline images and Google Apps Script but were afraid to ask – MASHe

Learn how to build an image library with Google Drive and a little Google Apps Script

With Google Drive, Apps Script, Bootstrap, and a little jQuery, you can develop an Image Library web application with search and download functionality!

Useful little tutorial if you are interested in learning how to interact with Google Drive files. The post is also a nice introduction into using the Bootstrap framework for building responsive web apps.

Source: Image Library

Bulk create Google Calendar events with optional Google Meet from a Google Sheet

Bulk create Google Calendar events with optional video conferencing (Google Meet) all from a Google Sheet.

Google Sheet columns allow for event details to be added

Google Sheet columns allow for event details to be added

Features of the tool

  • Performs an initial check that you have access to the provided Calendar to create events on.
  • Allows for events to be created on another Calendar that you have suitable access to (not just your own).
  • Uses toast popups to inform you of the progress as each creates each event per row.
  • Fast and efficient for creating a large number of events in one go.
  • Will not duplicate events if re-run, so you can continue to append further if you wish.
  • Provides a direct link to the created event from within the Google Sheet for easy access.
  • Performs a check of any missing ‘required’ information and informs the user via a popup so they can resolve this.
  • Includes ‘Log’ sheet to help output any error messages.
  • Has a ‘Reset’ option in the menu bar to remove all entered data and start from scratch.
  • Replicates 90%+ of the settings you can adjust when directly creating an event in Google Calendar.

Source: The Gift of Script: Bulk create Google Calendar events with optional Google Meet

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

[Solved] Inline images break when embedded from the htmlBody of an existing Gmail draft

Handy snippet from Andras Sztrokay to handle inline images when using Gmail draft messages as part of your script projects. As highlighted by Romain Vialard in the Google Apps Script community:

Google added the ability to easily retrieve the inline images in an existing draft via the getAttachments() method on October 30, 2018 (see Apps Script release notes)

Source: Inline images break when embedded from the htmlBody of an existing draft [GmailApp] [Apps Script]

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

Subscribe to Apps Script Pulse...