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 –


  • 🌐 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 IFTTT, Zapier, 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 ( 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 , which you can read more about in Google’s best practices documentation.

Update: I’ve replicated this benchmark (smaller dataset) with 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)

How to create slot booking system using Google Apps Script and Google Calendar

Google Apps Scripts is incredibly powerful and enables complex systems to be built on top of Google Apps. It can be a great choice when you need to quickly prototype an idea or design a solution that’s customizable by non-technical users.

In this article, I will walk through a simple example of building a “Slot Booking System” using Google Sheets, Google Calendar, HTML, Tailwind CSS and Google Apps Script.

Nice little Apps Script project shared in this post using Google Calendar and a published web app, making it possibly a nice starter project for something bigger.

Source: How to create slot booking system using Google Apps Script and Google Calendar

Google Apps Script library for parsing HTML form objects and adding the values to a Google Sheet

This is a Google Apps Script library for parsing the form object from HTML form and appending the submitted values to the Spreadsheet.

A common Google Apps Script use case is taking data from a webform and adding it to a Google Sheet. This can sometimes be quite painful has you have to handle the various input types and also write the data to the correct columns. The HtmlFormApp library makes this very straight forward and to illustrate once the library is added to your Apps Script project you can start appending data in a couple of lines of code:

// These are all options.
const obj = {
formData: formData,
spreadsheetId: "###",
sheetName: "###",
sheetId: "###",
folderId: "###",
headerConversion: {"header value of Spreadsheet": "name of HTML input tag",,,},
ignoreHeader: true,
choiceFormat: true,
delimiterOfMultipleAnswers: "\n",
valueAsRaw: true
const res = HtmlFormApp.appendFormData(obj);

Click through to the source link for more details 👇

Source: GitHub – tanaikech/HtmlFormApp: This is a Google Apps Script library for parsing the form object from HTML form and appending the submitted values to the Spreadsheet.

[Fixed] Google Apps Script Web App HTML form file-input fields not in blob compatible format

Image by Tumisu from Pixabay

HTML form file-input fields are not converted to compatible blob format when submitted. They are left in application/octet-stream format. The file-input fields are passed as application/octet-stream, so DriveApp.createFile(formBlob) does not work correctly … So the functionality provided by HtmlService is not consistent with the documentation

There was an annoying little Google Apps Script issue which meant when handling file uploads to published Web Apps some additional coding was required rather than just sending the file blob server side. Thankfully the open issue ticket for this has recently been marked and confirmed fixed.

Source: HTML form file-input fields not in blob compatible format

Letting users run a Google Apps Script on Google Sheets without authorizing scopes and showing the source code

This is a sample workaround for letting users running Google Apps Script on Google Spreadsheet without both authorizing the scopes and showing the script.

The post highlights the use of the existing =IMPORTML() Google Sheets function and a publish webapp to run a Google Apps Script without the end user having to authorize the script. A limitation of this approach, and also important warning to keep in mind, is the deployed web app needs to be deployed to run as the script owner and by anyone who has the web app link.

Source: Letting Users Running Google Apps Script on Google Spreadsheet without both Authorizing Scopes and Showing Script

Keep a Journal of Special Moments using Twilio Programmable SMS and Google Sheets

Learn how to build a Google Sheet journal that accepts new entries via text message. My toddler recently turned two and a half and my second daughter was born three weeks ago, so special moments are abundant but the ability to remember them is severely compromised. His suggestion of recording a memory every day in an Excel sheet or leather bound journal resonated with me, but after a few days of trying to find time to open up Excel and write a memory I realized I needed a more lightweight solution. … Twilio makes it easy and cheap to set up a dedicated phone number that can record those text messages to a Google sheet.

Nice example on the Twilio blog on using their service with Google Sheets. There is little setup required but this can all be achieved in 4 lines of code. Unfortunately, sending/receiving SMS messages with Twilio is not free, but it might be a service you keep in mind for Apps Script projects.

Source: Keep a Journal of Special Moments using Twilio Programmable SMS and Google Sheets

Creating a web app with Google Sheets

A wonderful set of video tutorials showing you how to set up a web app and connecting it to a Google Sheet. It starts off with a simple form and covers areas like getting data from a sheet and sending it back and using Materialize CSS for to easily create a good looking app. Plus, it also looks at connecting a Google Calendar to the app and how to create multiple pages in the web app.

Using Google Apps Script as a Webhook

Google Apps Script projects can be published as web apps …. This is helpful if you’re building a tool with a simple UI (using HTMLService), but they can also be used as webhooks to do work within a user account.

Google provides the option of service accounts, so why use webhooks?

For my project, it was because I needed to send calendar invites to other people. Service accounts are generally the way to handle those kinds of jobs, but it needed to have full account delegation, which means it can act on behalf of any user in the domain. That wasn’t an option in this case, so a webhook became the next best option.

Brian Bennett highlights the benefits of using ContentService and Web Apps as a way to let you interface other services.

Source: Using Google Apps Script as a Webhook