Use Google Docs to Manage your Digital Projects, from Freelance to Large Agency

In July 2012 I launched 3 Door Digital alongside my three co-founders. The launch was part of a merging process between two existing companies (Pleer, based in Manchester, UK and Matan Media based in Tel Aviv, Israel). Along with the merger came some potential operational hazards that we had to make sure we were on top of prior and post launch. Part of my responsibility was to find and apply the best project and time management process for our campaigns moving forward.

In the past few years I have used numerous task management tools and decided to put a few to the test to see which one would work for us. In the end we found that Google Docs was actually the best platform for us to use. This may seem like an uncommon choice but with the correct setup it has proved to be extremely successful.

I’m going to share how we manage to stay on top of over 30 client projects at any one time whilst making sure we don’t miss tasks or lose track of the hours we’ve spent.

Before covering all this, there was a second choice…

In the end we had a decision between two platforms – Basecamp and Google Drive (formerly Google Docs). Basecamp was great for us but there was something missing (which I’m sure will eventually be added at some point) – the ability to quickly browse tasks, task owners and time management all in one easy-to-read page. This is where Google Docs won. Don’t get me wrong – Basecamp is a fantastic choice for some companies – it just wasn’t the best solution for us.

So, there were a few reasons why we chose Google Docs and I’ll go through why, feature by feature. As well as this I’ll share a template document that you can copy as your own to tweak as you wish.

Google Docs Spreadsheets FTW

Before 3 Door Digital was born, I had two concurrent jobs: the Head of SEO at a search agency in Manchester and another running my own company with my wife and business partner. Because, at the time, there were only two of us it was quite easy to keep track of each other’s work using an Excel document within a Dropbox shared folder. Once 3 Door Digital started trading, it became obvious that this wasn’t going to be the best solution.

To start, I simply uploaded the spreadsheet template I’d been using for Pleer into Google Documents and added the additional clients from Matan Media into the new 3 Door Digital template. Simple!

“Simple” I thought – it wasn’t so simple. Although I was well aware that I was now planning out 20+ retainer clients, 10+ one-off clients as well as other internal tasks. It was time to roll out the “super template” that would make it easy to navigate these 30+ clients whilst being easy to read for all consultants, account managers and directors. Most of all, it had to be workable so that tasks were not missed out (the main personal downfall of mine for Basecamp’s GUI).

The Super Template

Marc and I took a day out to create this super template by discussing how both teams work between offices in two countries on various tasks. Each row would represent a different task, no matter how small. What we needed to do is select columns. For each task, we covered the following:

1. Client Name
2. Platform Name (because some clients have various sites and platforms, some of which may have different account managers)
3. Account Manager
4. Task Name
5. Owner (if not the account manager)
6. Deadline
7. Budgets
8. Actual Hours Undertaken
9. Completed (this uses a tick, exclamation point or cross – more on this later)
10. Notes

The “Summary Row”

Each client and platform is split by a few empty rows and a total time allowance is provided within a chosen “Summary” row. In this row we can use equations to calculate total hours spent on a platform and time left for the month. Scripts for time management are then applied in order to enable email notifications (covered later in this post).

Using other Tools to Compliment Google Docs

Even after all this, we were well aware that more information was needed dependent on the task. There is some information that you simply can’t illustrate well in a spreadsheet. Because more information is sometimes needed, or at least you need a separate area to work with for a specific task, we used other online collaborative tools and resources to help us with specific tasks – using the notes column as the point of reference.

Before Going Elsewhere, use the Comment function

This feature is easy to use. Right click a cell and you can add a comment. You can tag a user of the doc with the @ or + sign, and they will be notified by the email and directed straight to the comment itself. Once commented, a small orange label appears in the top right of the cell. Hovering over that cell shows the comments as a popup.

You will also notice a “Resolve” button at the top right of the popup. Clicking this, tags the comment thread as resolved, removes the orange marker, and hides the comments from the cell itself. If you want to access all comments within the document you can access them by clicking the Comments button at the top right of the screen (next to the Share button):

Use other Google Docs

