Google Sheets: How to create a task completion check sheet. (Updated Feb 2022)

Google Sheet: IMPORTRANGE, IF, TODAY, conditional formatting, tick box, alternating colors, protect sheet and ranges, share sheet 

Sometimes you have teams, staff or students who all need to complete reports and tasks by a certain date, but you need a way to keep track of when the reports are complete so that you can compile them later.

The following tutorial is a simple tick box-based Google Sheet that the user ticks when they have finished writing their quarterly report. I use something like this in my day to day to keep track of a number of reporting processes.

This is very much a follow-along guide for Google Sheet Beginners to help develop some basics skills and think about how to design Sheets for the workplace. Feel free to read what you need or skip to the end for a link to the google sheet for you to make a copy of.

Here is what we will complete by the end of the tutorial.

If the user’s tick box had not been ticked in Column ‘C’, Column ‘D’ will automatically report Overdue in red and Column ‘E’ will report the number of days overdue.

There is a space set aside for administration to note the reminders that they have sent to the user and the last date the reminder was sent. Admin can easily copy the emails and send a message to those staff who have not completed their report by the deadline.

Let me walk you through the workflow for creating this:

Getting the names and emails

set up

The easiest approach to getting names would be to simply find a list of staff names and copy them into a new sheet. The problem here is that if a staff member changes position or needs a correction to their details, then you have to go into every sheet that the user is on and change it. A much better approach is to import the user’s details from a core Google Sheet of user data that you use for all your spreadsheets. That way you are only changing one spreadsheet and the changes will apply to all.

We are going to use the function IMPORTRANGE to get all our staff and their emails.

Open a new Google Sheet and name it “Quarterly Report Checklist”.

On row 5 we are going to add the headers “Name” and “Email”. Don’t worry about making it pretty yet, we will tidy it up later.

Now, open the link to my Staff List. You will need to make this file your own to use in your sheet. To do this. click File > Make a copy.  You can put that file anywhere but for now, put it in the same folder as your Quarterly Report Checklist Google Sheet.  Here is the sheet:

Staff List

importrange

Go back to your Google Sheet and in cell A6 we are going to use the IMPORTRANGE function. This function allows you to get data from another sheet and put it in your current Google Sheet.

The IMPORTRANGE function takes two parameters:

1. The URL of the source Google Sheet – in our case, your copy of the Staff List Google Sheet.
2. The range of rows and columns you want to import – in our case, the Name and Email column of Staff List.

One super tip here is that you actually don’t have to provide the full URL to your spreadsheet, you just need to provide the unique id for the spreadsheet:

https://docs.google.com/spreadsheets/d/1oyA4L8M1cg1m4A2YJcEmffJRfikElNzHJ64poMfAnik/edit#gid=0

To find the id for your Staff List file, open your copy of the file and look at the URL link (remember, your link will be different because it is a copy).

Next, you will need to know the name of Sheet tab that the list is on in the Staff List Google Sheet. In our example, this tab is named Staff. You will need to combine this with the range of names and emails. Our range starts at A2 and ends at B21. So the resulting range would look like this: “Staff!A2:B21”. Note the “!” after Staff and make sure you put it all in double quotes.

Back in your Quarterly Report Checklist Sheet in cell A6, start the IMPORTRANGE function:

=IMPORTRANGE( "Your sheet ID","Sheet tab!rangeStart:rangeEnd")

=IMPORTRANGE("1oyA4L8M1cg1m4A2YJcEmffJRfikElNzHJ64poMfAnik","Staff!A2:B21");

NOTE! Remember to use your own Staff List id for your copy.

Now, you may get an error warning asking your permission to allow you to add this imported data into this document. If so, click “allow”. If everything was successful, your sheet should now look like this:

Tick Boxes

We’ll go ahead and add the Tickboxes in Column C. In cell C5 add the title Report Complete. Don’t worry if data overlap cells for the time being.

Next select cells C5:C25. Then go to Insert > Tick box.

Admin headers

Add the following headers for cells D5 to G5:

• Overdue?
• Days Overdue
• Reminders Sent
• Last Reminder Date

Select row 5 and ctrl + b  to bold.

Select the columns A to G and double click on G‘s right edge. This will reset the columns to fit the width of the data.

Select Row 5 again and then got to Format > Text wrapping > Wrap. Then go to columns E, F and G and click and drag the right edges in so that they are a little narrower.

In cell D4 add the: Admin Only header. Then click on D4 again and hold down shift and click G4. This will select this range. In the menu bar, click the merge button. Press ctrl + B + I to make it Bold and Italic. Go to the bucket icon and fill the merged cell with a light green. This will help users identify that this is for admin use and not for them.

Next, add the title Quarterly Report in cell A1 and merge it across to cell G1. Press ctrl + B to Bold. Resize to 14 and then got to File > Align > Centre, then File > Align > Middle. Click the first line below row 1 on the far left and drag it down to give it a bit of space.

In cell B2, type: ‘Due Date:’ and bold and italicise. Then in cell C2 add the due date. Note, I have set my date to Day, Month and Year. You can change yours to Month, Day and Year if that is what you are used to. Just go to Format > Number > custom date and time

Got to the fill bucket icon and fill with a light grey. Then got to the adjacent border icon and click it > select the border colour to a dark grey > then select the full border.

Creating the overdue field, column d

Our Overdue column updates to either Complete, Overdue or empty. If it is before the due date and the user hasn’t completed the report we want to leave the cell blank. If it is past the due date and Report Complete had not been checked, then we want the cell to display Overdue. Finally, if it is past the due date and the Report Complete cell had been check for the user, then we want the cell to display Complete. 

Let’s break the formula into parts. First, we want to check if the current date is equal to or less than ( <= )the current date. You can use the TODAY() function to get the current date. We will use an IF() function to see if today is less than or equal to the date and respond with and blank cell. Add this to cell D6:

=IF(TODAY() <= $C$2,"")

Now if today is less than or equal to the date, the cell will be blank otherwise the cell will display FALSE. Go ahead and play with the date in cell C2 to check that this formula is working as expected.

Now, if it is past the due date, we want to check to see if Report Complete is not ticked. If these conditions match (TRUE) then we want to display Overdue, if they don’t (FALSE) then we want to display Complete (because the box has been ticked. We will need another IF function to work this out:

IF(C6 = FALSE, "Overdue","Complete")

Add this IF function to the first one.

=IF(TODAY() <= $C$2,"",IF(C6 = FALSE, "Overdue","Complete")

Done. Play around with the C2 date again and the Tick Box in cell C6. Make sure everything is working according to the date. Once you are happy, click back on cell D6. There will be a little blue square at the bottom right, double click it or click and drag it down the column.

It’s a bit hard to see the difference between Complete and Overdue. Let add a bit of warning colour to the Overdue cells with some conditional formatting.

Select the full range of cells D6:D25. Then select Format > Conditional Formatting… .  A sidebar will appear with a list of options.

In the Format Rules click the dropdown menu under Format Cells if… and select Text is exactly. In the space below write Overdue.

Under the Formatting Styles heading change the text to a dark red and fill the cell with a pinky red.

creating the reminders sent filed, column E

In column E we want to display how many days overdue the user is in delivering their report. We only want to display the days overdue when column D displays Overdue. To get the days overdue we subtract TODAY() from the due day:

=IF(D6 = "Overdue", TODAY() - $C$2, "")

Go ahead and centre the days overdue.

Next, we will apply some conditional formatting to the Days Overdue so that any number greater than 0 will be red.

Reminders sent and Last Reminder Date

Columns F and G are for Admin. Here you can record when you sent reminders to staff and the date of the last reminder. Here I just added zeroes to the reminders sent*.

*We’ll be automating the reminder process with Google Apps Script in the next tutorial. 

making it pretty

Making things look good isn’t just for aesthetics. It also plays a role in guiding and informing the user. If we add some alternating colours to each row it will make it easier for the user to see which row they need to check.

Let’s apply some Alternating Colors to columns A:C. Select the headers and all the values in these columns. Go to Format > Alternating Colours… . A sidebar will appear.

I chose the default grey. It already comes with a darker header and you can modify the colours too. Scroll down to the bottom of the sidebar and select Done.

Give it a try with columns F and G too.

In cells D5 and D6 I matched the header colour to the Alternating Colour header for consistency.

Put a red grid border filling the Data for Overdue and Days Overdue columns D6:D25). This will look a bit tidier with the conditional formatting. Put a border around the Admin Only data D4:G25. Use a thicker border with a dark grey. If you do this out of order you might find you thick grey border missing between columns C and D and along two cells at the bottom.

Preventing users from editing certain cells

Finally, we don’t want users to edit all of our cells. To prevent them from doing this, we are going to Protect the sheet from editing by anyone else but us except for the Report Complete Cells C6:C25 range.

To do this, first select the range C6:C25. Then right-click on the Sheet1 tab at the bottom. Select Protect Sheet… . A sidebar will appear.  Tick the tick-box Except certain cells. Finally, click Set permissions.

Your Google sheet is now locked from editing except the cells you chose.

A pop-up will appear for you to choose who you want to give permission to still edit this. Choose the default only you.

share the sheet

Before you share the sheet, change the due date to the correct date and untick the Report Complete Boxes.

To share the Google Sheet the fasted way is to select all emails in the email column. Copy them (crtl + c).  Then in the top right click the green share button. Under people, paste all the emails in (ctrl + v). Make sure the pencil is marked on the right to give them the ability to edit (remember they can only edit the cells you gave them permission too).

After you paste the emails (The emails in the example won’t work of course), a text box will appear for you to send people a message. Write your message to them and click send. They will get an email with your message and a link to your sheet.

Conclusion

Hey! You made it all this way. Nice one! Here is access to the original sheet. Just Make a copy and it is yours to edit and own. You’ll need to update the IMPORTRANGE with your own sheet for it to show the names and emails for your own project.

Quarterly Report Checklist

We’ve covered a lot of ground in this tutorial. We looked at:

• IMPORTRANGE
• IF
• TODAY
• Conditional Formatting
• Style and Layout
• Alternating Colours in Rows
• Protecting Sheets
• Sharing Google Sheet

Did you enjoy the tutorial? Want to upskill and get 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.

 

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