Back to Blog

How to Build a Weekly Schedule Template in Google Sheets and Connect It to Airtable

If you have a few tasks per week, you can manage your time using a calendar app, such as Google Calendar or Apple Calendar. However, an avalanche of events, tasks, and other jobs to be done can be too much to handle with your calendar. In this case, entrepreneurs opt for weekly schedules, which can be customized and automated for data input. The latter feature will become a lifesaver for your business if properly used. Read on to learn how you can do this and discover a ready-to-use weekly schedule template.

How do you use a weekly schedule for your workflow?

A weekly schedule is a worksheet or timesheet that has two axes. The horizontal axis lists the days of the week. The vertical axis displays working time per day, broken down into hours or hours and minutes like 8:15 AM. The cells where the day and time cross contain data about a task or job to be done. Here is a schedule template that you can find in the Google Sheets template gallery.

It’s actionable and simple, but we’re going to pump it up so you can use it for versatile use cases, such as:

  • A timesheet for small teams
  • A timetable for teachers and students
  • A planner for event agencies
  • A work schedule for all sorts of meetings and sessions
  • A simple schedule for your household activities
  • And many more

Weekly schedule in Google Sheets: do it yourself

The default Google Sheets weekly schedule template is good, but it can and should be improved. For example, it only provides one row for each time. If you need to input several tasks for a particular time, you’ll need to manually insert as many rows as needed. For that reason, let’s make a custom reusable schedule template

Horizontal axis: days of the week

Most businesses start their work week on Mondays. Others may have a different starting day of the week. Hence, let’s make the template time-flexible. Apply Data Validation to the cell with the starting date – this is B2 on our template. For this, right-click on the cell and select Data Validation. Then pick Date in the Criteria field and click on Save.  

Now, when you double-click on the B2 cell, a calendar will drop down for you to pick the date as the starting day of the week. Next, select the C2 cell and apply the following formula:

=B2+1

Drag the cell towards the right to H2 – the cells will be filled with consequent dates. 

To assign the day of the week to the dates we have, do the following:

Apply the following function to the B3 cell:

=TEXT(B2, "DDDD")

Drag the cell towards the right to H3 – you’ll get the days of the week for each consequent date. 

Vertical axis: working hours

We want our template to have a few rows for each working time. Grouping rows will help us with that. Specify the working time – for example, 8:00 AM, in the A5 cell. Then select rows 6 to 13 and group them – either right-click and select Group rows 6-13 or use the Alt+Shift+ArrowRight shortcut. Read our blog post to learn more about Google Sheets shortcuts

After that, merge the cells of the grouped rows (A5 to A13).

Repeat this for all the time ranges you want to have in your weekly schedule.

Appearance

Feel free to benefit from all the formatting features provided by Google Sheets:

  • Use different font colors
  • Apply borders 
  • Merge cells
  • Apply the number format you want for the date
  • And so on

Your weekly schedule should look awesome! If you want, you may add a Notes section in the bottom like we did. That’s it.

Why should I couple my weekly schedule with third-party apps?

The weekly schedule template is ready and you can use it for your project. However, if you need to manually fill it in with the data stored outside Google Sheets, why not automate this data import? 

Check out the use case the Coupler.io team faced some time ago:

An educational online project uses Airtable to store their data, such as teaching programs, information about students and teachers, etc. They chose this app as a database for several reasons, including its fast speed and search. However, they still use Google Sheets to share timetables with teachers and students. What they want to do is integrate their relational database in Airtable and weekly schedules in Google Sheets. 

Coupler.io made it happen.  

Use case: how to integrate Google Sheets weekly schedule with Airtable

If you need to pull data from Airtable or another app, Coupler.io will do the job. It is a Google Sheets add-on that lets you connect your data source with spreadsheets. Check out the Coupler.io homepage to see the integrations available so far. If your app is not on the list, feel free to share which data source you’d like to connect to Google Sheets using this form. Our team is developing new integrations, and your input is highly welcome. 

Install Coupler.io

Install Coupler.io from the G Suite Marketplace. Alternatively, you can install it from the Add-ons tab in your spreadsheet.

Set up an Airtable importer

An importer is an integration that connects a spreadsheet to a specific data source. We need to set up an Airtable importer by filling in the required and, optionally, additional fields. You can find a detailed description of the setup process in our blog post, Connect Airtable to Google Sheets

Shared URL – this is the core parameter of the Airtable importer. You’ll need to create a shared view link of your Airtable database and insert it in this field. Here’s how we did this for our small database:

Note: Pay attention to the Automatic data refresh feature. If enabled, your data will be imported automatically at the specified frequency.

Once the importer is ready, run it to export data to your spreadsheet.

Link the Weekly Schedule with the imported raw data

The final step is to connect our weekly schedule with the raw data exported from Airtable. For this, we’ll need to do the following:

Step 1: Split the date time column into two separate columns for date and time 

Open your sheet with Airtable data and paste the following formula to the L2 cell:

=iferror(
   Arrayformula(
    SPLIT(D2:D," ")
   )
 )

D2:D – the date time column

You’ll get to separate the columns for the date and time. Name them and apply the relevant number format.

Step 2: Concatenate the teachers’ and students’ names in a separate column

We want the lessons to be displayed on the weekly schedule in the following format:

{teacher’s_name}<>{student’s_name}

The CONCATENATE function will help us with that. Apply the following formula to the N2 cell:

=concatenate(I2,"<>",H2)

Then drag the cell down to apply the formula for other cells as well. 

Step 3: Connect the Weekly Schedule Template with Airtable data

Copy the following formula and paste it into the B5 cell:

=IFERROR(FILTER('Airtable data'!$N$2:$N, 'Airtable data'!$L$2:$L=B2,'Airtable data'!$M$2:$M=$A5$,'Airtable data'!$C$2:$C="Confirmed"))
  • 'Airtable data'!$N$2:$N – column with the concatenated name for the weekly schedule 
  • 'Airtable data'!$L$2:$L=B2 – filter of the Date column by the date in the weekly schedule
  • 'Airtable data'!$M$2:$M=$A5$ – filter of the Time column by the time in the weekly schedule
  • 'Airtable data'!$C$2:$C="Confirmed" – filter of the lessons by the “Confirmed” status

Learn more about filtering in Google Sheets in our FILTER Function Tutorial.

Drag the B5 cell towards the right to apply the formula to other columns with days of the week. 

Oh, we’ve got the first match on our weekly schedule! That means we’re moving in the right direction.

Now, let’s make a tiny modification to the formulas in cells B2 to H2:

  • use $B$2 ($C$2, $D$2, and so on for other columns) instead of B2. This fixes the date value and it won’t change when we copy the formula to other cells in the column.
  • use A5 instead of $A$5. This unfixes the time value, so it will change when we copy the formula to other cells in the column. 

Copy the modified formulas to the cells with different times per column. That’s it. Let’s check out how it looks and, most importantly, how it works.

To wrap up:

Is anyone still reading this? This blog post is quite long, but it can provide value to the real seekers. If you’re one of those, please leave your feedback below so we can improve our content. If this blog post is not what you’ve been looking for, check out other articles on the Coupler.io blog – perhaps you’ll find what you need. Good luck with your data!

Back to Blog

Access your data
in a simple format for free!

Start Free