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”

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)”

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”

Creating Unique Ranges from 2D Arrays in Google Apps Script

Google Apps Script, Google Sheets, Javascript 

If you have ever done any work in Google Sheets you might have come across the incredibly useful UNIQUE function. This function can take a single row or multiple rows and generate a list of unique cell values.

UNIQUE demo Google Sheets

Sometime you might have a use case where you need to replicate the UNIQUE functionality in Google Apps Script. Maybe you are drawing in data from another source like an API, BigQuery or a database, or presenting your unique data in another format.

Whatever the case, knowing how to to create a unique array from a 1-dimensional array or a 2-dimensional array is an important part of your toolkit as a coding goat ?. This tutorial will look at how to create this with modern Javascript that is incorporated into the Google Apps Script language. And of course, we’ll focus our tutorial more on a Google Apps Script because that’s kinda our thing.

We’ll start off with a very basic example of creating a unique array from a 1-dimensional array in our code:

UNIQUE Array generated from a 1d array in GAS

…and then build ourselves up to a final example by grabbing multi-column range of Google Sheet data from which we create a unique list and then display the total sum corresponding to each unique cell value in each of our associated unique columns. Something like this:

UNIQUE 2-Array of multiple columns with result set in GAS
Click to Expand!

As we go through our examples I’ll display the runtime of the entire code. Runtime is not a perfect indicator of the performance of the script there are other factors that will influence the runtime, but it will do for our purposes. I think you will be surprised by how fast these scripts run.

Before we get started, let’s take a quick look at the sample data…

Continue reading “Creating Unique Ranges from 2D Arrays in Google Apps Script”

Google Apps Script – How to Automatically Generate a Time From a Four Digit 24 hour Time in Google Sheets.

Google Apps Script, Google Sheets

On a recent board post, a Google Sheets user wanted to change a four-digit number (for example, 1230) to a time, like 12:30, in the same cell that the item was entered.

Unfortunately, the user was not in a position to change the starting values, so they were left with the 4 digits.

There are two ways of doing this with varying levels of complexity:

 

  1. The Google Sheets Formula Approach
  2. The Google Apps Script onEdit Approach

Continue reading “Google Apps Script – How to Automatically Generate a Time From a Four Digit 24 hour Time in Google Sheets.”