Did you know Google Sheets brings a host of powerful features to users? These features automate repetitive tasks, including enhancing styles and splitting names. Here are some of my favorite problems and solutions for Google Sheets and Forms.
First, let’s take a look at one approach to problem-solving. Problems present opportunities to fine-tune our thinking. But often, we lack a process to follow. I guarantee that it will streamline your thinking for solution development. What’s more, it may save you time when it matters most.
The Power of Heuristics
Heuristics are powerful processes or checklists that do the following:
- Remove some of the burden on our minds to make a decision
- Assist with problem-solving
- Make simple a tough question with many moving parts
- Speed the journey to an on-target conclusion
Why wouldn’t you rely on a heuristic for solving spreadsheet problems? They help us learn by our own actions. What does that mean? Let’s dig in a little more.
Learn by Your Own Action
The power of heuristics is that they empower you to solve problems. They do this by providing a checklist– a process you can follow.
We all know that solving problems can be challenging. If you have to put pen to paper, it’s nice to have a process to guide you and aid your thinking. That’s why the heuristic shared on this page can be helpful.
George Polya, a mathematician, put it together. And this approach draws its inspiration from Socrates.
“We all must add the action of your own mind in order to learn something. Socrates expressed it two thousand years ago. He said that an idea should be born in the student’s mind, and the teacher should act as a midwife.
The idea should be born in the student’s mind and the midwife shouldn’t interfere too much, too early. But if the labor of birth is too long, the midwife must then intervene. The student learns by his own actions.” (Source)
Polya’s heuristic offers a four-step process for solving a problem. Let’s review it with a solvable spreadsheet problem in mind.
Skip the Google Sheets frustration! Sign up for the TCEA Google Sheets online, self-paced course.
Step 1 – Understand the Problem
Polya suggests one take the time to understand the problem. He suggests exploring the meaning of unfamiliar vocabulary terms. He also says to identify what’s given in the problem, including superfluous information. Finally, he recommends restating the problem in your own words.
Example Problem
Here’s a problem I have had to solve using Google Sheets and Google Forms:
I need to set up online professional development and track how many people sign up, represent that in graph form, and set up the form so that participants get digital certificates upon submission of the a form.
With that problem in mind, you can move to Step 2 – Design a Plan for Solving the Problem.
Step 2 – Design a Plan for Solving the Problem
In this step, you decide how you will work on the problem. There are several strategies you can use. Here’s a list of those in order of my preference:
- Draw a picture or diagram.
- Make a list.
- Break problems up into a series of smaller steps or problems.
- Solve smaller versions of the problem and look for a pattern.
- Use variables and write an equation.
Example Plan for Solving the Problem
As you can see, my preferred approach is to draw a picture that breaks the problem into a series of smaller steps. Here’s what that looks like on paper:
For me, the smaller steps involve a flow (what is first, what is second, etc.). I see them in this way:
- Set up professional development tracking with a Google Form.
- See how Form data appears in Google Sheets Responses.
- Summarize the data in Google Sheets Responses with a Status tab in the spreadsheet.
- Capture statistics for completion on a separate Google Sheet that aggregates results. Those results come from a wide variety of Google Sheets Responses. Those Sheets appear as their own document or as tabs in one Google Sheet (preferred).
Once I clearly understand how things will flow, I carry out the plan in Step 3 – Carry Out the Plan.
Step 3 – Carry Out the Plan
In this penultimate step, you spend a reasonable amount of time trying to solve the problem. For me, twenty minutes to an hour is reasonable. I have found trying to work on a problem longer than twenty minutes has diminishing returns.
Research suggests working in small chunks of time. Punctuate each chunk of time with rest periods in between. Doing this can help with focus.
Our attention tends to wane after a certain period. How long that time period lasts depends on the person. Some studies…place the range anywhere from 10 minutes to 52 minutes. (Source: Harvard Health Publishing, 2020)
There are a few problems I have had to spend several days working on, taking breaks between each work period. During each chunk of time, I try a new strategy or unfamiliar function to try to get the result I want. This may involve going online to watch videos and solutions others have provided. In tough problem-solving situations, I have found chatting with a colleague helpful. Trying to explain the problem, my solution aloud assists me in troubleshooting.
This way, I troubleshoot my solution when explaining what I’m doing to others. It’s rare that others will have a solution they have developed. I don’t want them to own the problem, but I want them to let me own it and clarify my thinking process.
Google Sheets and Forms make it easy to put plans in place, test the solution often, and try new variations.
Step 4 – Look Back, Reflect and Assess
You already know the value of self-judgment and reflection. Polya suggests you check your solution with a few questions:
- Is it reasonable?
- Is it unique?
- Can you see an easier way to solve the problem?
- Can you generalize the problem?
For me, the hardest part is making the jump after understanding the problem. That jump involves translating the problem into a mathematical version of the problem.
Example Reflection
Most plans fail in their execution. However, I’m happy to report that this plan worked well. Over twenty thousand people have gone through the process and received certificates. They get those certificates emailed as a PDF. One problem that arises is that sometimes Autocrat freezes. This means that the end users don’t get their certificates right away. That is something that I have to try and improve.
Sample Problems and Solutions
Need to see some example problems and solutions? Here are a few actual ones I’ve documented while working with Google Sheets and Forms.
- Create a parent communications log (See more)
- Set up a travel budget (See more)
- Collect campus-specific data and organize it in a summary-type Sheet that then feeds into a data dashboard showing all campus data (See more)
- Send out custom bulk emails using a list of names and email (See more)
- Translate comments submitted via a form (See more)
- Capture tweets from Twitter and run analytics on stats (See more)
- Pull related data from several sheets into one report (See more)
- Generate PDF certificates to send via email to participants (See more)
- Create a capacity matrix to track completion score (See more)
What’s Next?
Now that you have a process for solving real-life problems with Google Sheets and Forms, what’s next? For me, it’s being aware of the different tools for Google Sheets– functions that you can use in formulas that you design in Google Sheets. Stay tuned for my next blog entry with my list of favorite functions.
Feature Image Source
Screenshot by author