Blog Feed

Importing Range Data From One Google Sheet to Another

One really amazing thing about Google Sheets is how easy it grab live data from one Google Sheet and import it into another. I’m not talking about a simple copy and paste job here. I am talking about real live data. Data that, when updated in the master sheet, will be reflected in the Google Sheet that you have imported the data to.

Everything starts with the IMPORTRANGE Google Sheets function.

But before we get started on the “How to’s,” you might be wondering why you might need to import live Google Sheet data from one sheet to another.

Or check out the table of contents to dive straight into what you need right now!

Continue reading “Importing Range Data From One Google Sheet to Another”

Create a ISO String from date text input intended for UTC date in JavaScript

Let’s say that you receive a date like “14/01/2022”, “14 January 2022”, “Jan, 14 2022” etc, and you need to convert this date to an ISO string in JavaScript while ensuring that the date that is inputted is for UTC (Universal Time Coordinated) timezone – no matter where you are in the world. It would seem easy right?

Your first reaction might be to simply do something like this:

Now unless you are sitting smack-dab in a UTC timezone you might be in for a bit of a surprise.

Right now, my timezone is UTC+11 hours. This means that my result of the code in the example above will report the previous day at 1pm UTC.

That’s not what I am looking for all. I need to set this date to precisely midnight of 14 Jan 2022 UTC time.

The ISO String we need is this: 2022-01-14T00:00:00.000Z

The Solution

Continue reading “Create a ISO String from date text input intended for UTC date in JavaScript”

How to create a time sequence in Google Sheets

Whether you are creating a Google Sheets data validation dropdown list of each minute in the day or want to create a daily progress log with 15-minute intervals, learning how to create a list of times in Google Sheets is a pretty solid skill to have.

However, knowing how to create a list of times in a single formula not only makes you a spreadsheets archmage 🧙‍♂️ but also allows you to do cool stuff like:

  • Automatically change your start and end times.
  • Changing your step increments from a minute to, say, every five minutes or an hour.
  • Make these changes quickly straight in your formula or another cell reference or even a formula rule in another cell.

So yeah… like I said… archmage skills.

via GIPHY

Sound cool? Of course it does, it spreadsheets! 🐐

In this tutorial, we will cover two approaches:

  1. A whole day list of times in 1-minute intervals
  2. Selected start and end times with selected intervals

Why can’t we do just one?

Well, there are two slightly different approaches to each. Geez! What’s with the questions?

Let’s get cracking!

Continue reading “How to create a time sequence in Google Sheets”

Calculate the Total Duration of Time between two periods in Google Sheets

Calculating the duration of time between two times is a pretty common task. From measuring how long a process took, to entering the hours you put into a project, to creating a timesheet. You will find it everywhere, so working out duration is a hand skill to have in Google Sheets.

However, there are some gotchas when calculating the duration of time. So it is worth a good looking into.

To get a feel of how calculating durations work out in the wild, you can check out the video below for a great example on how I use it to build a Project Task Google Sheets workbook.

Get your very own copy here: Project Task Manager.

In this tutorial, we will cover how to calculate the duration of time over a single day or multiple days and sum a list of durations.

I encourage you to follow along so you can create your own template copy of the Google Sheet and do a bit of practice and experimenting on your own.

Pretty much everything in this tutorial is beginner-friendly. Don’t be afraid to give it a go.

Continue reading “Calculate the Total Duration of Time between two periods in Google Sheets”

How to Automatically Share Teachable Students to Google Drive Files and Folders when they Enroll in your Course

Not only are Google Workspaces, Google Sheets, Docs, Forms and Slide great to work in and look awesome, but the convenience of collaborating and sharing your Google Drive Files and Folders is also super useful. So much so that many course creators share their documents with their students all the time.

The problem that course creators have is that they are generally stuck with two options when sharing their Google Drive files and folders:

  1. Set sharing to Anyone with Link can view. And hope other freeloading students don’t find and share their course material.
  2. Manually share each student as they enrol. Which is time-consuming for the course creator and annoying for the student who needs to wait to be shared before they can get their hands on your awesome course content.

Both options are really terrible.

I reluctantly chose option one for my first Google Sheets Essentials Teachable Course and it really bothered me. I needed to find a way to share my Google Drive course content with only those students who signed up for my course.

In this tutorial, I will guide you through creating a Google Apps Script web app that receives a webhook notification when a student enrols onto one of my Teachable courses. If a student enrolled with a non-Gmail or non-Google Workspace domain email account, they will be sent an email with an attached form to add a Google-friendly email.

If you want a copy of the Google Sheet with the Apps Script attached, without coding it all yourself, plus written-visual tutorials on how to quickly set up your sheet head over to my teachable page now and purchase the sheet and instructions for just $2.95. Yeap not even the price of a cuppa.

The fun thing is that you will experience how the whole process works, because…well…that’s how I am going to share the Google Sheets file with you when you enrol. Neat, hey?

As a part of your purchase you will also get a few other perks:

  • Set files or folders for ‘view’, ‘comment’ or ‘edit’ access. 
  • Add existing students to your selected course Google Drive Files and Folders.
  • Get your full course list from your Teachable site right in your Sheet. 
  • A choice to bulk set your files and folders to:
    • prevent downloads, copying and print.
    • Prevent sharing by any documents you have provided ‘edit’ permission to.

If you want to understand how it all works and build your own, read on, you can always throw a couple of coins at me and enrol to run the workflow just for fun.

Instantly share ONLY Teach:able Students to selected Google Drive Files and Folders

 

If you are looking to build your own Teachable Course you can check out a how-to guide here:

How to launch an online course—and craft your email strategy

Continue reading “How to Automatically Share Teachable Students to Google Drive Files and Folders when they Enroll in your Course”

%d bloggers like this: