Search results for

"google sheets"

Boost Communication with Autocrat, Google Sheets, and Looker Studio

by Dan Larson April 26, 2024

written by Dan Larson

Communication is the cornerstone of any successful school. It fosters collaboration between teachers, keeps parents engaged, and ensures students feel informed and supported. Yet, navigating the complex web of communication needs within a school community can feel like a constant uphill battle. This is where technology steps in, offering a powerful trifecta to streamline data sharing and revolutionize communication across all stakeholders: Autocrat, Google Sheets, and Looker Studio. Let’s delve into how each tool can empower your school to achieve smoother and more effective communication.

Google Sheets: Organize Your School’s Data

Think of Google Sheets as the central nervous system of your communication strategy. This user-friendly spreadsheet tool offers a collaborative platform for collecting, organizing, and managing all your school’s data in one place.

Here’s how Google Sheets can transform communication:

Consolidated Data Source: Say goodbye to scattered information! Google Sheets allows you to create a central repository for student data, attendance records, grades, and parent contact details. This ensures everyone has access to the same accurate and up-to-date information.
Real-Time Collaboration: Multiple teachers or staff members can work on the same spreadsheet simultaneously, fostering better collaboration and ensuring everyone stays on the same page.
Data Analysis Made Easy: Google Sheets offers built-in data analysis tools, allowing you to identify trends or patterns in student performance or parent engagement. This valuable information can then be used to inform communication strategies.

But what if you could amplify the power of Google Sheets and leverage it for better communication?

Autocrat: Automate Tasks and Save Time

Imagine a world where repetitive administrative tasks are handled automatically, freeing up valuable time for educators to focus on what matters most – their students. Autocrat, a potential game-changer in this scenario, is a tool that automates communication workflows.

What is Autocrat?

Autocrat is a Chrome extension that seamlessly integrates with Google Sheets. It automates repetitive tasks within your spreadsheets, freeing up valuable time for educators. Imagine sending personalized birthday greetings to every student in your class or automatically generating report cards. Autocrat can handle these tasks, and more, all based on the data you have in Google Sheets.

Screenshot by Emily Horn: Example Template with Autocrat Merge Fields from TechNotes article **The Autocrat Add-On: Easily Generate Certificates, Progress Reports, and More**

Here are some ways Autocrat can benefit your school:

Automate Newsletters and Reports: Autocrat can automate the creation and distribution of routine reports like attendance updates, lunch menus, or upcoming events, ensuring everyone receives timely information. Here is a tutorial from Matt Miller’s Ditch That Textbook blog on how to create a newsletter and use Autocrat to send it:

Personalize Communication: It can personalize communication by automatically merging pre-written templates with relevant data from Google Sheets, saving teachers the hassle of repetitive tasks. Imagine sending birthday greetings, certificates, or personalized progress reports to students effortlessly.

Streamline Grading and Feedback: Autocrat can potentially automate the process of sending out grades or feedback. Make a Google Sheets Gradebook, create a grade report template in Google Docs, and use Autocrat to merge and send. This can significantly reduce teacher workload and improve communication with parents.

But what if there was a tool that made reporting and analysing this type of data easy?

Looker Studio: Transform Data into Meaningful Visualizations

Data is powerful, but raw numbers on a spreadsheet can be overwhelming. Looker Studio bridges this gap by transforming data from Google Sheets into clear, visually appealing dashboards and reports. Connect it to Google Sheets and send visual data reports with Autocrat!

What is Looker Studio?

Looker Studio (formerly known as Data Studio) is a free data visualization tool by Google. It connects to your Google Sheets and transforms raw data into interactive dashboards and reports. Looker Studio allows you to present complex information in an easily understandable way, making it ideal for communicating with parents and administrators. Here is a one-minute intro:

Here’s how Looker Studio empowers communication:

Easy-to-Understand Reports: Looker Studio allows you to create interactive dashboards that present complex data in a user-friendly format. Imagine presenting student progress reports to parents with clear charts and graphs, making it easier for them to understand their child’s performance.
Targeted Communication: Tailor reports to specific audiences. Create custom dashboards for teachers to track student progress or generate reports for administrators to visualize school-wide trends. This ensures everyone receives the information most relevant to them.
Data-Driven Decision Making: Looker Studio empowers data-driven decision making. By visualizing trends in areas like attendance or student performance, schools can identify areas for improvement and tailor communication strategies accordingly.

The Benefits

