AppsScriptPulse

Creating dining reservation system with Google Apps Script

Google Apps Script automates tasks (even offline) and builds web apps using spreadsheets as databases. This report presents a basic dining reservation system to illustrate key aspects of web app development with Apps Script, HTML, and Javascript.

One of my first Google Apps Script projects was an event booking system. It was a great way to learn about integrating with the various services like Google Calendar. Roll forward 14 years and it is a topic that Kanshi Tanaike has revisited with a dining reservation system built on Google Sheets with a web app frontend.

All the code is published on GitHub and even if you don’t need a reservation system a great opportunity to see how one can be coded and there could be lots of juicy little snippets that you can use in your own projects.

Source: Creating Dining Reservation System using Google Apps Script

Apps Script: solutions to the issue “installable triggers failing when created from another trigger function in V8”

There’s a long standing issue with triggers installed programmatically by other triggers, that was kind of fixed last year, but users are still reporting problems, at least in certain locations (Japan, India and others). The issue in short: triggers installed programmatically would not be created, or fail systematically.

In many cases this affects add-ons (and, to be noted, the issue still persists when testing them, see Testing details); I had a slightly different scenario: a webapp that, when executed (doGet), would install a time based trigger for the user executing the script.

This article provides two solutions to an annoying issue and unlocks many possibilities for programmatically installed triggers.

Source: Apps Script: solutions to the issue “installable triggers failing when created from another trigger function in V8”

Report: Implementing a pseudo 2FA for Web Apps using Google Apps Script

In Google Apps Script, there is the Web Apps. When Web Apps is used, the users can execute Google Apps Script using HTML and Javascript. This can be applied to various applications. When the Web Apps is deployed with “Anyone”, anyone can access the Web Apps. And, there is the case that Web Apps deployed with “Anyone” is required to be used. Under this condition, when 2 Factor Authentication (2FA) can be implemented, it is considered that the security can be higher and it leads to giving various directions for the applications using Web Apps. In this report, I would like to introduce the method for implementing the pseud 2FA for Web Apps deployed with “Anyone” using Google Apps Script.

There can be scenarios where you’d like to publish an Apps Script web app with ‘anyone can access’, but still provide a level of security. Here’s a nice example from Kanshi Tanaike where they use MailApp to email the user a a time limited random passcode.

Magic links and passwordless login are used by a number of services, including Slack, and rather than including a password, as demonstrated in this example, it wouldn’t take much to turn this solution into a passwordless app. There are risks associated with email based authentication and if an attacker already has access to your email so other solutions are worth considering depending on the sensitivity of your web app.

Source: Report: Implementing Pseudo 2FA for Web Apps utanaikech.github.iosing Google Apps Script

AppSheet/PayPal integrated payment solutions and tips on handling data in Google Sheets

In this post I’ll provide an overview of how PayPal was integrated into AppSheet with the help of Google Apps Script. Even if you are not interested in payment integrations this post also hopefully pulls together useful tips, best practices and code patterns for reading/writing data to Google Sheets.

Hopefully this post illustrates not only just a method for integrating a PayPal payment processor into an AppSheet app, but also a method which can generally be used to extend AppSheet functionality with Google Apps Script powered Web Apps.

As a bonus you also get some of my top tips for interacting with data in Google Sheets including efficiently reading/writing data for multiple users without concurrent overwrites.

Source: AppSheet/PayPal integrated payment solutions and tips on handling data in Google Sheets

📅 A free and open source Google Apps Script web app to make it easy for others to schedule with you

📅 A free and open source web app to make it easy for others to schedule with you –

Features

  • 🌐 Create a unique link that others can use to book an appointment on your Google Calendar
  • 📑 Offer mulitple types of meetings, each with their own configurable timeframe and event settings
  • 📒 Place events on any calendar you have edit access to
  • 📆 Confirm availability against multiple calendars
  • 🤖 Intelligent suggestion of available free times on both your and (if accessible) the scheduling party’s calendar
  • 🔗 URL parameters to bring the user to a specific meeting type and prefill their email1
  • ⚡ Send a webhook push to integrate with IFTTTZapier, and more when an event is scheduled
  • 🌈 Configurable accent color
  • 🌙 Automatic light and dark mode
  • 💳 No premium tier. 100% free.

This Apps Script solution comes thanks to a tweet from Sourabh Choraria (@choraria) highlighting a open source project from Leo Herzog which lets to deploy a highly customisable Google Calendar appointment scheduling app. The solution uses a nice JavaScript library for handling dates/times which you might find useful to include in your own projects called Luxon.

