How to Use Google Forms to Track Your Writing Sessions

This post is the seventh in a series designed to help you write your academic book during the semester. In previous posts, I’ve covered how to find time for the book using the system I call the container routine. Then, I showed you how to implement your container routine each week.

But putting time for your book on your calendar is only one piece of the puzzle. The next important piece is finding objective ways to track how you’re doing.

In previous posts, I’ve shown you how I use Excel to track my own writing sessions, and discussed why opening and closing routines are crucial to sustained writing. In this post, I put all three pieces together by showing you how to make an “opening routine” Google Form, a “closing routine” Google Form, and a tracking spreadsheet that automatically calculates what you’ve done.

The Opening Routine Form

In many ways, using Google Forms are easier than an Excel spreadsheet because each response is automatically timestamped. I’ll show you why this is amazing below. But, for now this means that you don’t need to enter date and time. All your opening routine form needs is a spot for you to type in your actionable session goals, like this:

Opening Routine Google Form

Enter those, click “submit,” and get writing!

Don’t know how to set up Google Forms? No worries!

Download your own copy to your Google Drive.

Once you download it, it’s your own private copy–I won’t see anything, and you can edit it as you please.

How to Fill Out Your Opening Routine Form

Once you have your own copy of the Opening Routine Form in your Google Drive, click on the “Send” button in the upper right-hand corner.

Google Form Send Button

Then, click on the link icon to get your own form URL.

Link Icon Google Form

If you’d like, you can get a “short” URL by clicking on the “shorten URL” checkbox.

Short URL Google Form

Copy the URL by highlighting it and clicking Command (or Control) + C, or clicking on the “copy” button.

Copy Button Google Form

Now, save the URL somewhere. Or, you can paste it into your browser’s address bar and bookmark it, since you’ll be using it frequently.

Your Closing Routine Form

Here, you will likely want to customize your form to reflect what variables you want to track over time. But, as I discovered first hand, trying to measure every single variable possible is a recipe for disaster. So, especially if you’re starting out, keep things simple. Track what you accomplished, how easily you started, your focus, and your actionable goals for your next session.

Closing Routine Google Form

If you want to better understand your own challenges, you can also create a field for that.

Download your own copy to your Google Drive, where you can edit it to fit your needs!

How to Fill Out Your Closing Routine Form

Follow the instructions for “how to fill out your opening routine form” above.

See Your Responses

You can see all your responses by opening your Google Drive or going to forms.google.com, which is where you can see all your Google Forms, specifically.

Google Drive

Click on the form you want to open, which will open the “editor” pane. Here, you can change the form (add/change questions, create sections, etc.). But you can also see your responses by clicking “Responses.”

Opening Routine Editor

There, you’ll be able to see all your responses. But you’ll also be able to set it up to send its data directly to a “linked” Google Sheet. Click on the three dots in the upper right-hand corner to set this up.

Google Form All Responses

Click “Select Response Destination” from the menu.

Then select “Create a New Spreadsheet” and click “Create.”

Google Form Create a New Spreadsheet

Setting Up Your Magic Tracking Spreadsheet

This is where the Google Form method is really amazing. Head back to your Google Drive, and locate the spreadsheet you just created. Right now, it doesn’t look like much. But in a few minutes, it will give you some interesting data.

You’ll notice that your sheet’s tab says “Form Responses,” and that a timestamp column has magically appeared. This is how you’re going to get your date and time information.

Sheet with Linked Form

Now, go back to your Google Drive and locate your closing routine form.

Click on “Responses” and the three dots to select a response destination. But this time, do not “create a new spreadsheet.” Instead, you will “select existing spreadsheet” and click “select.”

Select an Existing Spreadsheet

Choose the same spreadsheet where your first form’s data is being sent.

Selecting the spreadsheet

Now, open the spreadsheet again, and you should see two tabs–one for each “linked” form.

Spreadsheet showing two linked forms

Now, you’re going to click on the “+” to create a new tab for your tracking spreadsheet. Name it whatever you’d like.

Create the following columns:

  • Date
  • Date 2 (this is to make sure that your responses are matched up)
  • Day
  • Week
  • Start Time
  • End Time
  • Focus
  • Start
  • Time Elapsed
  • Mins Elapsed

How to Set up Your Spreadsheet Formulae

Date

Type “=”, then click on the tab for your opening routine, and the cell for the timestamp of the first row of data, and click enter. Your resulting formula should look something like this: =’Form Responses 1′!A2

Date 2

Type “=”, then click on on the tab for your closing routine, and the cell for the timestamp of the first row of data, and click enter. Your resulting formula should look something like this: =’Form Responses 2′!A2

