Switching to Google Sheets How one government agency doubled their productivity

 

What’s the first word that comes to mind when you think about government?

Probably not innovation.  Probably not “Masters of Google Sheets.”

Prepare to have your mind blown.

Here at CIFL, we’ve seen every type of Google Sheets + Google Data Studio implementation under the sun.

But none of them hold a candle to what we saw at the Department of Primary Industries, an Australian state agency based in Sydney, New South Wales.

They switched to Google Sheets for project management about a year ago, and have seen their performance as a team (measured in on-time tasks) double.

That’s double with a D: from a 40% to 80%.

For readers in the United States, the Department of Primary Industries (DPI) is like if the USDA and EPA had a baby.

They manage recreational (fishing + hunting) and agricultural (farming + livestock) projects throughout the state, and a part of their mission is providing advice on environmental assessments for major projects.

We recently spoke with Graeme White, the Manager of Assessments at NSW DPI, to learn how they pulled off this miracle of Google Sheets magic.

Move fast, don’t break things

It’s important to note how Graeme and his team went about switching to Google Sheets.

Since their parent agency, the NSW Department of Industry, already had G-Suite and allowed staff to use it, they didn’t have to go through any formal process or red tape.

They just did it and learned as they went – which they couldn’t do if they had chosen to implement a dedicated project management tool.

As Graeme put it:

If you constrain people too much by putting rules around how people do things, you’ll never innovate.  Ask for permission afterwards.

They also had no risk of breaking an existing system, so they could innovate on the fly.

You’re never more free to innovate than when you *first* switch to Google Sheets.

Prioritize and execute

Shiny objects, like Google Sheets, tempt us to bite off more than we can chew.

We can do this and that with it?  Let’s do it all!

But when Graeme took his post at DPI in 2016, he was confronted with a list of 700 assessment projects in a Google Sheet.

He wasn’t sure whether they were on-time or late on any given task.

What gets measured, gets managed – so Graeme knew that basic tracking of project statuses had to be their first priority.

None of the off-the-shelf software tools were suited to their workflow, so they decided to roll their own in Sheets.

They started there, and let the next priority make itself obvious when the time was right.

Training is your best friend

Before we dive into what Graeme and his team built, let’s all say it together (out loud if possible):

If you drop a new Google Sheet onto your team, and expect them to use it, you’re setting yourself up for failure.

When rolling out a new approach on your team, the biggest risk (by far) is that it doesn’t stick.

Prayer isn’t an effective form of training.

As Graeme’s team started picking up Google Sheets, he got tons of questions – mostly relating to the switch from Excel to Sheets:

Spending dedicated time on training sessions helped them get over these adoption hurdles.

They also started using an internal Google+ page for sharing broader G-Suite tips throughout the 7,000-person organization, allowing for knowledge to flow beyond just the Assessments team.

Before you build anything or make the switch to Sheets, budget a healthy amount of time to training + education.

What they built first

OK, back to the problem at hand – Graeme’s team managing their projects in Sheets.

Any useful project management tool does 3 jobs:

  1. Stores a database of tasks and current statuses
  2. Reminds people when their work is due
  3. Reports on team-wide performance

 

To keep things simple, Graeme and his team split each job into a separate Google Sheets tool.

Taking this microservices approach to rolling out Google Sheets is a *super smart* move – we’ve seen many teams get bogged down in overcomplicated Sheets.

The master project database

DPI’s entire approach depended on logging the entirety of the team’s tasks and statuses in one Sheet.

Without that working reliably, everything would fall apart.

They made a few really sharp decisions to make things easy on the team:

Hide Irrelevant Data

Graeme and team gave each division of the agency their own task tracker Sheet, so they only saw the tasks that they are responsible for completing.

That way, people only see the data that they need, and don’t have to swim through other people’s projects to see their own.

Color-code with conditional formatting

Their task tracker came alive with color – so one look at the Sheet would alert you to the problem tasks in the pipeline.

Major bonus points for usability.

bq2m1oO8U4tuMmowsuTZuB TVcT4B225RScwb8aqapPk15UPwoKeOksJe4gaHOjDYDqB3g4KPGsDXln2P y8ZvLdkCYs2048

