Google Sheets Beginners: Getting Help (44)

One of the biggest revelations that I came across that really changed my whole view of learning was that learning how to ask the right questions or know where to get help completely transforms how you learn.

Knowing how to get help quickly and efficiently in Google Sheets is really going to cut away a lot of the frustration of finding the right information and get you there in a much more efficient way.

In the video below I will go over how to use all of Google Sheets help tools effectively and then discuss how you can research to find out how to build more complicated functions and processes in Google Sheets.

Check out the video below!

00:00 Intro
00:27 The help menu
01:13 Keyboard shortcuts
01:30 The help search bar
02:08 The Sheets Help tool
02:44 Understanding the help tool for functions
04:03 Preparing complex formulas with the Function List
05:14 How to ask the right questions

This is the last video in the Google Sheets beginners Youtube course. This is the first online course I have created. It was a long and rewarding journey. I’ve learned a lot along the way and I hope you got a lot out of the course.

If you enjoy the videoes and want to learn more about how to use Google Sheets, check out the Youtube Playlist:

Google Sheets Shorts: Beginners

Did you enjoy the tutorial? Want a solid step-by-step course to become a pro at Google Sheets? Check out my course, Google Sheets: Learn the Essentials with Three Detailed Projects. Sign up today.
Google Sheets Shortcuts Mousepad
Google Sheets Shortcuts Mousepad

Want a fast track to boost your Spreadsheet efficiency? Grab one of these handy Google Sheets Shortcuts mousepads that I created from my store.

Google Sheets Shortcuts Mousepad Gel
Google Sheets Shortcuts Mousepad Ge

~Yagi

 

Google Sheets Beginners: Understanding The Explore Tool (43)

The Google Sheets team seem to come out with more and more great tools to help us do more, help us think creatively about how we display our data and improve our efficiency.

On the wave of machine learning, it seems that the Google Sheets devs are making our lives just that little bit easier with the Explore Tool.

You can find the explore tool down the bottom right of your Google Sheets. The tool strives to do a number of things:

  • Bridge the gap, and considerable learning curve, by providing some natural language support for asking questions about your data in Google Sheets.
  • Create a constantly improving list of styling, chart and pivot table suggestions that you can simply click an add to your own project.
  • Provide inspiration for you to think about how to best present your data in a clear and informative way.

Check out the video!

00:00 Intro
00:16 Features of the Explore tool
00:30 The first Example
00:42 Understanding the Explore Menu
02:00 Asking Explore questions
04:06 The second Example
07:46 My opinion of the current state of the Explore Tool

If you enjoy the videoes and want to learn more about how to use Google Sheets, check out the Youtube Playlist:

Google Sheets Shorts: Beginners

Did you enjoy the tutorial? Want a solid step-by-step course to become a pro at Google Sheets? Check out my course, Google Sheets: Learn the Essentials with Three Detailed Projects. Sign up today.
Google Sheets Shortcuts Mousepad
Google Sheets Shortcuts Mousepad

Want a fast track to boost your Spreadsheet efficiency? Grab one of these handy Google Sheets Shortcuts mousepads that I created from my store.

Google Sheets Shortcuts Mousepad Gel
Google Sheets Shortcuts Mousepad Ge

~Yagi

 

Google Sheets Beginners: Add-ons (42)

Sometimes you get to a point in your Google Sheets career where the vast array of tools that Google Sheets is not quite enough. Maybe you want to shortcut a process but don’t have time to learn or write Google Apps Script to do it.

Well, the good news is that there is a huge community of developers who have provided a huge amount of added extras to your Google Sheets all with the help of add-ons.

To learn about the Google Sheets Add-on marketplace and how to add, manage and remove your add-on check out the video below.

00:00 Intro
00:16 What are add-ons
00:53 Accessing the add-on marketplace
01:07 Searching for add-ons
01:38 Learn about he add-on
02:20 Installing the add-on
02:38 About authorisation
03:33 Accessing the example Add-on for the first time
04:16 Getting Help for the add-on
04:39 Run the example add-on
05:48 Managing add-ons
06:27 Uninstalling the add-on

