Tech Tips #66 - Automatically Continuously Sort Data in Google Sheets

Summary

This video demonstrates how to use a custom Google Apps Script to continuously auto-sort columns in Google Sheets. The script was created by Mike Brandtsey and keeps data sorted in real-time as new entries are added. The video walks through customizing the script by setting the sort column, sort order, and number of header rows. After saving the script and reloading the sheet, any new data entered will automatically be sorted in the selected column. This allows users to always view organized data without any manual sorting required.

Takeaways

• 😀 The video shows how to continuously auto-sort data in a Google Sheets column whenever new data is added
• 👉🏻 Uses a custom Google Apps Script written by Mike Bronski from GitHub
• 👨‍💻 Opens the Google Sheets script editor, copies in the script code, and makes edits to 3 parameters
• ➡️ Sort column index - which column to sort
• ➡️ Ascending or descending order
• ➡️ Number of header rows to ignore
• 👍🏻 Saves the script and reloads the sheet to test real-time auto-sorting
• 🆕 Adds new numbers to show auto-sorting in action, keeping data ordered
• 🙏🏻 Thanks Mike Bronski for sharingopen-source script to enable this functionality
• 💡 Suggests viewers leave comments/questions, subscribe for more content

Q & A

What was the initial question asked by one of the viewers?

The initial question was if it is possible to continuously/automatically sort data in a spreadsheet column anytime new data is added.

What Google script is used to enable continuous sorting in Google Sheets?

The script used is by Mike Brandt's key available on GitHub.

What are the three things that need to be configured in the script?

The three things that need to be configured are: 1) The sort column index, 2) If the sorting should be ascending or descending, 3) The number of header rows.

How do you specify which column to sort?

The sort column is specified using its index, where A = 1, B = 2, etc. So to sort column A, the sort column index would be set to 1.

How do you specify the sorting order?

The sorting order is set using a True/False value. True means ascending order and False means descending order.

What does the number of header rows configure?

It configures how many rows at the top of the sheet are static headers/titles and should not be sorted.

What happens after the script is configured and enabled?

After enabling, any new data added to the configured column will automatically and continuously sort based on the configured settings.

Does the automatic sorting work for numeric as well as text data?

Yes, the script supports automatic sorting for both text and numeric data entered in the configured column.

Do you need to rerun the script for the auto-sort to work?

No, once configured and enabled, the auto-sort runs continuously in the background without needing to rerun the script.

What if you need sorting on multiple columns?

You would need to configure and enable a separate instance of the script for each column that needs continuous, automatic sorting.

Outlines

00:00

📺 Introducing the Auto Data Sorting Script

Tom introduces himself as the director of technology and operations at Colonial Intermediate Unit. He states that this is tech tips video #66, which shows how to use a Google script created by Mike Brandt to automatically sort data in a Google Sheets column any time new data is added. Tom demonstrates how to install and configure the script.

Keywords

💡Google Sheets

Google Sheets is a web-based spreadsheet program that is part of Google's office suite. It is used in the video to demonstrate the auto-sort script. Google Sheets allows users to create, edit, and collaborate on spreadsheets online. The auto-sort feature keeps the data in a selected column continuously sorted whenever new data is added.

💡GitHub

GitHub is an online service for storing, managing, and sharing code. It is mentioned because the auto-sort script used in the video is shared on GitHub by a user named Mike Bronski. By using GitHub, the narrator can easily access Mike's publicly available script and integrate it into his Google sheet.

💡Auto-sort

Auto-sort refers to the automatic sorting of data in a spreadsheet. The main purpose of the video is to demonstrate an auto-sort script that continuously sorts entries in a Google Sheets column whenever new data is added. This saves the user from having to manually sort each time.

💡Script Editor

The script editor is a feature in Google Sheets that allows users to insert Apps Scripts - small JavaScript programs that add functionality. The narrator accesses the script editor to copy-paste Mike's auto-sort script into his sheet and customize it.

💡Ascending vs. Descending

This refers to the direction of sorting - ascending sorts data from lowest to highest, descending sort highest to lowest. The script has a boolean toggle to choose between ascending and descending sort. The video uses ascending sort to organize the numbers from small to large.