Imagine a Looker Studio dashboard for parents that displays their child’s progress in various subjects using clear charts and graphs. This dashboard would be directly linked to the student’s data in Google Sheets, and Autocrat could be used to schedule automatic updates to the dashboard throughout the semester. This creates a single source of truth for parents, allowing them to easily track their child’s performance.

The true magic lies in how these three tools work together. Google Sheets provides a central data hub, Looker Studio transforms data into clear communication, and Autocrat automates tasks. This integrated approach offers a multitude of benefits:

Reduced workload for teachers and staff.
Improved transparency and communication with parents.
Enhanced student engagement through personalized communication.
Data-driven decision making for school improvement.
Increased efficiency and streamlined communication workflows.

The Communication Trifecta

The magic truly happens when these three tools work together. Here’s a breakdown of the integration:

Autocrat automates tasks: It uses data within your Google Sheets to populate reports, personalize communication, and streamline workflows.
Google Sheets acts as the central hub: It stores all your school’s data in one place, ensuring everyone has access to the same information. Autocrat interacts with this data to perform its automation magic.
Looker Studio visualizes the data: It takes the information from Google Sheets and transforms it into clear and compelling reports and dashboards, perfect for communicating with parents and administrators.

By embracing technology and leveraging the power of Autocrat, Google Sheets, and Looker Studio, schools can bridge the communication gap and foster a more informed, engaged, and successful school community. Remember, effective communication is a journey, not a destination. Take the first step today and empower your school community to thrive.

April 26, 2024 0 comment

Stay Organized and Track Tasks with Google Sheets

by Dr. Bruce Ellis July 19, 2023

written by Dr. Bruce Ellis

When you are juggling many projects, staying focused and on task can sometimes be challenging. Having an effective to-do list will help you prioritize tasks and focus on getting critical ones done while not letting the small but still necessary to-do items fall through the cracks. Originally, I was a Post-it note or index card to-do lister. No matter what day it was, I had at least one Post-it note to help keep me on track. But I found that system works as long as you don’t need to track more than what you are doing today. Using a Post-it note or index card can help in a pinch, but this system can’t help you identify trends on how you’re spending your time. Having an effective system can be a lifesaver when reviewing what has been keeping you busy. It can also be a life support when looking ahead and what items you need to tackle that are coming down the pike. This is where a task tracker comes in handy.

What Is a Task Tracker?

screenshot of Google Sheets Spreadsheet template for Task Tracking

For our purposes, a task tracker is a spreadsheet used to keep us focused on a weekly basis. The spreadsheet has four columns: Priority, Task for This Week, Status, and Value Add. Each week, we duplicate the base sheet and fill it in with the items to be completed.

For each item, we identify the level of Priority for one column:

Priority Levels

Urgent and Important [UI]
Important but not Urgent [InU]
Urgent but not Important [UnI]
Not Important and not Urgent [nInU]

For sorting and filtering, it’s easier to have these as numbers 1 through 4. Having identified the level of priority will help you decide which tasks get your most productive hours of the day and/or most immediate energy.

In the spreadsheet, there is also a column for Status. This column can vary with how you fill it in, but it should include Completed, Delayed, In Progress, Canceled, and other signifiers as you feel they apply. Of course, one column will be the Tasks for This Week with a description or name for each task. And the last column will identify the Value Add. The value add is where you identify why you are doing something. How does doing this item benefit your company or employer? This column will be very different based on your particular role and job duties.

How to Use the Task Tracker Spreadsheet

You can use this template, and when you click, the link will ask you to make a copy to your Google Drive. Once copied to your drive, you will want to edit the spreadsheet to meet your needs. You can edit drop-down menus by changing the validation rules for each cell with a dropdown. Those rules will pop up when you click the arrow for the drop-down menu in a cell and click the Edit icon (pencil) at the end of the drop-down menu.

screenshot of Google Sheets Spreadsheet template for Task Tracking drop down menus

Now that you have your task tracker spreadsheet set up, don’t add tasks to the Blank sheet. Keep it for reference. Instead, do the following:

Duplicate the Blank sheet (right-click and select Duplicate) and name it the dates for that week.
(OPTIONAL) Hide the Blank sheet by right-clicking on the tab and selecting Hide Sheet.
During the week, add items to your spreadsheet and track them.
Set the priority of each task as you add tasks.
Set the value add of each task as you add tasks.
Continue adding tasks to the sheet (inserting rows as needed) and updating the status when needed.
As needed, sort the spreadsheet by the Priority column or Status column.