If you enjoy the videos and want to learn more about how to use Google Sheets, check out the Youtube Playlist:

Google Sheets Shorts: Beginners

Did you enjoy the tutorial? Want a solid step-by-step course to become a pro at Google Sheets? Check out my course, Google Sheets: Learn the Essentials with Three Detailed Projects. Sign up today.
Google Sheets Shortcuts Mousepad
Google Sheets Shortcuts Mousepad

Want a fast track to boost your Spreadsheet efficiency? Grab one of these handy Google Sheets Shortcuts mousepads that I created from my store.

Google Sheets Shortcuts Mousepad Gel
Google Sheets Shortcuts Mousepad Ge

~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? Fiverr’s your best bet to find a skilled Google Apps Script professional to solve your problem quickly and cheaply. *

*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

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

If you have made it to this post you are probably a little frustrates about why your Google Apps Script time triggers are not running on or around the times that you have designated. Of course, you could simply just be a well-oiled machine and want to know how to update the time zone of your project for your client.

To cut to the chase, the likely cause of clock triggers not running in the time range that you have selected is that the time zone in your Google Apps Script project is out.

How to check your Project’s Timezone

So, how do you check the time zone of your project?

If you need to do it programmatically you can use the Session class and the getScirptTimezone() method. Something like this will log your project’s timezone:


Alternatively, you can check out your project manifest file.

To do this in your Google Apps Script IDE, go to your sidebar and select the Project settings cog and then check the Show “appsscript.json” manifest file in editor checkbox under the general settings.

Google Apps Script manifest file
Click to Expand!

Go back to the editor and your “appscript.json” file will be visible. Select it and you will be able to see the current time zone for your project.

Google Apps Script time zone in manifest file
Click to Expand!

Changing the timezone

So, at the time of writing this, I found the information on the correct syntax for the time zone in the manifest in the docs either too simplified or when clicking the associated link, too convoluted for my poor goat brain to handle.

In short, you can add certain countries followed by their city. Like this:

“Australia/Sydney”

Or in your code, like this:

Alternatively, you can use GMT time in this format:

“Etc/GMT-10” << For Sydney, Australia

The -10 can be changed to whatever GMT you need.

Resources

After a bit of testing and some searching, I came across these two resources to help you when entering in the correct time zone.

Alternatively, I have created a Google Sheet that contains:

  • A full timezone list with aliases
  • A full timezone list without aliases
  • A GMT only list

These might be useful for bookmarking or exporting certain Google Sheet tabs as CSVs.

Just go to File > Make a copy so you have your very own version of the document.

Finally, here is a list of all the GMT values:

Standard Offset GMT
-12:00:00 Etc/GMT+12
-11:00:00 Etc/GMT+11
-10:00:00 Etc/GMT+10
-09:00:00 Etc/GMT+9
-08:00:00 Etc/GMT+8
-07:00:00 Etc/GMT+7
-06:00:00 Etc/GMT+6
-05:00:00 Etc/GMT+5
-04:00:00 Etc/GMT+4
-03:00:00 Etc/GMT+3
-02:00:00 Etc/GMT+2
-01:00:00 Etc/GMT+1
+00:00:00 Etc/GMT-0
+01:00:00 Etc/GMT-1
+02:00:00 Etc/GMT-2
+03:00:00 Etc/GMT-3
+04:00:00 Etc/GMT-4
+05:00:00 Etc/GMT-5
+06:00:00 Etc/GMT-6
+07:00:00 Etc/GMT-7
+08:00:00 Etc/GMT-8
+09:00:00 Etc/GMT-9
+10:00:00 Etc/GMT-10
+11:00:00 Etc/GMT-11
+12:00:00 Etc/GMT-12
+13:00:00 Etc/GMT-13
+14:00:00 Etc/GMT-14

And a link to the GMT converter:

GMT Converter

I hope this saves you a little time.

 

~Yagi