💡Column Index

This specifies which column's data should be auto-sorted. Columns in sheets are identified by index numbers, starting from 1 - A=1, B=2 etc. The narrator sets his sort column index as 1 since he wants to auto-sort the entries in Column A.

💡Header Rows

Header rows refer to the top rows in a sheet that contain labels/headings for the data columns below. The script has a setting for the number of header rows to skip sorting - this is set to 1 as there is one header row.

💡Code

Code refers to the programming code that defines the auto-sort logic and functionality. Without this code written by Mike Bronski, the auto-sort feature would not be possible. The narrator copies this code into his Script Editor to activate auto-sort.

💡Customization

After copying Mike's generic script, the narrator customizes a few lines to work specifically for his sheet and data - like setting the index for the column to sort, order direction etc. This makes the auto-sort script adaptable to any sheet.

💡New Content

The concluding message asks viewers to subscribe to get updates on new videos and content shared by the narrator. This refers to any future tech tips videos or other educational materials he may create for the audience.

Highlights

The question was is it possible to sort data in a column continuously as new data is added

Thanks to Mike Brandt's script on GitHub that can auto sort Google Sheets data anytime new data is added

Go to Tools > Script editor to access a blank script page

Copy Mike's auto sort script code and paste it into your script editor

Update the script with the sort column index, ascending/descending preference, and number of header rows

Reload the sheet and now any new data added will automatically sort

The script puts new data in the correctly sorted position automatically

The script keeps data continuously sorted with no further effort needed

Special thanks to Mike Bronski for creating the reusable Google Sheets script

The auto sort script saves time and ensures properly ordered data

The script is customizable by configuring the sort parameters as needed

Feel free to suggest scripts or ask questions to improve future tech tips

Don't forget to subscribe and enable notifications for new tech tip content

Auto sorting data continuously is a huge timesaver with dynamic sheets

Customizable scripts like this help tap the full power of Google Sheets

Transcripts

good evening my name is Tom kalinosky

I'm the director of technology and

operations at Colonial Intermediate Unit

xx welcome to tech tips number 66 this

tip is a result of one of our viewers

questions the question was is it

possible to sort data in a column but

not just one time but continuously so

anytime new data is added the data would

Auto sort and it is so thanks to Mike

Brandt's key on github he is a Google

script that does just that and I'm going

to show you how to do it so the first

thing we do is we have ourself a

spreadsheet you can see I put test and

Row 1 there with a couple of random

numbers there below let's go to tools

script editor that brings up this blank

page here let's go to Mike's script and

I'll put a link to this in the

description so you can grab it and we

want to select all of his codes so start

at the top click and hold and slowly go

down then you get to the end right here

after that let's right click and copy ok

go back to our script window let's make

sure to delete anything that's in there

isn't it so we'll just highlight delete

now let's paste and hit save on the name

doesn't really matter in this case we'll

put keep data sorted

now there's three things that we need to

change and the script does tell you

exactly what you need to change but

we're going to go down here and I'm

going to show you exactly where it is

so sort column index which column needs

to be sorted so he tells you here a is

equals 1 B is equal to 2 so in this case

our data was in column a so we're gonna

put of 1 there and do we want it to be a

sending or descending

so a sending let's uh we want ours to be

a sending so we're gonna make this true

if you wanted to be descending it would

be false and the number of header rows

so how many rows at the top are static

or titles for the data in this case it

is just 1 let's go ahead and hit save

again you could close this now and now

let's go ahead and reload our document

okay and you can see there that that

data sorted it all by itself now if we

were to go to add any other numbers in

here it again will sort by itself so

let's put something that'll be up at the

top let's go 10 hit enter and you can

see it put it up at the top if we do

something in between let's say 75 and it

puts it better and if we put something

at the way end it should stay there

that's a way that you could keep your

data continuously sorted special thanks

again to Mike Bronski for coming up with

that code that we could use inside of

Google sheets I hope you found this tip

valuable if you have any other

suggestions or questions please feel

free to put it in the comments below

don't forget to subscribe and hit the

bell on that we can become aware of any

new content that I post have a great day

and I'll see you again next time bye