Get a list of Google Shared Drives by ID and Name in Google Apps Script

If your organisation is using Google Workspace Business Standard, Business Plus, Enterprise, or one of the other supported plans, you are likely taking advantage of the power of Google’s Shared Drives.

If you have decided to create a Google Apps Script project that needs to get a list of your Shared Drive (or a user’s shared drives in the case of a WebApp), then you might be scratching your head right now wondering how to get this list using the built-in DriveApp class.

Whelp, unfortunately, at the time of writing this article the DriveApp class does not have this functionality. However, it is pretty easy to access in a single line of code using an Advance API.

Here’s what you need to do:

As always, read what you need and skip the rest. 

Continue reading “Get a list of Google Shared Drives by ID and Name in Google Apps Script”

Create a Google Workspace Add-on file picker card with CardService that opens a Google Picker in an overlay window – Google Apps Script

Google Apps Script: Card Service, Google Workspace Add-on, Google Picker

So you have this awesome idea for a Google Workspace Add-on (GWAO), but you need to be able to select Google Drive files and folders as a part of your process.

Sure, you could just open up another tab and find the link and paste it in a text input or paragraph input in your Card Service build, but that is time-consuming and kinda defeats the purpose of the convenience of the sidebar.

Ideally, you would want a built-in File Picker class that would select the files and folders from the directories you need. Whelp… unfortunately, we don’t have that right now for Google Apps Script’s Card Service.

One approach might be to build out a file picker card selecting each parent’s files and folders and navigate through it like, say, a linked list. Now, I haven’t tried this approach, but looking at how slow and memory expensive it is to call your Google Drive through either Google Apps Script’s Drive App class or the Advanced Service Drive Class, I gave this a pass… for now… .

Instead, I decided to incorporate Googles File Picker API as a popup window from the sidebar, because, it’s kinda what it is designed for. Also, not gonna lie, the example at the bottom of the docs was a huge help (cough … copy and paste … cough)

Let’s take a look at what we are going to build.

Continue reading “Create a Google Workspace Add-on file picker card with CardService that opens a Google Picker in an overlay window – Google Apps Script”

How to find and replace text in a Google Doc with a link or a list of links with Google Apps Script

So you are a citizen Google Apps Script developer and you’ve decided to make yourself a mail-merge-type project where you want to create new documents from a template. You have discovered the simplicity of the replaceText() method:

Now you want to take it to the next level and replace the text with a hyperlink containing the text and the URL. You might be scratching your head wondering where the replaceTextWithLink() method is or why you can’t simply chain the setLinkUrl() method without making a hyperlink out of the entire body of the document.

via GIPHY

What to do?

In this tutorial, I’ll cover how to find and replace text in a Google Doc with a hyperlink with Google Apps Script under three common conditions:

Danger!!! Word repetition warning ahead!

  1. Find text and replace it with new text and a link where the text is the only text in a document.
  2. Find text within a paragraph and replace it with new text and a link.
  3. Find text and replace it with a list of hyperlinks.

I encourage you to play along. Here is a link to the Google Doc without the code attached:

Test Google Doc.  

Just go to File > Make a copy to get your own copy of the Google Doc. Then Tools > Script Editor.

While you are testing, you can just use undo (ctrl + z) to return the text to its original state.

Let’s dive into the three examples.

1. Find a single item of text as a completed paragraph in a Google Doc and replace it with new text and a link

In our first example, we have a paragraph where we have just the text that we want to replace. Take a look at the image:

Find a single item of text as a completed paragraph in a Google Doc and replace it with new text and a link Apps Script v2

Here’s the code:

Once we have grabbed our body element on line 12, we set up our chain of methods to produce our hyperlink.

First, we use the findText() method to grab the text we want to find in the body. This method takes our textToFind variable as an argument and returns a range element indicating the position of the searched text. Line 14

Next, we get the element that the found range of text is in using the getElement() method. This will be a text element. Line 15

Note! You can find the type of text element by using this approach:

We then call the asText() method to get the current element as … well … um … text so that we can edit it. This allows us to perform rich text editing of the element. Line 16

Now we can set the text we want to use to replace the current text with setText(), inputting our text variable. Line 17

Finally, we add our link using setLinkUrl(). This will take our url variable as its argument. Line 18

Note that this approach will replace all the text associated with the element removing your reference search text and any other text. If you want to replace the selected text in a paragraph and add a link to it, check out the next example.