Sometimes the commenting feature is never going to cover everything that needs to be covered. Some projects may need a separate document or spreadsheet or have more dialogue with more limited access. Let’s say for example that this whole task document is viewable to the whole company but one task only the Account Manager and one Director want to see details of this specific task. Here, we would create a new document and only give the Account Manager and Director access rights. In the task sheet you simply link to that separate document.

For Even Larger Tasks that Require a Folder Structure

Sometimes a task or project needs its own file and folder structure consisting of a lot of different types of files. Within the company we use Dropbox for collaborative filesharing. We used the share option within Dropbox to link to the specific folder or noted down the folder location for anyone to access. Here’s an example of where an image is entered within the notes column for other people to click and view:

Tasks that Require a More Visually friendly UI

One example of this could be a web build. We use Trello – a fantastic task management tool that I have been personally using for years and can’t recommend enough. I don’t want to talk about Trello in this post, however, sign up (it’s free), and get to know it well – and be comforted in the knowledge that they have an iPhone, Android and Windows 8 app and are currently developing their iPad app.

Paddy Moogan of Distilled wrote a great post about using Trello to Manage Projects for SEO – go and check it out.

Conditional Formatting FTW

I’ve never been at a level with spreadsheet production that I would call myself advanced, but when I learn something new in it I realise how versatile it is and how it can display information. One thing I’m a fan of is colour coding as I like to see the status of multiple tasks without always reading each row in any detail. To achieve this, I used conditional formatting in two columns:

Completed

For completed tasks I knew that there were only three options that we would need to choose:

1. Incomplete
2. Hurdle
3. Complete

The “Hurdle” option is when something outside our control is stopping us from completing the task. When this happens, we add notes into the notes column where we can make efforts to complete the task as soon as we can. Using conditional formatting I have made the three different statuses with three different backgrounds for us to easily identify. As well as this, we used the Data Validation option to ensure that only these three option can be selected by a dropdown. This is set by right clicking the column/cells and selecting Data Validation. We used the following configuration for status:

This then allowed us to select an option rather than type the words manually. This is done when you select the cell and you will notice a dropdown option that opens a popup for you to make the relevant selection:

Originally in Microsoft Excel, I used a different system whereby each task is set to 0 by default for incomplete, 1 for hurdle and 2 for completed. In Microsoft Excel (version 2010 or newer) you can use conditional formatting using the tick/exclamation/cross Icon Set, which is what I use when not using Google Docs (if you work for Google and you’re reading this please forward that as a feature request ;)). This is the specific conditional formatting we used for the Status Column:

Deadline

This column used more sophisticated conditional formatting. We wanted to have different colour coding dependent on how many weeks or days there were to the task’s deadline. These were as follows:

• If more than a week away, we’re good
• If within the next week, turn orange
• If within the next 2 days, turn red
• If in the next 24 hours, turn even deeper red
• If deadline has passed, turn black

This conditional formatting is somewhat limited in Google Docs when it comes to timescales (another feature request :P) so we used the conditions above, the setup of which looks like this:

Keeping the Document Tidy

After a while you may find that the document becomes crowded. To eliminate this, you could hide rows where the tasks are confirmed as completed. Hiding a row (different than deleting a row) means that all data is still stored for future reference; as well as it keeping other formula data such as time management.

We choose not to hide rows, as we personally like to see all tasks regardless of its completion. Instead, we split our tasks into worksheets separated by month. At the end of each month, we copy the existing month’s worksheet as a new worksheet and delete all completed rows. This then leaves us with only incomplete tasks, which are then added to as the month progresses. If we want to refer to a previous month, we simply refer to the relevant month’s worksheet. This way we can easily see every task over the course of the whole year.

Getting More Technical with Time Management Scripts

One thing I found that Basecamp lacked apart from its UI was the lack of time management connectivity. Relevant Managers want to be notified via email if a client is running out of management time for that month. Within the email we can include information from anywhere else in the spreadsheet. In our example, we have used the Client Name and the amount of hours left.

If this method isn’t your cup of tea, other online tools such as Toggl and Harvest will help you with time management.

Managing Account Hours

To begin, let’s use a sample client. This client has three platforms and has a number of tasks within each platform. Each platform therefore has its own set time allowance for the month. We now have the following time data:

• Total allowance for the month
• Total estimated time for each incomplete task (if you want to be that detailed)
• Actual Hours Undertaken