screenshot of a Google Sheets Status Drop-Down Menu for Task Tracking

Below the portion for tracking tasks, use the section for follow-ups and notes to note any follow-up phone calls, conversations, or emails you need to make or any updates or questions you have.

screenshot of Google Sheets Notes and Follow Up in a Task Tracking Spreadsheet

At the end of a week, duplicate the current week’s tab and rename it with dates for the upcoming week.

screenshot of a Google Sheets dates tabs

Leave items from the current week in the new week’s tab if they need to stay on your radar or if they are incomplete.

“Nerdify” Your Task Tracker Sheet

As if you aren’t nerdy enough by keeping your to-do list in a spreadsheet, consider using conditional formatting to color the cell based on the content of the dropdown box. For my task tracker spreadsheet, I have the row to turn green if the status is set to “Complete”. If the status is “In progress”, then the row is yellow. Otherwise, the row is red.

Instead of setting the conditional formatting based on the status, you could use the priority as the trigger for coloring the rows. If you are new to conditional formatting, then check out one of these previous posts to get more help:

How Will You Use It?

If you use the task tracker, how will you tweak it to make it fit your own style and processes? Will you do conditional formatting? What other features will you add to it to make it more user-friendly? Drop a comment below and let us know.

July 19, 2023 0 comment

Title image with 8 Google Sheets Tips listed

Google Tips and Tricks

Eight More Google Sheets Tips to Save You Time

by Emily Horn March 30, 2023

written by Emily Horn

It’s the end of March. That means it’s time for more time-saving tips for Google Sheets. Why? Because any time is a good time for Google Sheets tips! Right? Here are eight more tricks to try. Hopefully, you’ll learn something new that can help you on the daily. Enjoy!

1. Create a Timeline

Did you know that you (and students!) can create timelines using Google Sheets? These can be used for history, project management, collaborative tasks, and more. Check out this quick three-minute video showing how to take Civil War battle information to create a timeline of the Civil War.

2. Force a Copy of a Spreadsheet

Want to let people use your spreadsheet as an editable template without giving them access to your actual spreadsheet? Change the word “edit” in the URL to “copy”, and share this link.

They’ll be prompted to make a copy of your spreadsheet for editing!

3. Easily Create and Open a New Spreadsheet

If you’re looking for a fast and easy way to create a brand new spreadsheet, look no further. Simply go to the search bar and type “sheets.new.”

Screenshot of creating a New Spreadsheet

Your new spreadsheet will magically appear!

4. Translate Languages within a Sheet

You can translate the contents of one cell into another language using this formula:

=GOOGLETRANSLATE(cell with text, “source language”, “target language”)

Simply place the formula in the cell you want the translated text to appear, indicate the cell you’d like translated, and use the two-letter language code for the source language and the target language. In the example below, I place the formula in B1. I asked it to translate text from A1 from English (en) to French (fr).

Google translate formula in a google sheet cell

Et voila! You have your translation.

example of English to French translation

5. Use Google Sheets Keyboard Shortcuts

There are so many shortcuts to be taken advantage of in Google Sheets! Here are just a few of my personal favorites:

6. Get Notifications of Changes

Did you know you can get notified anytime a change has been made to your spreadsheet? You can! Click on Tools, Notification settings, and then edit notifications.

Select to be notified when “Any changes are made” and then make your choice for a daily digest or an immediate email.

7. Protect Parts of Your Sheet and Make Other Parts Editable

Let’s say you want students to add to your sheet, but you don’t want anyone to edit your formatting! No problem. Right click on the sheet and select Protect sheet.

Sheet menu with the option to protect a sheet

Then, you can enter a description and select which cells and ranges you don’t want protected, which means they can be edited. Pretty cool!

protect sheets and ranges options in Google Sheets

8. Use Macros to Help You Complete Tasks in One Click

This one is truly amazing. You can literally record your screen (“Record a macro”) as you’re completing any task (averaging grades, formatting a sheet, setting up a calendar, etc.) within a spreadsheet. Then, instead of completing the same task step by step the same way over and over and over again to multiple sheets, you can just run the macro, and it will do the task for you. Mind. Blown. Take a look at this video example.

Have any of your own tricks up your sleeve? Share them with us in the comments below. The more tips, the better!

March 30, 2023 2 comments

Google Tips and Tricks Microsoft Tips and Tricks

Google Sheets Tip: Conditional Formatting Custom Formulas

by Miguel Guhlin November 2, 2022

written by Miguel Guhlin