Organize tabs around problem areas

They used the Query function to build mini reports within the task tracker.

So if you wake up in the mood to put out fires, you know exactly where to look for them.

st52Pc3xXLX7anQfN6ES NEScLRiYCPNgKt7qOjHqQikvumY4pTpeXocRjTXj9FDaY c5HFxS694VlzyfylbKDDzwHMs2048

Using Sheets for communication

Getting all of their tasks being tracked consistently in Sheets was really just half the battle.

It means nothing if people forget to update their projects.

But Graeme’s team doesn’t live in this Google Sheet – they’re busy doing their jobs.

So Graeme leaned on Google Apps Script for reminders and notifications, to gently nudge the team about their upcoming projects.

Calendar invites

The second Sheet of their toolbelt did one simple job: sent Google Calendar invites to team members for their upcoming projects.

When they checked out the calendar for a week, they’d know exactly which projects they have due, without having to log into the project management Sheet.

Email reminders

He also configured summary emails to be sent once a week with Apps Script, which reminded each team member of the projects they had due in the next 14 days.

Who said redundancy was wasteful?

Put it all together in Data Studio reports

Once their Google Sheets process for tracking tasks was flying, it became important for everyone on Graeme’s team to know how this process was improving performance.

Because if it wasn’t helping them do better work, what’s the point?

Just like their redundancy in notifications, they leaned on two different methods for communicating results.

Dashboard reporting in Data Studio

Back in the dark ages, before Google Data Studio launched internationally, Graeme and co. relied on simple Google Sheets Queries to see how they were tracking week-over-week and month-over-month.

They built a handy dropdown report that would pop up reporting snapshots in Sheets:

f3HH5vA3sb9j4sKPXFm9Lep 20wamxGVH4wN6c5tBVbgLk JcXzrS9 2sGHi2dtPAOWFo3VgljcFoT5OdrtVsjyfg0s2048

But, as soon as Data Studio went live for them, they took advantage and moved their  reporting over to it – making for a much more attractive dashboard:

E h2WIXOrZ0LKKnm1sEznDoo6xanxmvILgJkpdpBXLWYM0VeKi9LSQ4HA Q3CzGkzPTqKVN9n9220G3c6CBnWW9dRTcs2048

That’s right: this government agency was among the earliest adopters of Data Studio.

Monthly newsletters

Not everyone on a team needs to be looking at reporting every day – but it’s still helpful to be in the loop occasionally.

Their solution: a monthly newsletter (sent via Mailchimp), that transparently shows the team where they’re winning and losing.

Sheets is for teams, Excel is for _______

It’s impressive how they designed all of these seemingly data-related problems around the people on the team.

Since Google Sheets (unlike Excel) is a collaborative tool, migrating to it is not about the tool itself.

It’s about molding the tool to your team’s preferred way of working together.

For Graeme’s team at DPI, they molded their switch around:

  • Reducing technical anxiety through basic Sheets training
  • Rolling out a simple, core tool (the project tracker) first
  • Reminding everyone to use it in redundant ways (calendar, personal email, newsletter, dashboard reporting)

Their rollout of Sheets was designed up-front to be robust, and outlast any individual member of the team.

Because they know that 1 year, 5 years, 10 years from now, they will very likely be doing the same work.

The government does not go out of business.

And if you take a solid approach to your switch to Google Sheets, you likely won’t either.

2 Comments

  1. This is super awesome, and helpful to see here how you laid out the three categories: 1) Database of tasks 2) Reminders 3) Reporting

    Problem: I created a Master Tasks List that assigns tasks to 3 employees, who each have their own Employee Sheet that pulls in their tasks with the query function. Problem is, since query isn’t really a bi-directional sync, they can’t update that Employee Sheet and have it send the status back to the Master Task List, so what they do is hand copy their tasks to a new tab in the sheet which then feeds back into my Master Tasks List with the query again.

    It’s pretty close, but wondering if there’s a better way?

    • A classic problem Joe – the way we’ve gotten around that in the past is using Google Apps Script + the onEdit function, to log changes to the Master sheet whenever someone changes the status in their own Employee sheet.

      Drop me an email and I can help you set it up.

Submit a comment