2. Find text within a paragraph and replace it and add a link.

In this example, we only want to replace the target text (and add a link) that resides inside a paragraph. Here is our example:

Find text within a paragraph and replace it with new text and a link DocApp Google Apps ScriptWe need to do three things here.

  1. We need to find the text element from which template text we want to replace resides.
  2. Get the offset where the found text starts in the overall text.
  3. Replace the text with our new text and link.

Check out the code:

Again we start off by grabbing our body on line 12. We won’t be able to chain our methods too much here because we need to get some extra information out of them. So instead we set foundText to the result of our findText() method call. Line 14

Our next task is to get the start and end locations of the text within the greater text. We can get the start location (or offset) by using the getStartOffset() method. This essentially gets how many characters in our text starts on. Line 17

We then need the location where our text will end. Now, this is not the end location of the current text. It is the location of the text that we are going to use to replace it. To calculate this, we add the startText to the length of our replacement text. We need to subtract one because the startText value is the beginning location of our text and not the character location previous. Line 18

Now we can get cracking and replace our text.

First, we grab the element (text) of our foundText. Line 21

We can then chain our next steps by setting the element to text. Line 24

This time around we can use the beloved replaceText() method to find the text again only searching inside the text element and replacing it with our desired text. Line 25

From here we can now set our link. This time around we will take advantage of setLinkUrl() method’s alternate parameter arrangement which takes:

  1. Start text index – startText
  2. End text index – endText
  3. the URL – url

This allows us to set the link at a specific location in the text.

But what if you want to add multiple hyperlinks to a list, Yagi?

3. Find text and replace it with a list of hyperlinks

In this final example, we want to add a list of links based on a text reference in the document.

Take a look at the document.

Find and replace text with list of hyperlinks DocApp Google Apps ScriptHere is the code:

The Data Source

In this example, we have an array of objects containing the title and URL for each of the links we want to add within our links variable. Lines 6 – 19

Just like in the previous two examples, you could get your data from many other sources. This is just an easy example of data to follow.

Get the paragraph element containing the text.

Before I explain this step, it is important to know that our sample text resides inside a text element that resides inside a paragraph element which will probably reside inside the body element.

Our ultimate goal is to remove the selected text and replace it with a list. If we just remove the text element, we will still be left with the paragraph, which will look like a carriage return (do kids still use that term?). So we will want to remove that whole paragraph.

This means that our first step is to get the paragraph element that contains our text.

We do this first by finding the text (Line 27). We grab the text element (Line 28). This allows us to get the element’s parent with the getParent() method. This is stored in our element variable.

Get the index of the paragraph containing the text

Here on line 32, we grab the index location of our template text. We head back to the body for this one and use the getChildIndex() on our paragraph element of the selected text. This method returns an index of the location in the body element.

The index will allow us to add our list of links in a moment.

Removed the paragraph element from the text

Now that we have the index location of where we need to add our list of links, we can safely remove our reference text.

To do this, we grab the paragraph element and use the removeFromParent() method. Line 35

Add the list of hyperlinks

Our final step is to push our list into our Google Doc at our new index location.

The text will be inserted into the new index location. This means that if we looped through our text and inserted it at the same index each time, the links will appear in the opposite order that we originally had them in our array.

The first step then is to get a reversed copy of the array before we start our loop (We get a copy because we don’t want to change the original array). This is achieved with the Javascript slice() method without any parameters, which collects the whole array. Then we use the reverse() Javascript method on it to reverse the order of the array. Now we have a copy of the array in reverse order, but we haven’t change the original array. Line 37

Now we can run our foreach() loop to iterate through each array item.

Inside each iteration of our loop, we want to use the insertListItem() method  to add our list item to the index location of our Google Doc body (Line 39). This method takes two arguments:

  1. The index location – index
  2. The text – link.text

The method then returns the newly created list item element.

Here we can then add our link using setLinkUrl().

Before we finish with our list item we can set the type of list we want by using the setGlyphType() method. The method takes a ‘list character type’ which is drawn from the Glyph Type enumerator. For our example, we set our list to be numbered.

Give it a crack yourself!

Conclusion

So that’s it. Three different scenarios for you to insert hyperlinks based on a text key in Google Docs with Google Apps Script. Of course, there is more than one way to do things. I would love to hear your approach to these problems in the comments below.

I’d also love to hear how you used these scripts in your own project. It is always inspirational.


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.