You want these to be dates, though, not dates and times, so select the cell, and click on “Format” above.

Format Google Sheets

Then, select “Number” and “Date” from the drop-down menu.

Number and Date

Note: If these are NOT the same, then your rows from your Google Form-linked spreadsheets are off. Either you did a closing routine but not an opening one, or you did an opening routine but not a closing one. Delete the data from those forms to sync them up.

Day

This is handy so that you can see how your writing time, focus, etc. varies by day.

Copy and paste the following formula, assuming A2 is your first row of data (adjust the cell, if not):

=if(count(A2)=1, TEXT(A2, “dddd”), iferror(1/0))

You should now see the day of the week printed.

Week

This is handy so that you can see how much time you’ve spent in a week, and compare weeks to each other.

Use the following formula as a template:

=if(count(‘NAME OF YOUR OPENING ROUTINE SPREADSHEET GOES HERE’! CELL OF THE FIRST TIMESTAMP)=1, WEEKNUM(‘NAME OF YOUR OPENING ROUTINE SPREADSHEET GOES HERE’! CELL OF THE FIRST TIMESTAMP), iferror(1/0))

Mine is:

=if(count(‘Form Responses 1’!A2)=1, WEEKNUM(‘Form Responses 1’!A2),iferror(1/0))

The result should be a number between 0 and 52.

Start Time

This will turn the timestamp of when you submit your opening routine into your “start time.”

Use the following formula as a template:

=TEXT(‘NAME OF YOUR OPENING ROUTINE SPREADSHEET GOES HERE’! CELL OF THE FIRST TIMESTAMP, “hh:mm”)

Mine is:

=TEXT(‘Form Responses 1’!A2, “hh:mm”)

This should print a 24-hour time.

End Time

Same as start time, except replace your opening routine timestamp with your closing routine timestamp.

Focus

This will allow you to see how your focus varies over time. Are you consistently more focused on Mondays? In the afternoon?

To populate it, simply type “=” and then click on the cell on your closing routine tab that has the first day’s focus information. (e.g. =’Form Responses 2′!G2)

Start

This will allow you to see how the effort it takes you to start varies over time. Do you start more easily in the morning?

To populate it, simply type “=” and then click on the cell on your closing routine tab that has the first day’s start information. (e.g. =’Form Responses 2′!H2)

Time Elapsed

This will allow you to see how much time you’ve spent in each session. Use this formula template:

=TEXT(CELLOFENDTIME-CELLOFSTARTTIME, “hh:mm”)

Mine is:

=TEXT(F2-E2, “hh:mm”)

Minutes Elapsed

This will allow you to see how many minutes you’ve spent per session. If you’ve set your sheet up the same as mine, you should be able to use the following formula:

=if(count(A2)=1,I2*1440,iferror(1/0))

Data Trends and Analytics

Using the above data (pulled from your Google Forms), you can also see trends. For instance, you can:

  • See how many minutes you average on the different days of the week. To do this, use the “Averageif” formula. If you’ve set your sheet up the same as mine, the formula =Averageif(C:C,”Monday”,J:J) should give you an average (in minutes) of how long you write on Mondays.
  • See how focused you are on a given day. Here again, use the “Averageif” formula. If you’ve set your sheet up the same as mine, the formula =Averageif(C:C,”Monday”,G:G) should give you an average (in minutes) of how you focus on Mondays.
  • See how many total hours you’ve written on any given day. To do this, use the “Sumif” formula. If you’ve set your sheet up the same as mine, the formula =SUMIF(C:C,”Monday”,J:J)/60 should tell you how many hours you’ve written total on Mondays.
  • See how many hours you’ve racked up per week. To do this, first type the week numbers in a column, like this:

Week Numbers in Spreadsheet

Then, next to the first number, use the SUMIF formula like this:

=SUMIF(D:D,CELLOFFIRSTWEEKNUMBER,J:J)/60

In mine, I typed the week numbers in cells N10, N11, and N12, so my formula is:

=SUMIF(D:D,N10,J:J)/60

Your Turn

Interested in using Google Forms to track your writing sessions? Think they might keep you more accountable than Excel spreadsheets?

Let me know what questions you have in the comments below or by email.

Humanities First Book Author Inner Circle

Writing your first academic book in a humanities or qualitative social science discipline? Wondering how to manage such a large project? You don't have to struggle alone! Sign up, and I'll send you resources and advice to help you get a handle on your manuscript, find the best publisher, and develop productivity habits to get the book done.

I won't send you spam. Unsubscribe at any time. Powered by ConvertKit