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:
Enter those, click “submit,” and get writing!
Don’t know how to set up Google Forms? No worries!
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.
Then, click on the link icon to get your own form URL.
If you’d like, you can get a “short” URL by clicking on the “shorten URL” checkbox.
Copy the URL by highlighting it and clicking Command (or Control) + C, or clicking on the “copy” button.
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.
If you want to better understand your own challenges, you can also create a field for that.
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.
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.”
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.
Click “Select Response Destination” from the menu.
Then select “Create a New Spreadsheet” and click “Create.”
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.
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.”
Choose the same spreadsheet where your first form’s data is being sent.
Now, open the spreadsheet again, and you should see two tabs–one for each “linked” form.
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 2 (this is to make sure that your responses are matched up)
- Start Time
- End Time
- Time Elapsed
- Mins Elapsed
How to Set up Your Spreadsheet Formulae
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
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.
Then, select “Number” and “Date” from the drop-down menu.
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.
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.
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))
=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.
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”)
=TEXT(‘Form Responses 1’!A2, “hh:mm”)
This should print a 24-hour time.
Same as start time, except replace your opening routine timestamp with your closing routine timestamp.
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)
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)
This will allow you to see how much time you’ve spent in each session. Use this formula template:
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:
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:
Then, next to the first number, use the SUMIF formula like this:
In mine, I typed the week numbers in cells N10, N11, and N12, so my formula is:
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.