~Yagi

How to Create a Custom Email Auto-responder for a Google Forms Submission with Google Apps Script

A few days before publishing this post, I put a call out for some Beta testers to get free access to my new course Google Sheet: Learn the Essentials through 3 Detailed Projects. 

Yeah, yeah, yeah, I am super excited about finishing this course. It’s been a labour of love for two and a half months. No wonder I am banging on about it.

Anyway back to the tutorial. I needed a way to provide coupon codes for free access to the course in exchange for some feedback and much-needed tutorials.

To do this, I created a Google Form.  This contained some details and expectations, and then some details about the submitter and a consent checkbox at the end. If the submitter consented and hit submit I wanted them to get an email back with the coupon code.

In this tutorial, we will go through how to create a custom auto email response containing:

  • The submitter’s name in the greeting.
  • Your email message.
  • Your primary signature block from your Gmail account.

To me, this seems like a pretty standard task that anyone might be considering doing. So, I thought I would share the process and the code.

Let’s get cracking!

The Google Form

Here we will go over the highlights and setup as they pertain to the context of the Google Apps Script code.

The form starts off with some information about beta testing the course.

Google Forms beta testing instructions
Click to Expand!

Next, we have the input fields.

  1. Email: This is an atypical field. You create this field by going to the Settings cog and select  Collect email addresses. Here, I also sent the submitter a copy of their responses and limited the responses from each user to one. This made the user use their Gmail account, which added some extra integrity to the form and… well… the user will need a Gmail account to complete the course anyway. This will become important in our code later.
Google Forms settings collect email limit to 1 for beta testing course
Click to Expand
  1. Name: The name of the Beta tester. I’ll grab the first name from this and also record it as part of their testimonial that they consented to.
  2. Occupation (optional): This apparently helps with the credibility of the testimonial and it is also interesting to see the occupation of the people taking the course. Testers can opt-out of this one.
  3. Company (optional): This one was a fizzer. I understand why, you really don’t want to be telling people where you work online. This item was added as part of the testimonials that I researched in preparation for this. I have no idea how course creators got these, which makes me one part, a little suspicious and, one part, blown away by how these folk got students to share who they worked for. In hindsight, I would not add this item. If I personally wouldn’t give out these details, I shouldn’t expect others to.
  4. Consent: This is a mandatory field that the submitter needs to check to confirm their expectation in exchange for the free course. If they don’t check the box, they can’t submit the form and get the coupon code.

The code

We want to get our email auto-responder code to run when the Google Form is submitted to us.  To do this we need to connect our script to the form by opening the Google Apps Script editor up in the Google Form.

You can do this by clicking the vertical ellipsis () in the top-right of the Google Form editor and selecting Script editor.

Google Form access to Google Apps Script Editor
Click to expand!

Code.gs

You can copy and paste this now into your project and rename the project to whatever you are working on. I usually name my project the same as my Form or Sheet if it is directly related.

You can update the subject and htmlBody values to your own data here. You can add HTML to your htmlBody within backticks (your html) Lines 27 & 28

Of course, the coupon code is a big faker-Rooney.  🙄

The code grabs the current users email and name and then emails the respondent with a coupon code after they hit submit on the Google Form.

If you run the code right now you will face some errors. First, we need to set up the trigger to run this code when the user submits it. Also, we need to get authorisation to use scopes, or permissions to give Google Apps Script certain access to your data to run the code and add in an API to access our Gmail signature block.

Add the Gmail API

First up we need to add an advanced API to get access to be able to display the signature block from our Gmail account. You know, to make it look professional.

To add this API in the Google Apps Script editor:

  1. Ensure you are in the Editor.
  2. Select Services +
  3. Scroll down and select Gmail.
  4. Select Add
Add the Gmail API to a Google Apps Script Project
Click to Expand!

Note! As of writing this, the Gmail API only uses your primary signature block and can’t access any other signatures you might have. 

Make sure the Scopes have been Authenticated

You may find, even if you have set up the triggers, that the script doesn’t run because it is missing the permissions you need to run the script. To find out what happened you can check out the execution log in the Google Apps Script IDE  sidebar -4th one down.

Google Apps Script IDE execution log
Click to Expand!

You can then check the errors of your execution log to see what happened.

To fix this ahead of time we can modify the onFormSubmit(e) function a little to run a dummy version of the script.