Do you ever wish the color of your spreadsheet rows would change color to match the content? If yes, you may enjoy learning about conditional formatting. Conditional formatting is a wonderful feature in Google Sheets and Microsoft Excel. Let’s explore how to use custom formulas that can change the color of cells and rows of data based on the data in a cell.

Diving 🏊‍♂️🥽🌊 into #GoogleSheets with Biology to show how Ss grew from pre- and post-Quarter 1 test using TEKS and a little conditional formatting. Source: Lauren Hawkins’ (@EdTechHawk) Tweet

Meet the Problem

“Oh, I use conditional formatting all the time. But I want to change the colors of the first column when the data in the last column changes. How could I do that?”

It might help to see what this problem looks like. Most spreadsheet users know you can use conditional formatting.

Consider this spreadsheet, which represents a list of workshops and the number of registrants for each course:

Conditional formatting could allow me to change the colors. For example, I could add conditional formatting rules for different conditions. Let’s take a look at examples.

1) Create a sliding color scale.

These settings would adjust the colors of the cells where values appear. For example, the minimum number “0” would be red. The midpoint (“15”) would be yellow, and the maxpoint (“30”) would be green. You can see what that looks like below, as well as take a look at the color scale:

2) Apply single colors.

This is the most often used conditional formatting solution. In this case, you can use a variety of format rules. These format rules say, “Format cells if…” certain conditions are met. Here is the list of most conditions you can apply:

I often use these when getting data from a Google Form, and I want to see how YES or NO responses look.

As you can see in the screenshot above, conditional formatting changes the color of the cell. The colors depend on the value in the cell. In this case, “Yes” is green, and “No” is red.

But what happens when you want the entire row to be green or red based on the YES or NO? That’s where custom formulas come to the rescue.

Introducing Custom Formula

Custom formula makes it easy to apply colors and formatting to an entire row of data. After you highlight the rows you want to apply conditional formatting to, go to Format -> Conditional Formatting. Then, enter a formula into the “Custom Formula” option, as shown below:

You can see that you will need to enter a “value or formula” into the custom formula box:

This will allow you to enter a formula for the conditional formatting rule. In this case, I want two rules to be present:

When the word “Yes” appears in column “C,” the entire row to turns green
When the word “No” appears in column “C,” the entire row to turns red

But how do you do that? What do you type into the custom formula box?

Here’s what that looks like with one custom formula rule applied:

Notice that the $ in the formula applies formatting that matches the row of data where it appears (A2 to C2, A3 to C3). This is referred to as “absolute references” rather than relative references. If I were to not include the dollar sign, only the first column would change color, as you can see below:

To add the red fill color to each cell in a row, I have to add another rule:

Both of the custom formula rules appear below:

When the word “Yes” appears in column “C,” the entire row to turns green: =$C2= “Yes”
When the word “No” appears in column “C,” the entire row to turns red: =$C2= “No”

And that looks like this:

Isn’t that cool?

Want to Learn More?

As a Google Sheets user, I often find myself learning new things about stuff I thought I knew. I’ve been using conditional formatting for years. Yet, I never thought to scroll to the bottom of the formatting rules to see what else was there! Now that you know about custom formulas in conditional formatting, how will you use them?

Learn more about conditional formatting and Google Sheets:

If you don’t use either Google or Microsoft, give OnlyOffice a try. It also offers many features in spreadsheets without the price. You can read my blog entry about OnlyOffice here.

November 2, 2022 0 comment

Google Tips and Tricks

Powerful Add-ons and Functions for Google Sheets

by Miguel Guhlin October 25, 2022

written by Miguel Guhlin

Have you ever tried to cut a tree down with a hammer instead of a hatchet? If you know how these two tools work, you won’t even try. But that’s what we do with digital tools every day. Imagine if you knew a simple hack for turning a hammer into a hatchet. It could save a lot of time, effort, and money.

It was only last month that I found myself dealing with tons of data. What I wanted was simple, but I had no idea how to get Google Sheets to reveal the data. The answer? Use a function I had never heard of. This experience convinced me to try and learn about new functions.

So now, I want to share some of my favorite tools and functions of Google Sheets– tools and functions that can help you avoid the frustration of trying to cut a tree down with a hammer instead of a hatchet. In other words, they can make your work a little easier.

Why We Use Google Sheets

I’ve been using spreadsheets since I was 13 years old, but I still know very little about them. That’s because I’m always focused on the problems I have to solve. But if you take a moment to look around, you’ll realize that you don’t always have to use the same tools to solve problems. And Google Sheets is certainly a tool for solving problems. Examples of problems Google Sheets can solve include:

Making sense of complexity or representing data in easy-to-understand formats like graphs
Sorting and filtering data to see relationships
Calculating and summarizing
Connecting more efficiently with others

Add-Ons and Extensions for Google Sheets

If you’re like me, you may have reached for some digital helpers like Google add-ons or extensions to Google Sheets. Some of my favorite solution helpers for Google Sheets include these power tools from Hymel Stayte’s Solutions:

Mail Merge Tools for Sheets

FormMule
Yet Another Mail Merge (YAMM)

Using Sheets as a Database

Google Data Studio
Watch Mike and Megan Video
Google Data Studio Resources: A Wakelet Collection
Awesome Table
Tweet Archiver
TAGS – Twitter Archiving Google Sheet

Generating Documents Using Google Sheets

If you have more add-ons that you like for Google Sheets, please feel free to share those in the comments. But what if these power tools are too much for what you are trying to do? Or what if you need to dig into your data in a way that’s incongruent with these add-ons?

It’s time to explore some of my favorite functions below.

Google Sheets Functions

The following functions are ones that I use often. You can find a sample spreadsheet here (get a copy of it) that shows how each of these works.

One simple example that a lot of folks don’t know about that I use every day? Concatenate.

=Concatenate(A2,” “,B2,”<”,C3,”>”)

This allows you to put text together. My biggest use involves concatenating text such as firstname, lastname, and email. I use the result to enroll people in Canvas LMS.

Another one that I didn’t know about but is so powerful: IFERROR. It prevents showing a nonsensical error. Instead, it shows you a message you can customize.

Each of these expands your repertoire of what’s possible in Google Sheets. Take the time to learn them and practice them.

=join(“,”,A1:A5)

=IFERROR(value, [value_if_error]) (See more)

=IMPORTRANGE (sheet_url, range_string)

=QUERY(NAMEDRANGE, “SELECT A WHERE B=‘Google Sites’ “)

=COUNTIF (range, criterion)

=COUNTA (range, criterion)

=DetectLanguage(B2) with =GoogleTanslate(B2,C2,”en”)

Need a few more?

Get the “Make Magic with Spreadsheets” Wakelet

Want to get more examples in video format? You may want to check out this Wakelet where I’ve curated a few videos and tutorials.

October 25, 2022 4 comments

Google Tips and Tricks Math

Five Ways to Supercharge Your Google Sheets

by Lauren Hawkins October 12, 2022

written by Lauren Hawkins

Struggling to take your Google Sheets beyond data collection and surveys? Do you think spreadsheets are only helpful when investigating trends within your classroom? While Google Sheets can provide you with ways of analyzing your data to impact your instruction, let’s explore five tricks that can take you far beyond those capabilities.

1. Conditional Formatting in Google Sheets

What if I’d like students’ responses to be automatically color-coded?

Conditional Formatting makes this process efficient and an absolute time saver. Google Sheets provides you with ways to automatically color code responses to provide that “instant check” of student work. This can apply to both text and numbers, depending on your need.

Use this skill to:

Highlight student responses on a discussion board that contains a certain word or phrase.
Check student solutions to a mathematics or science problem.
Insert checkboxes as a way for students to monitor their progress through a blended learning unit. Checkboxes can turn green once selected by using “TEXT EQUALS TRUE” under the Conditional Formatting menu.

In the Algebra 2 example below, students solved three problems within a skill set and Google Sheets automatically checked if their total was correct. If it wasn’t, students needed to work through their problems again, encouraging error analysis conversations and academic collaboration. Learn how to get started with conditional formatting.

Google Sheets conditional formatting example

2. Data Validation

What if I want students to select a phrase or word from a dropdown menu?

Data Validation, located under the Data menu, can help with this. You can create a specific list of items, i.e. answers, for students to select from to alleviate any spelling concerns. This is perfect for those fill-in-the-blank activities, sequencing events, geometric proofs, and student surveys. Use the date option instead to create a sign-up Google Sheet for student presentations, grade interviews, or small group instruction.

DID YOU KNOW: You can combine this skill with conditional formatting! Choose an answer and it can automatically go green. Check out this example to test it out.

3. IF Formulas in Google Sheets

What if I want students to get scaffolded support based on which answer they type?

Using the IF formula, Google Sheets will check the student’s work against the correct answer that you provide, and then provide support with either text or a link to an external resource. You can even create digital breakout activities using the IF formula. For example, IF students type the code “MATH ROCKS”, THEN next clue is given. In general, your formula will look like:

