Forked from mikebranski/Keep Column Sorted in Google Sheets.gs
Last active
May 12, 2021 19:40
-
-
Save BenoitDuffez/71dada8033e733d57f251d92de638ba4 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/** | |
* 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. | |
*/ | |
// Define the multi-column sorting rules. One column after the other, one per line. | |
// Column index starts at 1 (A=1, B=2, etc.). | |
var SORTING = [ | |
{ column: 1, ascending: true }, | |
{ column: 3, ascending: false } | |
// ... etc | |
]; | |
// 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(SORTING); | |
} | |
/** | |
* 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); | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
i have this problem:
TypeError: Cannot read property 'getDataRange' of undefined
autoSort @ Kod.gs:66
how to solve it?