Before we start, you need to have a least one response in your Google Form. Go ahead now and add in a dummy form submission. You can always delete it later.

Go back into your editor create a new Google Apps Script file and call it Test.gs.

Copy and paste in this code:

Go ahead and run the code. You should get a warning popup for authentication. This will happen just one time and it will be fine for you and anyone who enters the form in future.

Once you have accepted all the scopes your code should run and you will get an email. If you don’t, check the execution log for any errors.

You can also check to see your script scopes by going to the Overview of your Google Apps Script IDE.

Google Apps Script IDE Scopes overview
Click to Expand!

Now we can go back to our Code.gs file and add our trigger to send the email on submission.

Assign the Trigger on Submit

Next, we need to assign a trigger to run our onFormSubmit(e) function in our Code.gs file.

To do this, navigate to the Code.gs file and then:

    1. In the sidebar of the IDE, select the Triggers button indicated by a clock.
    2. Select Add Trigger at the bottom right of the page.
    3. A popup window will appear. Under Choose which function to run select onFormSubmit.
    4. Select event type: On form Submit.
    5. Click Save.

Google Apps Script trigger on form submitClick to Expand!

Run a practice Form Submission

Go ahead and run a dummy form submission again and you should now get an auto-response email with the email you created.


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

Pfaawhh!!! 

If you made it to this bit you really want to learn about the nuts and bolts of the script. That’s awesome.

In this, code breakdown, I will go over some of the highlights of the code to help you understand what is going on.

Get the current submitters data

You will notice the single ‘e‘ as a parameter for the onFormSubmit function. This indicates an event object. When a trigger, like our on form submit trigger, is assigned to a function, you can use the event object to get the data that will be drawn from triggering that event. For us, we will get the response from the person who submitted the trigger.

To do this we use the response method on our event object and assign it to our responses variable. Line 3

This will give us access to the same list of methods as you would when you run the Test.gs testAndAuthScopes function’s response variable on the first response in the form

const responses = formResponses[0];

You can find out more options about the formResponse class here.

Here we can access things like the date and time the response was submitted, the response, the response item, the respondent’s email if that was enabled in the form.

For us, we will grab the current respondent’s email first. Getting to the email is a little different than accessing other responses. If you remember when you set up your form, you can force users to add their email to the form in the settings menu.

The email here will always be at the top of the form. To access it, you need to use the getRespondentEmail() function. Line 4

Next, you can access any form item response in your form by calling the getItemResponses() method that will return an array of responses in your Google Form. You can then count from zero down your form to find the number of the item that you want to draw the response from and grab it with an array call, [n].

For us, we need to get the first item after the email, which is the name. This is the zeroeth item in the list. Line 5

Alternatively, if you have already grabbed the item’s ID, then you could use, getResponseForItem(item).

You would have probably have noticed the trim() method on the end of each email. This JavaScript method allows us to simply take any whitespace that the respondent accidentally added to their response or email – I’m guilty of accidental extra spaces too. 

Get the first name of the respondent

We want to personalise our email response and use the respondent’s name in the email. These days, it’s generally preferable to use a casual first name approach.

We don’t know if the respondent entered their first name or full name. We know with a certain degree of confidence that they will also not add in their title in this input as well. With this in mind, we can extract all the letters up to the first name and safely assume that this will be their first name (No one likes entering a first and last name input when they can do it in one line).

Here’s our code:

Here we create our firstName variable. The first thing we need to check is if the user added just a first name or a first and last name.  This is done with a JavaScript ternary or conditional operator that is basically a single line if statement.

In our condition inside the braces, we use the indexOf method on our name variable. This method takes one variable, the searchable value. In our case, this is an empty space, (" "). If a space is found it will return the index or numerical location of the space. If no space exists, it will return -1.

We check to see if there is no space, which will equal -1. If this is the case, then we just report the name variable, because the user only entered their first name on the form.

However, if there is a space, then we want to create a new string with all the characters up to the first space. We do this by using JavaScript’s substring method. This method takes a start and end index inside the string. For us, our start is the first value which is zero. To get the end index we can use indexOf() again on name to get the location of the space.

Use our signature block on the email

We want to look professional here and add our Gmail signature block to the end of our email like we normally do when we send an email. You would think this would be a pretty simple process, but actually, it is a little challenging.

To access the signature, we have to use the Gmail API Advanced Service we installed earlier.

Here’s the code:

Google Advanced APIs are written a little differently than what is shown in the doc’s when we use them in Google Apps Script. This can make using them a little confusing to get your head around. Let’s take a look at the Gmail API documentation for this method:

GET https://gmail.googleapis.com/gmail/v1/users/{userId}/settings/sendAs.list

In Google Apps Script, this boils down to:

Gmail.Users.Settings.SendAs.list({userId}).sendAs

We replace the {userId} hint with the special "me" input to reference your own account.

Running this on its own will give you a list of all of your emails and their data that are assigned to your account. kind of like this:

 

For me, I have a number of domain accounts along with my primary Gmail account assigned. As you can see in the sample data above, you can see a signature object in the first array. This object contains all the HTML we need to generate our signature block at the end of the email.

We can access this by using the JavaScript filter method. This method creates a new array based on parameters you intend to use to filter. For us, we want to filter down to just keep our default Gmail account. Line 7 

This will give our list of objects and we can then select the signature object (Line 7) which we store in our signature variable (Line 4).

Note, that as of writing this email, there is no way to access alternate emails.

We can then use this at the end of our email message.

Emailing the respondent

Finally, we can email the respondent with our custom message. We do this with the MailApp class and call the sendEmail() method. This method can take a number of possible variations of parameters, but I like to use the object parameter because it gives me more flexibility.

With this approach, you can modify a number of different objects. For our project we will use the following:

  • to: This is the email of the respondent that we will send our message to.
  • subject: Just like in a normal email, you can add your subject text here.
  • htmlBody: You can add your HTML for your text here. You can always use one of the numerous email template builders to do this without hard coding HTML for most of your task. However, you will need to wrap your code in backticks and then at the very bottom add in the signature paragraph.

Here is a link to more emails on MailApp for you to get familiar with it:

Conclusion

In this tutorial, we looked at creating a custom email responder that is triggered when a Google Form is sent. We looked at how to add triggers to run our code when the form is submitted. Then we made sure all of our permissions and scopes were added so that our code would run. We also had to add the Gmail API advanced service to get our signature block.

So where to next?

You might want to run different custom email responses based on the respondent’s response. Here you could look at the user’s response and then perhaps use an if statement or switch to send the code to a specific set of htmlBody that relates to that particular response.

What do you think you would use this for? I would love to hear in the comments below.

If you’ve liked this tutorial and want to get regular updates on what I am working on next, you can subscribe down below this post.

Happy coding!

~Yagi

How to programmatically schedule weekly time triggers between two dates in Google Apps Script

Carelessly left behind Google Apps Script time triggers can be greedy little blighters that can ultimately end in a whole lot of noggin scratching when your scheduled scripts decide not to run all of a sudden. Then there is a whole lot of house cleaning to remove all those time triggers you couldn’t be bothered setting a calendar reminder to remove them when you didn’t nee them any longer.

After all, there are some pretty tight quotas for the Consumer account (90 min) and if you are doing some heaving lifting in your Google Workspace account (6 hrs), then it will add up fast. Well… maybe I am just a glutton for triggers.

In this tutorial, we will cover how to schedule weekly time triggers between a period of dates in Google Apps Script programmatically using the Clock Trigger method of the ScriptApp class. The code basically sets all the triggers up on the desired range of dates and then removes all the triggers when the time expires.

Let’s get stuck into it!

The Code

Triggers.gs

This is the main code you will copy and paste into your own project. Read the Quick Use guide for what you will need to update for your own project.

ExampleFuncitons.gs

This file is just an example of the functions I have assigned for my project in my runsies() function.

Quick use guide

Here, we will quickly go through using the script to get you up and running.

The Example

In the example, we have an imaginary document that needs to be edited by our team. If you look at the ExampleFuncitons.gs file you can see our list of time trigger task for our Google Apps Script project as follows:

  • Refresh the Google Sheet: We possibly need to send a report and clear it out at a certain time to set it up for the next week.
  • Open and send an access email: We will send off an email to our team to let them know that they now have edit access for the sheet and complete their weekly task.
  • Send a deadline reminder: Just before the deadline, we send out a reminder to our stragglers that the deadline is due so that they can get unnecessarily offended. 🙄🐐
  • Remove the editors from the sheet: Once the deadline hits, we revoke edit access for our team from the sheet to maintain the integrity of the sheet before we send off our report.

Before you add your trigger times

A note on time zones