=IF(Cell= “Code”, “Correct answer next steps”, “Wrong answer next steps/Feedback”)

=IF(C5=“Start”, “Congrats! Your next lock is…”, “Revisit our 2.1 notes.”)

Check out this Algebra 2 example where students practiced factoring polynomials. Learn how to get started with this digital breakout tutorial using Sheets and this TEMPLATE.

This Geometry example, created by Mandi Tolen, utilizes the same IF formula and conditional formatting to automatically have a hidden picture appear if students answer questions correctly. Learn and practice with her Google Sheets template on her blog post.

4. Hyperlinks

What if I want students to explore a website, watch a video, or reference a digital classroom material within an activity?

The Hyperlink formula will accomplish just that! Combine the Hyperlink formula with the IF formula to provide students with reteach pieces or interventions. Link to anchor chart images, online exploration exercises, or Slides with questions like the example above. In general, your formula will look like:

=hyperlink(“web URL address”, “title of the link”)

5. Google Sheets’ vLookup

Do you have a list of task cards that you’d like students to complete AND have their answers checked?

Use the VLOOKUP formula to pull in questions based on what students want to work on. This formula will take the students’ selection, vertically locate the task from a list you’ve created, and automatically pull it up for students. Combine this skill with conditional formatting in Google Sheets so that students’ answers can be checked while they work. Check out the Algebra 2 example below on characteristics of rational functions. This activity allowed students to work in any order within the ten provided questions. Take a look at this blog post from Mandi Tolen to learn more and snag a template.

I hope these five tricks help you leverage the power of Google Sheets to provide instant feedback and create engaging experiences for your students. Let’s take those worksheets and make them into authentic “work”sheets!

To learn more from Lauren, take a look at the sessions she’s leading at TCEA 2023, January 30- February 2 in San Antonio, TX. Want to be in the middle of some majorly innovative learning, meet other passionate educators, and explore limitless possibilities in ed tech? Early bird pricing ends November 4. Don’t miss it!

October 12, 2022 0 comment

Educational Research Google Tips and Tricks Math

Solving Real Life Problems with Google Sheets

by Miguel Guhlin September 22, 2022

written by Miguel Guhlin

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.

Adapted from George Polya’s work by author

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

September 22, 2022 0 comment

Google Tips and Tricks

Seven Google Sheets Tips to Save You Time

by Emily Horn December 2, 2021

written by Emily Horn

We could all use a little more time. In fact, I’ve recently wondered where August, September, and October went. I can’t be the only person to experience this phenomenon! But regardless of how time feels to you right now, here are seven tricks for saving time with Google Sheets.

1. Merge cells to create first initial last name (i.e. ehopkins) usernames.

Did you know there’s a quick way to merge a cell with a first name (Emily) and a cell with a last name (Hopkins) to make a username (ehopkins)? Well, it’s true. Using one of the formulas below, you can easily do this with a list of hundreds of names. If you ever have to upload CSV rosters to create accounts, this will save you tons of time.

Here are two formulas to choose from:

=(CONCATENATE(LEFT(CELL,1),””, CELL))
=(LEFT(CELL,1) & “” & CELL)

Here’s what it looks like in action:

Take a look at the following trick to see how to change the case for your usernames.

2. Change text in cells to lowercase, uppercase, or proper case.

You can use one of the three functions below, either with another function or on its own, to change the case of your text in cells.

=PROPER

=UPPER

=LOWER

See them in action:

3. Want to see all Google Sheets keyboard shortcuts? You can!

If you’re using a Mac, simply press the command and forward slash keys. On a Windows machine, press the control key and forward slash. A window full of keyboard shortcuts will appear for you. It’s magic.

4. Easily create QR codes for any cell.

I love this one. You can create QR codes with information from your spreadsheet using this function.

