Of course you use Google Forms! Adding a spreadsheet on the Responses tab allows you to see all the responses in one place. By default the responses show up in the spreadsheet in the order they are filled out on the Form. However, you may have a different way you want to sort the results. Additionally, you need it sorted this way each time you look at the data. To automate sorting in Google Sheets you need to record a macro, apply the sorting filters, and set a trigger.
Sorting in Google Sheets
The first thing you need to know is HOW to sort the data.
Hovering over a column indicator in the spreadsheet will reveal a tiny arrow that is a drop-down for additional options. Alternatively, you can right click on the column indicator to reveal the menu of options.
Select “Sort sheet A to Z”
Use the Data Menu
You can also use the Data menu to sort the sheet. Select the column you wish to sort by and use the Data menu to select “Sort sheet.
More Complicated Sorting
If you want to sort by multiple columns, first click on the “Awesome box.” This is the blank spot to the left of column indicator A and above row 1. This highlights the entire document. Then use the Data menu. Third, select “Sort range.” This will allow you to choose “Advanced range sorting options.”
This will bring up the advanced sorting options pop up box. Google Forms automatically create column headers so make sure you check “Data has header row.” This will change the sort options from column letter to the column header title in the first row of your spreadsheet. Consider what you FIRST want to sort by. Then, click “Add another sort column.” This will allow you to choose additional columns that you want to set sorting by. Click on “Sort.”
Want More Help with This? Become a Premium Member
Using Macros to Remember Your Sort Preferences
A macro, in Google Sheets, is a series of actions recorded by the user and played back to automate repetitive tasks. Record actions, such as formatting changes, data entry, or other tasks, play back the macro to save time and effort. Use the “Macros” feature under the “Extensions” menu to create and run macros.
BEFORE YOU SORT
To automate sorting in Google Sheets you’ll need to remember the steps you went through to sort your spreadsheet. This is what a macro is for. Select record macro under the Extensions menu. This will start a recorder. Click on the “awesome box” to select all in your spreadsheet. Go to the Data menu and choose “Advanced range sorting options.” Then select your sorting preferences.
When you are done applying how you want to sort your spreadsheet, stop the recording of the Macro by clicking “Save”.
Name Your Macro
You will have the option to name your macro something meaningful.
Using Your Macro
You COULD, each time you open the spreadsheet, go back to the Extensions menu and select Macros. You’ll find your named macro in the menu.
Automate Sorting in Google Sheets
Most importantly you want this to just happen each time you open the spreadsheet! Good news, this is relatively easy!!
Instead of selecting the named macro each time, choose “Manage macros.” This will allow you to click on the 3 dots menu for the macro. Select “Edit script.”
Macros are Google Apps Script
Macros actually record Google Apps Script. By choosing “Edit script” you will see the code that was generated to create the steps for recording your macro. You do NOT need to look at this.
Add Trigger to Automate Sorting in Google Sheets
The trigger icon, a clock, on the left side gives you the option to “Add trigger.”
Choose Macro to Automate
The first option in the trigger menu is which macro function you want to run.
Select Event Type
The bottom option is “Select event type.” The default is “On open.” This is possibly what you want. Each time you open the spreadsheet the macro will automatically run! Boom, sheet sorted. If your data is from a Google Form you might as well have it sort each time someone submits the Form. Choose “On form submit” instead. Click Save!!
How to Add a Google Sheets Spreadsheet to Google Forms
To add a spreadsheet to a Google Form, follow these steps:
- Go to the Google Forms website and sign in to your Google account.
- Click on the form you want to add a spreadsheet to.
- Click on the “Responses” tab at the top of the form.
- Click on the green “+” icon to create a new sheet.
- In the window that appears, enter a name for your sheet and select “Create”.
This will create a new spreadsheet that is linked to your form. Any responses that are submitted through the form will be automatically added to the spreadsheet.
How to Sort Data in Google Sheets
To sort data in Google Sheets, follow these steps:
- Select the range of cells that you want to sort. You can select a single column, multiple columns, or a block of cells.
- Click on the “Data” menu and select “Sort sheet by column”.
- In the window that appears, select the column that you want to sort by from the “Sort by” drop-down menu.
- Select the sort order (ascending or descending) from the “Sort order” drop-down menu.
- If you want to sort by multiple columns, click on the “Add another sort column” button and select the additional columns that you want to sort by.
- If you want to sort the data in place (i.e., without creating a new sheet), make sure the “Data has header row” option is selected. If you want to create a new sheet with the sorted data, leave this option unselected.
- Click on the “Sort” button to sort the data.
If you want to sort the data using a formula, you can use the SORT function. For example, the formula
=SORT(A2:C10, 2, TRUE) will sort the range A2:C10 by the second column in ascending order.
How to Use Macros in Google Sheets
Macros in Google Sheets allow you to record a series of actions and play them back to automate repetitive tasks. Here’s how to use macros in Google Sheets:
- Open the Google Sheets spreadsheet that you want to use the macro in.
- Click on the “Extensions” menu and select “Macros” from the drop-down list.
- In the window that appears, click on the “Record Macro” button.
- Perform the actions that you want to include in the macro. These actions can include formatting changes, data entry, or other tasks.
- When you are finished, click on the “Stop Recording” button.
- In the window that appears, enter a name for your macro and click “Save”.
To play back a macro, click on the “Extensions” menu and select “Macros” again. Then, select the macro that you want to run from the list and click on the “Run” button. The actions in the macro will be performed on the current sheet.
Note that macros in Google Sheets are only available in the desktop web version of the application. They are not available in the mobile app or in the Sheets app for Chrome.
Student engagement matters. It is challenging to get true student engagement. If you are a user of Google Workspace here are 50 ways to engage students with Google Apps.
Why do you go left to right? Because a teacher said so? It is the rule? Help your students be more flexible by NOT using order of operations. Ditch PEMDAS!!
Chatbots aren’t just for casual conversations like those in Google Chat. They offer a powerful new way to enhance the learning experience.