-
-
Save mikebranski/285b60aa5ec3da8638e5 to your computer and use it in GitHub Desktop.
/** | |
* This Google Sheets script keeps data in the specified column sorted any time | |
* the data changes. | |
* | |
* After much research, there wasn't an easy way to automatically keep a column | |
* sorted in Google Sheets, and creating a second sheet to act as a "view" to | |
* my primary one in order to achieve that was not an option. Instead, I | |
* created a script that watches for when a cell is edited and triggers | |
* an auto sort. | |
* | |
* To Install: | |
* 1. Open your Google Sheet. | |
* 2. Navigate to Tools > Script editor… | |
* 3. Copy and paste this script in the editor. | |
* 4. Change the three constants at the start of the code below to reflect | |
* your preferences. | |
* - Note: My goal is to move these settings to a GUI and have this script | |
* be installable as an add-on. | |
* 5. Give the script a name (e.g. "Keep Data Sorted") and hit save. | |
* | |
* To Use: | |
* Simply edit your Google Sheet like normal. Any time you edit data in your | |
* sort column (specified in `SORT_COLUMN_INDEX`), the script will re-sort | |
* your rows. | |
* | |
* If you are having trouble getting it to work, try the following in order: | |
* 1. Reload your spreadsheet. | |
* 2. Open the script editor (Tools > Script editor…), click the "Select | |
* function" dropdown, choose `onInstall`, and hit Debug (the bug icon | |
* that precedes the dropdown). | |
* 3. If that doesn't work, reach out via GitHub (link below) and ask for | |
* help. You may also find that others have run into the same issue | |
* and have already posted a solution. | |
* | |
* @author Mike Branski (@mikebranski) | |
* @link https://gist.github.com/mikebranski/285b60aa5ec3da8638e5 | |
* | |
* @OnlyCurrentDoc Limits the script to only accessing the current spreadsheet. | |
*/ | |
// The numeric index of the column you wish to keep auto-sorted. A = 1, B = 2, | |
// and so on. | |
var SORT_COLUMN_INDEX = 2; | |
// Whether to sort the data in ascending or descending order. | |
var ASCENDING = false; | |
// If you have header rows in your sheet, specify how many to exclude them from | |
// the sort. | |
var NUMBER_OF_HEADER_ROWS = 1; | |
// No need to edit anything below this line for general use. | |
// Make an improvement? Ping me on GitHub and let me know! | |
// Keep track of the active sheet. | |
var activeSheet; | |
/** | |
* Automatically sorts on the pre-defined column. | |
* | |
* @param {Sheet} sheet The sheet to sort. | |
*/ | |
function autoSort(sheet) { | |
// Get the entire set of data for this sheet. | |
var range = sheet.getDataRange(); | |
// Then, if there are any header rows, offset our range to remove them from | |
// it; otherwise, they will end up being sorted as well. | |
if (NUMBER_OF_HEADER_ROWS > 0) { | |
// Setting the second parameter of offset() to 0 to prevents it from | |
// shifting any columns. Note that row headers wouldn't make much | |
// sense here, but this is where you would modify it if you | |
// wanted support for those as well. | |
range = range.offset(NUMBER_OF_HEADER_ROWS, 0); | |
} | |
// Perform the actual sort. | |
range.sort( { | |
column: SORT_COLUMN_INDEX, | |
ascending: ASCENDING | |
} ); | |
} | |
/** | |
* Triggers when a sheet is edited, and calls the auto sort function if the | |
* edited cell is in the column we're looking to sort. | |
* | |
* @param {Object} event The triggering event. | |
*/ | |
function onEdit(event) { | |
var editedCell; | |
// Update the active sheet in case it changed. | |
activeSheet = SpreadsheetApp.getActiveSheet(); | |
// Get the cell that was just modified. | |
editedCell = activeSheet.getActiveCell(); | |
// Only trigger a re-sort if the user edited data in the column they're | |
// sorting by; otherwise, we perform unnecessary additional sorts if | |
// the targeted sort column's data didn't change. | |
if (editedCell.getColumn() == SORT_COLUMN_INDEX) { | |
autoSort(activeSheet); | |
} | |
} | |
/** | |
* Runs when the sheet is opened. | |
* | |
* @param {Object} event The triggering event. | |
*/ | |
function onOpen(event) { | |
activeSheet = SpreadsheetApp.getActiveSheet(); | |
autoSort(activeSheet); | |
} | |
/** | |
* Runs when the add-on is installed; calls onOpen() to ensure any initializion | |
* work is done immediately. | |
* | |
* @param {Object} event The triggering event. | |
*/ | |
function onInstall(event) { | |
onOpen(event); | |
} |
Hello, I am trying to freeze an x number of columns but whatever code I put at the bottom of your script does not work! Please help!!
@mikebranski - is there a way to have this sorting feature only apply to the first sheet in my spreadsheet? It is currently sorting the other spreadsheets as well and I can't figure out how it make it only apply to the first one (where it is working beautifully).
Thanks!
@mikebranski I am using this on a spreadsheet linked to google froms. Is there a way to trigger this script once the spread sheet recieves the data from that form?
@mikebranski - is there a way to have this sorting feature only apply to the first sheet in my spreadsheet? It is currently sorting the other spreadsheets as well and I can't figure out how it make it only apply to the first one (where it is working beautifully).
Thanks!
I'd love to know the answer to this question too. I have multiple sheets in a google doc and it ordered all of my sheets
@mikebranski - is there a way to have this sorting feature only apply to the first sheet in my spreadsheet? It is currently sorting the other spreadsheets as well and I can't figure out how it make it only apply to the first one (where it is working beautifully).
Thanks!
@lwerickson3 @luisagradeless It's been a long while since I've done any Apps Script work, but something like this might work (untested):
var ss = SpreadsheetApp.getActiveSpreadsheet();
var first_sheet = ss.getSheets()[0];
var active_sheet = ss.getActiveSheet();
if (first_sheet.getSheetId() === active_sheet.getSheetId()) {
// do stuff
}
Love your script, but I have attached a ton of tabs to my original list and I only want the code active on one tab, is there a way to easily manage the script to only actively sort on tab by name?
I used this one on my event planning spreadsheet to automatically sort my google form responses by date - it has worked beautifully! I am wondering if there is a way to sort by date & time. I have the date, event start time & event end time within different columns. Thanks!
Is there a way to instead put the "completed" at the bottom of the sheet, put it in a different sheet instead?
Tọa độ của phạm vi nằm ngoài kích thước của trang tính. - Xin hãy tư vấn.
Please add a row at the end and do not fill in anything, the error will be fixed
it is only working when i refresh the page, what am i doing wrong?
Hi! I've been trying to figure this out for about an hour--for some reason the code is not being triggered. When I run the script it goes through, but nothing is happening on the sheet itself. Please help!
Hello I am trying to get it to auto update and for some reason it wont, also I added a few columns after having this script setup and now when I input scores it gives the scores to someone else rather than who I gave the points too in the Doc.