=IMAGE(“https://chart.googleapis.com/chart?chs=200×200&cht=qr&chl=”&CELL&”)”)

Just as an example, let’s imagine that you have a spreadsheet of usernames and passwords for your students. You can create a QR code for their passwords and provide them with a printed copy of their username and QR code. And if they forget their password, they can simply scan their code!

Here is a short clip to show how this can be done:

5. Create a drop-down list in any cell.

Select the cell where you’d like to have a drop-down list.
Click on Data in the Sheets menu and select Data validation.
From the list, choose List of items and type your items into the text box, separating them with commas.
Click Save.
The dropdown box will appear, and you can drag it to place it in more cells inside your spreadsheet.

See this in action:

Check out this blog article about drop-down menus in lesson plans if you’d like to go deeper.

6. Send a Google Sheets comment through email.

Click the cell you’d like to add a comment to.
Either click Insert in the Google Sheets menu and then Comment; or you can select the add comment icon in the toolbar.
When you see the comment box pop up, type @ and select an email address for the person you’d like to recive the comment. If you do not see their name on the list, you can type their email address in manually after the @.
Type your comment after their email address.
Click the checkbox next to “Assign to…”, then click the green Assign box. The assinee will recieve an email with their comment.

7. Fill multiple cells quickly and easily.

If you drag one cell down, you can fill cells with the same contents, but there are a few more tricks for this. Watch this short video by @JakeMillerTech to learn these tips to “fill down” cells and give him a follow on Twitter for even more great tips. These are fantastically useful, and I learned something new watching this.

A2 There are so many hacks and tricks that I ❤️ to use with #GoogleSheets! One that really blew #GIFaDay participants minds is the simple double-click to fill down trick! Here's the #EduGIF! #DitchBook https://t.co/MLVfv24ywU pic.twitter.com/vO9RGj2iXh
— Jake Miller (@JakeMillerTech) May 10, 2019

I hope that you find these Google Sheets tips useful. If you have any to add, shout them out in the comments.

December 2, 2021 0 comment

Educational Trends Google Tips and Tricks

Create a Mobile App from Google Sheets in Minutes

by Diana Benner April 4, 2019

written by Diana Benner

updated 04/29/19

Did you know that you can easily create a mobile app from one of your Google Sheets?

Glide is a service that lets you add information to a Google sheet, then quickly create an app from its contents, all without coding. Just pick one of your sheets and Glide assembles it into a polished app that you can customize, share with a link, and even publish to the app store.

Using Glide

It’s simple to use Glide. As a matter of fact, I can create an app in less than five minutes—and I know if I can do it, anyone else can. Follow these easy steps below:

Go to the Glide website.
Click on Create an App and sign in with your Google account.
Click on New app and search for your Google sheet.
Wait for your app to be created.

Customizing Your Mobile App

In addition, you can customize your app’s color, logo, and name. You can also create a URL for it. Most importantly, you can password protect it if you don’t want it to be public. If you need extra help, short, built-in videos will talk you through each step. In addition, the Glide Community Forum offers help and feedback on your apps.

Once you are done, you can share your app via its link or open it in a new window. When you do this, the app will appear on your web browser. You can view it there or scan the QR code that is automatically generated. After scanning the code with your mobile device, add the app to your home screen.

Cost of Glide

Glide can be used for free. With the free version, you can create unlimited apps and share them using a link. There are also paid versions that allow you to remove the Glide branding, as well as push the app out via Apple’s app store and Google Play.

Types of Apps Being Built

Many different types of app can be built using Glide, from school directory apps to school project apps. If you are looking for ways Glide is being used, check out these samples.

For instance, the Objectives app allows you to track objectives or goals in order to keep everything aligned. Another example is an Employee Directory app which helps locate people quickly. You can even copy and customize these sample apps to make them your own. On Twitter, #madewithglide showcases the amazing apps people are making.

Be aware that users must be at least 18 years of age to use Glide, according to Glide’s Legal Terms and Privacy Policy.

Updating the App

A great thing about Glide is the two-way connection between the app and the Google sheet. The app automatically updates from your sheet, so you don’t need to do anything for it to work: it’s automatic. This means edits and changes to your Google Sheet update quickly and without the need to relaunch the app.

Give It a Try

I challenge you to try to create an app using Glide. You’ll be surprised at just how easy it is! Got a great idea or an example of how you use Glide? Let me know in the comments!

To learn even more about Glide, listen to the podcast by David Siegel, in which he explains the history and ideas behind Glide and be sure to follow @glideapps on Twitter.

April 4, 2019 14 comments

Google Tips and Tricks

Five Hacks for Google Sheets

by Diana Benner November 6, 2018

written by Diana Benner

Do you create Google Sheets? In our office, many use Google Sheets for a multitude of projects. At a recent Google Educator Level 1 training, participants were unaware of my top five hacks for Google Sheets. Let’s take a look at five Google Sheets hacks you may find useful. Depending on your needs, these can make your Sheets experience more productive.

Hack #1 – Flippity.net

“Have you explored Flippity.net?” I asked session participants. Almost all shook their heads in the negative. Flippity takes data you have in your Google Sheet and then does cool stuff with it.

Note: With each Sheet, you will must go to File->Publish the sheet. This make its available on the web for viewing. When you get it setup, you will copy the Flippity.net website address (URL) (shown in blue in the image below) then share that with students or staff.

Here are some of the problems that the free Flippity website helps you solve as an educator:

Flashcards – Take content in a Google Sheet and turn it into online flashcards. You create two columns in a Sheet, putting side one (the prompt) of the flashcard in column one. In column two, you put side two content (the response). With this option, you can also generate more resources. Those include a simple list, practice, matching, and word cloud. Flippity makes it easy to send your list of data to create Bingo cards, Crossword, and even Hangman. It can generate a Memory Game, Word Search, and Print Quiz.
Quiz Show – Want to create your own trivia game a la a Jeopardy show? Flippity makes that easy.
Random Name Picker – Need a random name picker for selecting students for projects? This tool will use your class roster to make it happen.
Scavenger Hunt – Turn any Sheet into a scavenger hunt activity.

More creations that Flippity makes possible include Timeline maker tool. You can use its Badge Tracker, Spelling Word Manager, and Word Search tools, too. MadLibs, Mix and Match, and Certificate Quiz appear, too. Ready to get started? Go to Flippity’s website and follow the instructions that go with each tool.

Hack #2 – RosterSync

This free add-on to Google Sheets makes it easy to create and update Google Classroom. With RosterSync Teacher Edition, you can sync a copy of a Google Classroom course roster. Student names and email addresses get saved to Sheets. Why is this important? It makes getting at data saved in Classrooms easier to use elsewhere. Once you have that class roster of students, you can use other add-ons (like Flippity) to do more.

Hack #3 – Save Email and Attachments

Get a lot of emails with attached files? Use this add-on to Google Sheets to create a custom filter. This add-on will save file attachments from selected incoming email as PDF files. You can create a “rule” that has two options. The options include save the email body (the contents) and file attachments. Here is what one query looks like:

Query:

label:All from:SOMEONE@tcea.org has:attachment

More options are available in the premium version ($29 per year). Those options include:

Unlimited rules for saving emails and attachments to:
- Google Drive
- Google Team Drive folders
Faster save rate (every 15 minutes), which is great if you have tons of incoming email
Custom file names
Permit or deny file attachments that have specific extensions (e.g. EXEs to avoid potential virus carriers)
Remove watermark in generated PDF files

The free version can get you started. Want to learn more? Watch this short video. An alternative solution is CloudHQ.net’s Save Emails to Google Drive add-on. CloudHQ’s version lets you save one hundred emails to Google Drive for free. They feature another add-on, Save and Backup My Emails, that will backup all emails and files.

Hack #4 – Power Tools

Feature-rich, this add-on offers rich variety of tools you can use to manage your data in a spreadsheet. If you have three minutes, you may want to watch this video overview. Some of the tools in this must-have tool for Sheets include:

Change case (e.g. capital, lowercase)
Sum colored cells
Remove duplicates and compare two or more columns
Split, merge, compare, find data
Unpivot tables, unmerge cells
Autosum numbers in every row or column
Add or remove text by position
Transform formulas
Change formula reference types
Delete unwanted characters
Merge sheets
Randomize data

Power Tools offers a helpful toolbar that provides one-click actions (like those listed above). The Power Tools add-on is one of my top hacks for Google Sheets. I expect it will soon become yours.

Hack #5 – Bulk Email Tools

Have you collected students’ parent names and emails via a Google Form? Make it easy to stay in contact with custom updates to parents. Send a clickable email (instead of paper) that parents will be able to interact with. Need to send a standard email to workshop participants? You can do this with one or both of these two bulk email tools:

Yet Another Mail Merge (YAMM) (Watch video) – YAMM comes with 50 free emails a day that you can send out and a variety of video tutorials. Create a draft email in Gmail and then send it to everyone listed in a Google Sheet.
FormMule (Watch video) – A powerful bulk email tool, you can set up ten different email templates.

My favorite uses of these tools include emailing:

Parents with student-specific information that is not FERPA related
Students (in older grades) with specific non-FERPA related information, including assignment reminders
Colleagues and staff about upcoming or past events that include links to more information

Five Hacks for Google Sheets

Adopt these hacks to step up your work with Google Sheets. You will be able to get more done and collaborate with others in new ways.

November 6, 2018 0 comment

Newer Posts

Older Posts