Sheets: Automating Sorting from Google Forms

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.

Basic Sorting

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

The problem is, with new Form submissions all of the responses are not sorted.

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.”

LOOK AWAY!!! You do not need to look at the code

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!!

That is it!! Record a macro and set a trigger!

• Magic! Automatic Daily Agenda Google Slides
• Automate – Share Calendar Events With Yourself
• Easy! Google Forms: Automatically Sort on Submit
• Trigger An Action from Google Sheets
• How to Use the New Google Sheets Extensions Menu

How to Add a Google Sheets Spreadsheet to Google Forms

To add a spreadsheet to a Google Form, follow these steps:

1. Go to the Google Forms website and sign in to your Google account.
2. Click on the form you want to add a spreadsheet to.
3. Click on the “Responses” tab at the top of the form.
4. Click on the green “+” icon to create a new sheet.
5. 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:

1. Select the range of cells that you want to sort. You can select a single column, multiple columns, or a block of cells.
2. Click on the “Data” menu and select “Sort sheet by column”.
3. In the window that appears, select the column that you want to sort by from the “Sort by” drop-down menu.
4. Select the sort order (ascending or descending) from the “Sort order” drop-down menu.
5. 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.
6. 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.
7. 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:

1. Open the Google Sheets spreadsheet that you want to use the macro in.
2. Click on the “Extensions” menu and select “Macros” from the drop-down list.
3. In the window that appears, click on the “Record Macro” button.
4. Perform the actions that you want to include in the macro. These actions can include formatting changes, data entry, or other tasks.
5. When you are finished, click on the “Stop Recording” button.
6. 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.