Just hold up one second. Before you add your trigger times, I recommend that you double-check the time zone assigned to your project.

Didn’t know that was a thing?  No worries. Check out this short video on how to get that done fast:

And you can dive a little deeper here:

Help! My time triggers are not in sync! : How to update your Google Apps Script project time zone.

Adding the script into your project

I recommend creating a separate *.gs file to add in the timeTrigger object from lines 35-145. It just gets it out of the way of your awesome project codes.

The runsies() function is just an example function. If all you want to do is add your triggers days and dates in and hit run, then it is fine. But if you want to programmatically draw your start and end trigger dates and weekly trigger times from somewhere else then all you need to add into your own function is the  TIME_TRIGGER objects and then run:

timeTrigger.deploy(TIME_TRIGGER);

Adding your times and date window

There are two parts to this object. First, set the start and end date that you want to run your weekly triggers. Lines 4 & 5

If you want to start your triggers straight away, then you can enter “now”, otherwise enter in a date. You will get an error message if your date is before the current date.

The end date removes all the time triggers in your project. So if you have other triggers, you will need to make some changes to the timeTrigger object (Maybe register each trigger id in PropertiesSerivce).

To add your dates, start with the year, month and date. Note that unlike the weirdness of the Javascript Date() constructor, I have made the month the common number. So a 3 will be March (not April in the Date() constructor).

Next, add the weekly triggers that you want to run each week for this project. Lines 21-28

You can add in as many as you want here. In the example, I have added four. Each piece of weekly trigger data is contained in an array:

[function, weekday, hour]

  • function: The function that you want to be triggered.
  • weekday: Full word days of the week from Monday to Sunday.
  • hour: The hour of the day that you want to run the trigger.

Deploying and scheduling the time trigger

To schedule, your  triggers run the timeTrigger.deploy(TIME_TRIGGER); function.

If you want to test things before your first deploy, you can check the triggers are all set up you can check the triggers in your Apps Script menu (1).

List of triggers in Google Apps Script Editor IDE
Click to Expand!

To check the times of your trigger, you can click on the vertical ellipses beside each trigger (2).

You can delete all the triggers in your project and start again with:

timeTrigger.remove();

That’s pretty much all you need to know to get this script up and running in your project.

If you want to learn more about how the timeTrigger object was written and how to code Clock Triggers, jump down into the next header.


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 for timeTrigger

timeTrigger.deploy()

The timeTrigger.deploy() function is the main run function and takes the object of start and end dates and weekly triggers that we assigned in the TIME_TRIGGER object in the runsies() function.

Its first task is to set the date the weekly triggers need to be removed. This is done with the setEndDateTrigger(triggerData) function that we will discuss in a minute.

Check if start dat is now

Next, we need to check the input for the triggerData.startDate. If the user selected “Now”, then we immediately run timeTrigger.setTrigger() and complete the script. Lines 12-16

Check if start date is today

Alternatively, if the user puts in the current day’s date then we need to run the timeTrigger.setTrigger() straight away too. First, we need to transform the users triggerData.StartDate data into a readable date. We do this using the Javascript new Date() constructor that has the option to take the format; year, month, date:

new Date(year, month index, day of month)

Because the month index for Date()  starts at 0 for January and ends at 11 for December, so we need to subtract 1 from the users month input before creating the date. Line 18

I used a Javascript spread operator (…array) to add in all the values of triggerData.StartDate which in our example are:

[2021, 3, 8]

So:

new Date(...triggerData.startDate)

Is actually, this:

new Date(2021, 3, 8)

Which is much tidier than:

new date(triggerData.startDate[0], triggerData.startDate[1], triggerData.startDate[2])

I’m really digging the spread operator lately. 

Next, we need to compare the current date with the triggerData.startDate. We can do this by converting the two dates to times using the getTime() method. This transforms the dates into long number values that can be compared. Line 25

Before we do this though we kinda need to clear the current time out of the current data otherwise we won’t be able to compare things properly (line 26). When we run new Date() to create the today variable it will give us the date and the current time all the way down to milliseconds. However when we created the date for our scheduled date we only added the year, month and day so the time will be set to midnight.

Let’s update today date by changing the time of the day to midnight with:

today.setHours(0, 0, 0, 0);

If start date is in the future, schedule it!

Our final condition is if the user has scheduled a date in the future. We don’t want to clutter up their trigger quota unnecessarily so we need to postpone our triggers. To do this we ironically, need to create a clock trigger that runs only once on the date we want our weekly triggers to start.

