Append List Items, Paragraphs and table cell items with a date-time stamp in Google Docs using Google Apps Script

Recently, I thought it would be a cool idea to add a date-time stamp to the end of a Google Doc checklist item with Google Apps Script. So I knew when I completed a task.

I often share a project Google Doc with clients and then add my tasks to the document list. With Google’s new check box list item, I wanted to add the date and time that I completed the task when I checked the box.

The bad news is that there is no onEdit() trigger (like in Google Sheets) for the DocumentApp class that would listen for an edit of the document and see a change of the checked box from unchecked to checked and then apply the date-time stamp. 😢

All good, I settled for the next best thing! A menu item.

Take a quick look at the results.

What the Date-Time Stamp Button does

The timestamp button allows you to set your cursor anywhere on a:

  • List Item
  • Paragraph
  • Table cell item
  • Header

… and then click the ‘AddDTS’ menu item. The Google Apps Script will then run the script and your custom date time stamp will be added.

The Code

Run the script

Go to Tools > Script editor to open the Google Apps Script IDE. Copy the code above and paste it into the script editor. Ctrl + s to save.

You will probably be prompted to change the title of the script. Generally, for document bound scripts it’s good practice to give the script the same or similar name as the attached document.

Close the editor and go back to your Google Doc. Refresh the page and your menu will now reload each time with your new menu item.

The first time you run the script you will get a warning to authenticate the code.

You can find out more about this here:

Running Google Apps Script for the First time. What’s with all the Warnings!

After you have run the code for the first time, you can now click the menu item and it will add the date-time stamp to where ever you have your cursor.

Looking to learn more about Google Apps Scripts in a more structured format? Udemy has some great courses that can get you from the basics to a real Google Apps Script pro.

Got a more specific problem you need help with, but don’t have the time to develop the skills? I can connect you with a trusted freelancer. I have a team of hand-picked experts ready to help.

*The above affiliate links have been carefully researched to get you to what you specifically need. If you decide to click on one of these links it will cost you just the same as going to the site. If you decide to sign up, I just get a little pocket money to help pay for the costs of running this website.

Code breakdown

onOpen()

The onOpen() function is a Google Apps Script custom simple trigger that is run each time the Google Doc, (Slide, Form or Sheet) is opened.

For our purposes, we want to create a menu item. Menu items are part of the Google Docs user interface and also consist of dialogue boxes, sidebars, prompts and alerts. So we can use the getUi() method from the DocumentApp ClassLine 6

To build the menu item, we first call the createMenu(‘menu title’) method that takes a title as an argument and commences the build chain to develop the menu. Line 7

We can add as many sub-menu items to our main menu by using the addItem() method. For example, if you wanted to create different displays of your date-time stamp append tool you could add more here.

Each addItem() takes two arguments (Line 8):

  1. The title of the menu item: For us, this is “Add DTS”.
  2. The function to run when you click the menu item: In our case, this is “appendElement”.

Once we have added everything we wanted to our menu, we build it with the addToUi() method. Line 9

Google Docs custom menu item for appending elements using Google Apps Script

appendElement()

This is the main function that runs your date-time stamp appender.

Let’s see how it works.

Document variables

Our first task is to set up our document variables.

To get the Google Doc we are using in Google Apps Script, we call the DocumentApp class and then call the getActiveDocument() method. This will create an instance of the DocumentApp class containing all the methods that you can use to work with the document. We’ll assign this instance as ‘doc’. Line 7

Referencing our ‘doc’ instance, our next job is to get where the cursor is. We can do this with the getCursor() method. Line 8

A Google Doc is broken into elements, like paragraphs, lists, tables, pictures, headers and more. Each element can have child elements as well.

Once we have our cursor position, we can find the element that the cursor is on. This will come in handy in a moment when we append the element. Line 9

Input variables

Our next task is to add our input variables.

This is where you can start getting really creative and change what is displayed when you click the ‘Add DTS’ menu item.

In our example, we are going to add a string of text containing:

  • Today’s date.
  • Today’s time.
  • The current user’s email.

We first create a new date with JavaScript’s Date constructorLine 4

Next, let’s use the Google Apps Script Session class to grab our email. We can find the active user, a.k.a. the current user, with the getActiveUser() method that then has a child method to get the email with getEmail()Line 5

Note that all editors of your Google Doc will need to give permission for the scope that controls the Session class the first time that they click the menu button.

Now we can put our date and email together in a string. I am using template literals indicated with backticks or grave accents to store our string data because it makes it easy to insert variables into the string with the ${code} template approach.

After I indicate “Complete” at the beginning of the text string, I use the Javasript toDateString() and then toLocaleTimeString() methods on the date variable. This will provide the date and time in your local area (Or more specifically, to the date and time that you have assigned to your script). Line 6

