I am trying to automatically apply a filter to a column - and sort the sheet in descending order by-CodePudding

I'm trying to have the spreadsheet automatically sort by Column B, in descending order. However the raw data as the "-" text so I'm using the formula in Column C "=abs(B2)" and then trying to filter by Column C instead.

I would like the sheet to automatically apply the filter and then sort when edits are made. I've been playing with the Apps Script but cannot get it to work.

Any help would be appreciated!

    function myFunction() {
      var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
      ss.getRange ("C2").setFormula("=abs(B2)");
    
      var lr = ss.getLastRow();
      var fillDownRange = ss.getRange (2,3, lr-1);
      ss.getRange("C2").copyTo(fillDownRange);      
    }
    
    function autosort(){    
      const ss = SpreadsheetApp.getActiveSpreadsheet()
      const ws = ss.getSheetByName("Trustlines")
      const range = ws.getRange(2,1,ws.getLastRow()-1,3)
    
      range.sort({column: 3, ascending: false})    
    }
    
    
    function onEdit(e){    
      const row = e.range.getRow()
      const column = e.range.getColumn()
    
      if(!(column === 3 && row>= 2)) return
    
      autosort()    
    }

CodePudding user response：

You probably do not want to get your data from IMPORTHTML. The main reason for that is that the onEdit trigger is not triggered unless the user changes some data (witch is probably not what you want to use).

What I would propose instead is to make all the calculations using Apps Script and only adding to the spreadsheet the result. This will be done following this steps:

Get the page source. This can be done using UrlFetchApp.
Interpret the source and extract the data. XmlService should allow you to do so.
Filter and sort the data. Use the native sort and filter JavaScript Array methods.
Add the result as values on the sheet.

References