To programmatically set a time trigger in Google Apps Script we call the newTrigger method in the ScriptApp classLine 34

The newTrigger method takes our assigned function as an argument. This is the function we want to run when our trigger goes off. For us, this is the timeTrigger.setTrigger() function that will build the weekly triggers.

Running setTrigger() also starts the process of building the trigger.

Your next step is to decide what type of trigger you want. In our project, we want a timeBased() trigger. Line 35

Selecting timeBased() takes us to the Clock Trigger builder, where we can choose from a bunch of settings. For us, all we want to set is a start date so we chose the at(date) method which takes a constructed Javascript date. Here we throw in the startDate variable we built on line 19.

Once we have all our trigger data inputted, we need to create() our trigger.

timeTrigger.setEndDateTrigger()

The timeTrigger.setEndDateTrigger() function takes the triggerData as an argument. From that, it extracts the endDate array of year month and day.

Just like in timeTrigger.deploy(), we need to take one away from the month to get the proper value for Javascript. Then we want to remove our weekly triggers basically on the midnight of the following day after the user’s recorded end date. Lines 11-12

Why? Well, the end date usually means that it is a date the final trigger will occur on. We don’t want to remove our trigger before that date though or we will mess up our user’s process.

Once we have created our date we go through the same process as scheduling a start date in the future like we did in timeTrigger.deploy().

timeTrigger.setTrigger()

This trigger is initialised from either the timeTrigger.deploy() straight away if the start date is the current date or is scheduled for deployment at a later date.

The timeTrigger.setTrigger() takes the 2d array of all the weekly triggers assigned by the user. In our example, that was this:

First, it loops through each set of trigger input data with a forEach loop.

Assigning variables using destructuring

I want to assign a variable for each item in the currently iterated array so that I can work with it in building the trigger. Here I used a destructuring assignment now available in Google Apps Script V8 runtime. Line 11

If you are unfamiliar with the destructuring assignment, you can basically set an array of variables – in our case – on the left and assign (=) them to a corresponding array of data on the right. So:

let [nextFunct, weekday, hour] = time

Would assign these values to the varialbes on the first iteration:

let [nextFunct, weekday, hour] = ["refreshSheet", "Monday", 7]

Without destructuring this variable assignment might look like this:

Pretty cool, I reckon.

PReparing the days of the week

Next, we need to assign a day of the week to our clock trigger. These assignments require an enumerator which is basically:

ScriptApp.WeekDay.A_DAY_OF_THE_WEEK _IN_ALL_CAPS

Fortunately for us, we assigned our variable weekday to time[1] which is the day of the week for this iteration.

Lines 13-21

Creating the weekly clock triggers

Finally, we create our first clock trigger. Just like our date triggers we first call ScriptApp.newTrigger(nextFunct) where nextFunct is the time[0] value the user assigned as their function that they want to run on their trigger.

Again, we set the trigger to timeBased() but this time around we use onWeekDay() method. This method takes one of those weekday enumerators we store in our wkDay variable. We will select the correct one by adding weekday variable inside weekday:

wkDay[weeday] Line 25

For weekday triggers we can also set the hour of the day. This will deliver the trigger close to the hour selected. Line 26

timeTrigger.remove()

The timeTrigger.remove() function runs on the end date. Alternatively, you can run this to remove all of your triggers if you are testing on making a mistake.

To remove your triggers, you will first need to get all of your projects triggers. We do this with ScriptApp.getProjectTriggers();Then, you will need to loop through each trigger and delete the trigger using the deleteTrigger() method that takes the trigger object.

 Conclusion

I have been deploying triggers like this for a while now after and…erhm…unfortunate incident with meeting my quota and being lazy and leaving old triggers active. This now saves me a lot of future grief.

One thing you might have to adjust for a bigger project is the timeTrigger.Remove() method. If you have other triggers running in the same project, you probably don’t want to delete them. You could use the properties service to store your trigger ids and then check them against the list of all triggers to determine if they need to be deleted.

Another thing worth considering is that you might want to schedule multiple dates to run your triggers. You would need to loop through each start and end date here and create a trigger for each one.

Anyway, if you found this useful please give it a like in the comments below and if you have a plan to deploy it in your own project or a version of it I would love to hear about it in the comments.

Happy coding!

~Yagi