I have a table as shown above. I'm looking to sort the range C386:D392 based on column D, whenever there's a edit in Column D.

C386 comes from the top of the table.
D392 comes from 386 to number of filled rows in column C. I already made a count of column C in B385.

I don't know why is not working. The variable "cox" and "y" are my two variables to create the range I want to sort

function onEdit(e) {

   var sheet = SpreadsheetApp.getActiveSheet();
   var spreadsheet = SpreadsheetApp.getActive()
   var d = SpreadsheetApp.getActiveSheet().getRange('D:D').getColumn();
   var row = e.range.getRow();
   var col = e.range.getColumn();
  
  
   if(col == d){
    var valor = sheet.getRange(row,1).getValue();
    var x = sheet.getRange(row-valor,3).getRow();
    var cant = sheet.getRange(row-valor-1,2).getValue();
    var y = sheet.getRange(x cant,4).getCell();
    var cox = sheet.getRange(x,col-1).getCell();
    spreadsheet.getRange(`${cox}:${y}`).activate()
    .sort({column: 4, ascending: true});
    }
}

CodePudding user response：

Template Literals

It hard to do in a comment. But the inner expression in the range must surrounded by back ticks like this.

spreadsheet.getRange(`${cox}:${y}`).activate()  .sort({column: 4, ascending: true});

I believe these are called template literals and you can learn more about them here

CodePudding user response：

• Use template literals
• Hardcode C and D, if you're only using C:D
• Depending on usage, getDataRegion() may also help in your case.

/**
 * @param {GoogleAppsScript.Events.SheetsOnEdit} e
 */
function onEdit(e) {
  const sheet = e.range.getSheet();
  const row = e.range.getRow();
  const col = e.range.getColumn();
  if (col === 4) {
    const indexFromA = sheet.getRange(row, 1).getValue();
    const topRow = row - indexFromA;
    const countFromB = sheet.getRange(topRow - 1, 2).getValue();
    const bottomRow = topRow   countFromB;
    sheet.getRange(`C${topRow}:D${bottomRow}`).sort(4);
  }
}