We use this data to perform the following equation to find how many hours left there are for a platform:

Time left=SUM(Allowance-(SUM of all actual hours undertaken))

Here’s one example:

Column I, the number of hours left, is updated as the document is edited and is the number we need in order to see how many hours are left for the month. What we needed was a way to be emailed once this approached a low number.

Creating Scripts

David Sottimano of Distilled shared a script with me that emailed a chosen recipient if the number of a specific cell reached a specific number. From here, I took that script and integrated it into our Task Document.

The script runs every time the document is edited (or opened, depending on how you configure it) and is triggered if the “Time Left” figure for the platform reaches 2 or less than 2 (i.e. 2 hours). Once triggered, the script collects the Platform name and emails the person responsible for receiving the alerts with an email that the platform has run out of account hours. To install the script you will need to configure

• The column that contains the Platform name
• The column that contains the “Time Left” formula
• An integer that triggers the alert (we chose 2)
• The email(s) to send the alert to
• The column to set the notification integer (otherwise you’ll get a LOT of emails :P)

To include the script, you can use the script manager tool and set the trigger in there:

And here is the script itself (highlighting the five variables you can configure above):

function notifyManagement() {
// Update & Edit values below ###############

var testing = false; // false = Live emails … true = Browser popup…

// The Column for the task name
var taskColumn = “B“;

// The Column for Time Remaining
var timeLeftColumn = “I“;

// Number of Hours (less than X) that you want to be notified
var HourTrigger = “3“;

// Column for notifications
var managementNotifiedColumn = “O“;

// Emails addresses in array format [”,”,”] var managementEmails = [“[email protected]“,”[email protected]“];

// Stop Editing values here ##################

var sheet = SpreadsheetApp.getActiveSheet();
var lastRow = SpreadsheetApp.getActiveSheet().getMaxRows();
var row = 0;

// loop through the timeleft column
for(row=1; row < lastRow; row++){
var cell = timeLeftColumn + row;
var managementCell = managementNotifiedColumn + row;
var taskCell = taskColumn + row;

// let us get all the values ready for checking
var timeLeft = sheet.getRange(cell).getValue();
var notified = sheet.getRange(managementCell).getValue();
var taskName = sheet.getRange(taskCell).getValue();

//check to see if we should send an email
if ((timeLeft < HourTrigger) && (!notified) && (isInt(timeLeft))) {
// Set the notified cell
sheet.getRange(managementCell).setValue(“1”);
// email management
for (var i = 0; i < managementEmails.length; i++) {
if (testing) { Browser.msgBox(“Email to ” + managementEmails[i] + “n” + taskName + ” time managementccccss warning!”); }
else {
MailApp.sendEmail(managementEmails[i], taskName + ” time managementccccss warning!”, taskName + ” has ” + timeLeft + ” hours remaining”);
} // end testing check
} // end of send emails loop
} // end of checking if we should notify
} // end of for loop of all rows
} // end of mailMe function

function isInt(n) {
return typeof n === ‘number’ && n % 1 == 0;

}

Internally, we also created a similar function to email us when budgets were too low using the same template as above and changing the variables at the beginning to focus elsewhere in the document.

Triggering the Script

Once you’ve created your script you can set triggers for each one. To do this, select the “Current script’s triggers…” within the script editor:

Here, we have decided to trigger the scripts on edit so we have live notifications sent to us and not just when the next person opens the document:

Too Much for You? No Problem!

This post either may be too technical for you, or will take you too long to implement. That’s fine – I’m a nice guy. I’m a guy who’d be kind enough to share a canvas template for you with the script installed, conditional formatting set up, scripts running (with a fake email) and even included some sample data 🙂

Known Bugs

Sometimes we have found when adding rows or editing the doc with new information, some of the rows or columns begin to look messed up. A simple refresh of the document usually fixes this and your updates should be all good!

An Added Note

This is an awesome document and works for us better than anything else we’ve used, but this may not be for you or your agency. We all have our own preferences, although I am pretty sure at some point we may need to invest in our custom management toolset.

This doc isn’t the only thing I use to manage my own time – I also have my own personal Trello board and can’t live without the traditional email inbox to keep me from going insane.

I’d love to know what other tools and resources you use for your own project management…