Tag: Google apps script

Add the Editor’s Email when they Tick the Check Box in Google Sheets with Apps Script

This tutorial is for Google Workspace Domain accounts.

Recently, I was a guest interviewee at a Google Workspace Developer Summit in Singapore and an attendee asked me how they could automatically add the editor’s email to an adjacent cell when the user checks a check box in Google Sheets.

This can be incredibly handy for project managers to see when a user completes a task or, at a glance, who edited a row.

Of course, there are some simple ways of doing this out-of-the-box within Google Sheets.

  1. A simple right-click > Show edit history on any cell can reveal who has edited the cell in the past.
    Show cell history in Google Sheets
  2. If you want a more detailed history of edits on your Google Sheet workbook then you can always select the version history button in the top right of your Sheet.

    Google Sheets Version History
    The ‘clock’ icon here opens the version history page.

The problem with these options is that it is not there on the screen for the user to quickly see who edited what line.

In this tutorial, we are going to use some Google Apps Script magic to automatically add the editor’s email to the row when they click that checkbox.

Let’s dive in!

Continue reading “Add the Editor’s Email when they Tick the Check Box in Google Sheets with Apps Script”

Creating Webhooks for Google Chat App with Apps Script

In this tutorial, we follow the adventures of Captain Webhook and his crew in their insatiable desire to report their latest booty conquest to me via Google Chat webhooks…

That, dear readers, did not come out right.

Webhooks are HTTP POST requests that are usually generated by a triggered event. The event could come from any third-party source within Google Workspace like:

Or it can occur with other third-party services. Imagine that you want to get an update from:

  • Your Stripe or PayPal account whenever a new payment comes in.
  • A course management service like Gumroad.
  • Patreon when another awesome supporter shows you some love.

Or when a rather rambunctious figment of my imagination insists on updating me when his latest haul of treasure has come in… live.

Sigh.

Captain Webhook of the Pirate Sheet HTTPS Request

Chat App webhooks will need an intermediary step for them to be compiled in a way that the Google Chat API can understand. We’ve chosen Google Apps Script here to do this, but you can choose to use any other language to convert your data into Chat API readable JSON or even build in a CLI to post your webhook request.

In this tutorial, we will cover how to:

Continue reading “Creating Webhooks for Google Chat App with Apps Script”

Add the Current Date to a Sheet When Data Is Added So That The Date Does Not Change(Static) – Google Sheets (Updated July 2023)

Google Sheets, Google Apps Script: onEdit

You’ve probably come across the problem where you need to know when a piece of data has been added to your spreadsheet. You probably have been equally frustrated that there is no out-of-the-box function that will do just this.

You’ve tried TODAY() and NOW(), but they change dynamically. What you really need here is something that does not change.

Let’s look at two workarounds that can help you out with this problem.

Continue reading “Add the Current Date to a Sheet When Data Is Added So That The Date Does Not Change(Static) – Google Sheets (Updated July 2023)”

List all files and folders in a selected folder’s directory tree in Google Drive: Apps Script

If you have ever tried to get a list of all the child files and folders of a parent folder in Google Drive, you’ve no doubt discovered that it is a slow old process. Iterating over each item in a folder and then reading the metadata of that file or folder before calling the next one can take forever!

The built-in DriveApp Class for Google Apps Script is great for working on a small number of files and folders but it just doesn’t have the functionality to retrieve specific fields in your metadata, nor does the searchFiles method or searchFolders method have the ability to isolate just the fields that you want to retrieve. Subsequently, your processing time increases significantly as it ships junk data.

This tutorial is my attempt at, as the kiddies say today, creating a ‘blazingly fast’  file and folder iterator with Google’s Drive API v2 for Apps Script.

Continue reading “List all files and folders in a selected folder’s directory tree in Google Drive: Apps Script”

Creating an embedded interactive Chain Story app with Google Apps Script and Google Sheets

Google Apps Script: WebApp, HtmlService, LockService; Google Sheets

In this tutorial, we are going to create an interactive story chain app that we can embed into a Google Site or your own site like WordPress.

What’s a chain story, Yagi? 

Maybe you did this in school. Someone wrote the first part of a story. You then gave that story to someone else to continue writing. They then pass the story on to someone else to write the next part. And so on and so forth. In the end, the story is read out and everyone laughs at the direction the story went – except that one kid silently raging over their lack of control of the narrative.

Why are we making this? How’s it going to help me?

Well, for one, I thought it would be fun. More importantly, this will allow us to have a look at how Google Apps Scripts communicates client to server-side and vice versa in a little more advanced environment than our previous tutorial. It will also give us an opportunity to look at some more parts of Google Apps Script as they relate to creating a WebApp.

Our chain story WebApp tutorial will also give us an opportunity to look at some of the pitfalls of using WebaApp. Particularly when using the execute as me permissions. Finally, this will then launch us into our follow-up tutorial on updating the WebApp to execute as the user rather than me, the owner of the app.

This tutorial is the second part of the WebApp series. However, if you can read a bit of JS, CSS and HTML, you should be able to follow along and if you get stuck you can always go back to the first tutorial:

Google Apps Script: How to create a basic interactive interface with Web Apps

Let’s get started…

The Example: An interactive chain story

Embedded below is our interactive Chain Story web app. If you are feeling creative, read the story so far and then add your part to the story. It has been written by readers just like you:

Continue reading “Creating an embedded interactive Chain Story app with Google Apps Script and Google Sheets”