Want to auto sort a Google sheets spreadsheet on edit, but want to sort by date

Question

Been looking around for a solution to this for a while and not finding anything that works, unfortunately. I have tried copying and pasting several bits of code but cannot get my sales pipeline sheet to automatically sort on edit of a date field.

The link below is to a test sheet which should give an idea as to how I have formatted my sheet:

https://docs.google.com/a/trinitymirror.com/spreadsheets/d/1huTvgwfmh0RM0kz4BUEuYFZm7DwBG-69D8jTfps3IPg/edit?usp=sharing

I want to sort by Column G with the most recent dates at the bottom of the sheet.

Any suggestions welcome!

Thank you in advance for your help.

https://webapps.stackexchange.com/a/14265/188352 – x29a Jun 07 '18 at 11:44 — x29a, Jun 07 '18 at 11:44

OblongMedulla · Accepted Answer · 2017-04-21T16:26:23.107

5

this should help?

function onEdit(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheetByName("Sheet1")
  var range = sheet.getRange("A2:Z450");

 // Sorts by the values in column 2 (B)
 range.sort({column: 2, ascending: false});
  
}

edited Apr 21 '17 at 16:26

answered Apr 21 '17 at 13:55

OblongMedulla

1,305
8
19

Thank you, but although that works on the test sheet when I copy it to the spreadsheet I am working on, it doesn't sort. Also, how can I sort it so that it doesn't affect the header? BTW I have Triggers on, current set to onEdit - on spreadsheet - onedit. Thank you – Simon Flavin Apr 21 '17 at 14:31
are you working on the first sheet- indicated by the getsheets()[0] - you could try to use get sheet by name instead. change the range to A2:Z999 – OblongMedulla Apr 21 '17 at 14:35
1

Yes I am working on the first sheets. Also after changing the range to A2:Z999 I get an error message: Service Error: Spreadsheets – Simon Flavin Apr 21 '17 at 14:39
Received this error: TypeError: Cannot call method "getRange" of null. (line 4, file "Code") – Simon Flavin Apr 21 '17 at 15:59
1

Changed Sheets1 to Pipeline which is the name of the sheet, and now i get this error again: Service error: SpreadsheetsDismiss – Simon Flavin Apr 21 '17 at 16:01
do you have less then 450 rows? try to decrease this number – OblongMedulla Apr 21 '17 at 16:05
1

That's worked thank you! And what do I add if I want it Ascending rather than descending? – Simon Flavin Apr 21 '17 at 16:12
you will want to change the sheet title and the row number to a smaller number as you did before. – OblongMedulla Apr 21 '17 at 16:34
1

Ignore that, I changed the range to a lesser amount again. Changed to Y200. – Simon Flavin Apr 21 '17 at 16:34
1

Thank you for your help! – Simon Flavin Apr 21 '17 at 16:34
I have realised that as soon as I delete a row, the range needs modifying. Is there anyway to edit the range so that it automatically updates should I delete a row? – Simon Flavin Apr 24 '17 at 11:39

Want to auto sort a Google sheets spreadsheet on edit, but want to sort by date

1 Answers1