Finally, I add my email.

You can remove any of these items that you don’t want or move them into a better order to suit your own purposes.

get element lengths

To style our appended date time stamp we will need to get the length of the element and then get the length of the element plus the newly added date-time stamp. Line 4

We then get the length of the chosen element. Line 5

We will be applying our styling to the end of the old element length through to the length of the element plus the length of the input.

Note that we will subtract 2 from this value. Why? Because if we added the value right to the end of the element and a user select the end of the element and hits ‘enter’, they will bring the formatting with them. This will make the user sad. Nobody wants a sad user. Line 6

Create text styling

There are two ways of applying styling to text elements in Google Apps Script. We could simply chain a related method like setBold(), setFontFamily() or setForegroundColor() methods to your appended text. This is an okay approach for one or two styles but will slow your code down for more. There is a better approach.

The setAttributes() allows you to set a whole bunch of styles on a particular area of text and then send them to your Google Doc in one big hit.

We will be chaining the setAttributes() method to our text append method in a moment, but first, let’s talk about how the method takes style data.

The last argument for setAttribues() is a list of attributes. These attributes are formatted in an object. Each of the objects property keys needs to come from the DocumentApp.Attribute enumerator. You can find a list of properties here:

Document App Attribute Properties

To make things easier, let’s create the attr variable and assign it to DocumentApp.Attribute. Line 4

Next, we can build our object of attributes. Lines 5-11

Note that I have used square brackets around each key to generate the property key allowing for computed property names.

Append the text and styling

Finally, we get to append the text to the chosen element.

We first grab the element and read it as a text item (Text Class). Line 4

Next, we append the inputVal text to the end of the element with the appendText() method. Line 5

Finally, we use the setAttributes() method to add our style. This method can take:

  1. A start range: {number} For us, that this is the elements original length.
  2. The end range: {number} This will be our combined length of the original element plus our appended text minus 2.
  3. The style: {Object} This is the object of style properties we created in the previous section.

Conclusion

That’s all there is to it. You can modify the style and what you write in your text to how you would like it. I would love to hear how you modified your script for your own project. It is always interesting to see.

If you have found the tutorial helpful, why not shout me a coffee? I'd really appreciate it.

Happy coding!

~Yagi

I hired a Google Apps Script developer and they delivered unreadable code. What’s going on?

A few days ago I got an update from a Google Apps Script group that I subscribe to. The poster shared a jumble unreadable single-line mess of code with a message that read something similar to:

I hired a Google Apps Script developer and they shared me this. It works, but it is unreadable. What should I do?

Obfuscated Code Google Apps Script
Google Apps Script code that has been intentionally obfuscated. Click to expand!

It looked like the poster’s code has been deliberately obfuscated or obscured to make it difficult for someone to read. The code solves the problem for the client and runs as it should. It’s just near impossible to read or edit without seeing the original source code.

Picket lines were formed in the comments section of the post and salvos ensued. It wasn’t a Reddit-level skirmish, but it was getting there before it ran out of steam.

The post and the ensuing comments did raise some good points of view that are well worth considering as a freelance Google Apps Script developer or as someone who plans to hire a developer.

Continue reading “I hired a Google Apps Script developer and they delivered unreadable code. What’s going on?”

Why Object.create() doesn’t work in Google Apps Script and how to fix it.

If you have found this post while searching to try to figure out why your JavaScript Object.create() method is being a big stupid head isn’t working how you would expect it would in Google Apps Script, you have come to the right place.

You’ve probably used Object.create() in a JavaScript project in the past to create a new object based on a template object and then added another property or so to it.

As you can see in the example above, it works just dandy in JavaScript. The original object "a" is not affected if we add a new property to the "newObj" object.

However, if you try and do the same thing in Google Apps Script, as you have no doubt discovered, you will get the following results:

So what’s going on? What can I do to fix this? Read on or select from the menu to get to the bits you are interested in.

Some Solutions

Spread Syntax to the rescue

With the modernisation of Google Apps Script with their introduction of the V8 runtime back in February of 2020, we can now use the JavaScript Spread Syntax ({...obj})to create a shallow clone of the original object (a.k.a object literal).

This is my favourite approach. It is quite neat and elegant, but perhaps not as explicit in its meaning for someone reviewing your code.

If you create a new object and add a property to it

Dam it! Now I’ve got that Beyonce song in my head. ✔💍*

Let’s fix the original code with our spread syntax.

You can see on line 5 we have a nice neat spread syntax to add to your "newObj" object. This shallow clone of "a" allows us to keep the original object unchanged while importing the properties into the new object.

Just like on line 6, we can now add new properties to our new object without fear of updating the original source object, "a".