There are some interesting approaches used in this project like checking if there is a newer version of the source code on GitHub. The Luxon library is also fetched/cached and inserted using eval() – the Mozilla MDN web docs have some notes on alternatives to eval() and in the case of Luxon as shown in this Apps Script example you can drop the library into a script file and use it in your code.

Source: GitHub – leoherzog/ScheduleQuest: 📅 A free and open source web app to make it easy for others to schedule with you

How to track link clicks in emails and more with Google Apps Script and Google Sheets

Learn how to track link clicks using Google Sheets and Apps Script to create a simple, lightweight tracking system

You live and learn! This is a regular occurrence if you are a subscriber to the work of Ben Collins (benlcollins.com). No exception with this recent post highlighting the ping attribute which can be used in <a> HTML links. This attribute has been around for a long time, but I’m sure many people like have never come across it. In the source post from Ben you can learn how to setup an Apps Script web app which will let you record link clicks in a Google Sheet.

A note of caution is whilst ping is valid HTML not all browsers choose to use it or enable it by default, something Mozilla have decided to do in Firefox. You can head over to Mozilla mdn documentation for the browser compatibility table and here is also an interesting post on how Google tracks with the ping-attribute.

Source: How To Track Link Clicks With Apps Script And Google Sheets

Benchmark: Process cost for HTML Template using Google Apps Script

Image credit: Kanshi Tanaike

When we use HTML in the Google Apps Script project, in order to show the values from the Google Apps Script side, the HTML template is used. When I used the HTML template with a large value, I understood that the process cost can be reduced by devising a script. In this report, I would like to introduce the process cost of the HTML template using the benchmark.

A great feature of Google Apps Script is the ability to create and serve custom HTML, often used to interface data you have in Google Workspace such as Google Sheets. Google highlight a coupe of different ways you can mix Apps Script code and HTML. Some of these ways are better in terms of process time and this report from Kanshi Tanaike highlights the cost of calling Apps Script functions as scriptlets in HTML templates. The good news is you can avoid delays in your web app rendering by making asynchronous calls with google.script.run, which you can read more about in Google’s best practices documentation.

Update: I’ve replicated this benchmark (smaller dataset) with google.script.run and it was only marginally slower (0.3s) than the ‘create HTML table with Google Apps Script’:

Source: Benchmark: Process cost for HTML Template using Google Apps Script

Removing Google Drive file access permissions via a Google Apps Script Web App

Remove a users edit access to a Google Drive file via an Apps Script Web App.

Web App code sample

Web App code sample

The following Google Apps Script is designed to remove a person’s edit access from a Google Drive file via a Web App. The reason for using a Web App in this instance is because removing a person’s access to a file whilst they are running Apps Script code typically results in an error (or most certainly the inability to cleanly end the code). In the project I was working on prior to this step the code needs to send some automated emails before finishing with removing the person’s access.

Source: The Gift of Script: Remove File Access via a Web App

Unnest an object with array values using cartesian product [for Google Apps Script POST/GET web apps]

Convert array values from the doGet & doPost event parameters into 2D array when writing them onto a Google Sheet.

Sourabh Choraria has hit a rich patch of Google Apps Script exploration and following the post we shared recently on Google Sheets cell precedents in this latest post Sourabh looks at restructuring data into a cartesian product. The post contains some nice other ‘Easter Eggs’, in particular, worth checking out Sourabh’s latest Workspace Add-on, Webhooks for Sheets, and a snippet of code on GitHub used in the add-on for handling GET and POST requests.

Source: Unnest an object with array values using cartesian product

Creating a ‘full fat’ RSS feed for Google Gmail labels with Google Apps Script

In this post I want to cover three things. First I want to introduce a little app I’ve developed which allows you to create a RSS feed for any of your Gmail labels (with the option to remove certain links – useful if you don’t want others unsubscribing you from mailing lists). Secondly I explain how it was made and how you can use it yourself. Finally I want to discuss how this could be used in an open course environment, utilising the vast processing power from services like Twitter and reusing their target marketing emails to your benefit with a bit of ‘dark social judo’.

This solution was first published in May 2013 and since then Google Apps Script has evolved deprecating services that originally made this solution possible, in particular, ScriptDB and the original XML service which includes a handy .parseJS() method.

Following a request I’ve recently updated the solution to make it work again. As well as swapping out ScriptDB in favor of using the Properties Service I used the makeRSS method previously highlighted here in Apps Script Pulse.

Source: Creating a ‘full fat’ RSS feed for Google Gmail labels (enabling some dark social judo)