I am trying to automatically sort my Google Sheet by date onEdit. This is the script I am using and it is working as I need it to. However, I have two more sheets in the same file I want this to work on, with the date column being in different columns between all of my sheets. Is there a way to adapt the following so it can automatically sort the data on multiple Sheets in the same file?
Thanks in advance for any help.
function onEdit(event){
var w = event.source.getSheetByName("Flights");//Replace Flights with the name of the worksheet you want to sort in
var c = w.getActiveCell();
var srtby = 4; //Change this to the position of the col you want to sort by
var t = "A2:Z"; // Change this to range of cells that you want to sort (don't include the column headings).
if(c.getColumn() == srtby){
var range = w.getRange(t);
range.sort( { column : srtby, ascending: true } );//change false to true if you want to sort in ascending order
}
}
In your situation, how about the following modification using a JSON object?
Please set object
in this function to your situation. "Flights": { srtby: 4, t: "A2:Z" }
is from your script. "Sheet2": { srtby: 1, t: "A2:E" }
is a sample value. Please set the sheet name as a key and srtby
and t
as the value.
function onEdit(event) {
// Please set this object to your situation.
const object = {
"Flights": { srtby: 4, t: "A2:Z" }, // This is from your script.
"Sheet2": { srtby: 1, t: "A2:E" }, // This is a sample.
};
const sheet = event.source.getActiveSheet();
const o = object[sheet.getSheetName()];
if (!o) return;
const { srtby, t } = o;
if (event.range.columnStart == srtby) {
var range = sheet.getRange(t);
range.sort({ column: srtby, ascending: true });
}
}
In this modification, when column ""D" of "Flights" sheet is edited, "A2:Z" is sorted by column "D". When column "A" of "Sheet2" is edited, "A2:E" is sorted by column "A".