*Just when you thought the humour couldn’t set a lower standard, the goat’s gone subterranean.

Joining two objects together in Google Apps Script

Probably the most useful part of using the spread operator is that we can join or concatenate two objects together.

In our next example, we will join objects "a" and "b" together to form one new object with our spread syntax. To do this we simply separate the two objects by a comma and use the spread syntax on both.

As you can see, "c" has now combined "a" and "b" successfully while both "a" and "b" still maintain their individuality.

Adding an object to an existing object in Google Apps Script

One final thing you might find useful is to concatenate one or more objects to an existing object. We can do this with the Object. assign() method. This method takes a target object as its first parameter and then any number of objects as its subsequent parameters.

Adding one object to another.

Let’s say if I wanted to add all of "a"‘s properties to "b". It would look a little something like this:

As you can see, object "b" now has the properties of "a", but if we change the "name" property of "b" to something else, then "a" will not be affected.

Adding multiple objects to an existing object.

Likewise, we can add multiple objects to an existing object with the Object.assign() method.  In the next example, we will make a copy of "a", "b" and "c" objects and join it to object "d".

As you have probably guessed by now, objects, "a","b" and "c" are not affected by the Object.assign() but now "d" has assimilated a copy of the properties of the other objects.


Looking to learn more about Google Apps Scripts in a more structured format? Udemy has some great courses that can get you from the basics to a real Google Apps Script pro.

Got a more specific problem you need help with, but don’t have the time to develop the skills? I can connect you with a trusted freelancer. I have a team of hand-picked experts ready to help.

*The above affiliate links have been carefully researched to get you to what you specifically need. If you decide to click on one of these links it will cost you just the same as going to the site. If you decide to sign up, I just get a little pocket money to help pay for the costs of running this website.

The video.

What’s going on?

Well, I found a relatively obscure reference to the reason why Object.create() performs how we expect it to in StackOverflow that was answered by a former legendary member of the Google Apps Script team, Corey Goldfeder.

Corey informs us that in Google Apps Script when objects are passed to Object.create() method, the properties of the original object are non-enumerable by default. This means that they cannot be explicitly iterated through with a for…in loop inside the create() method.

Corey goes on to say that Object.create() uses the Object.defineProperties() syntax which, unless the enumerable property is explicitly defined previously, will be defined as non-enumerable and not be added to the newly created object.

You could resolve or test this by explicitly defining the enumerability of each property in "a" as true which would then give you the result you were expecting.

Note the changes to the object 'a' and the parameters in the create method (Line 6).

 

That’s it for creating new objects based on existing objects in Google Apps Script. I would love to know how you used this code or if you have another approach. Feel free to share your ideas or questions in the comments below.

If you found the tutorial, useful why not shout me a cuppa.

~Yagi

How to Validate Specific Users on a Web App in Google Apps Scripts

You’ve created an awesome Google Apps Script web app for your secret society within your Google Workspace organisation or …dom! dom! DOM! … the world. The problem is that you only want to share your web app with the worthy. Those selected few. 🐐🛐🛐🛐

How do you do this? How to prevent this most coveted of apps from reaching the wrong hands?

It’s actually surprisingly simple.

In this tutorial, we will explore how to validate selected users to provide access to your web app. For our example, we validate users based on whether or not they have edit access to a Google Drive file ( a common occurrence). In the discussion, we will also look at alternative ways of validating emails.

One of the bonuses of the approach we will go through is that it can also be easily adapted for use in Google Workspace Add-ons, and Editor Add-ons like sidebars and dialogue boxes.

We’ll start off with an example and then move to a quick-use guide for those of you who just want to get in and apply the code to your own project. Then for those who want to know how it all works, I’ll dive into the details.

Let’s get started!

Continue reading “How to Validate Specific Users on a Web App in Google Apps Scripts”

List All Users in an Organisation’s Google Workspace Account with Google Apps Script

Note! This tutorial is for Google Workspace for organisations and not the free consumer account, unfortunately. 

While the Google Apps Script docs provide a great example of how to get a list of users in a Domain on a Google Workspace account, it is not in the scope of the documentation to go into the weeds and explain all the ways we can search for all users.

Weeds sound much more like the purview of a goat. A coding goat, perhaps 🐐. Me. I’m talking about me…yeesh!

In this tutorial, we will cover how to access your Google Workspace organisation’s user data, what data you can retrieve and how it looks, who can retrieve it and a couple of ways to display what you need.

This post is intended as a resource reference that compliments the Google Docs on the Admin SDK. Links to the Google documentation are provided throughout the post. It is worth a bookmark if you intend on using the Admin SDK a lot in Google Apps Script.

Use the contents page to navigate to what you need.

Continue reading “List All Users in an Organisation’s Google Workspace Account with Google Apps Script”