0

enter image description here

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});
    }
}
TheMaster
  • 45,448
  • 6
  • 62
  • 85
Benjamin Urrutia
  • 15
  • 5
  • What does the alert show? Your getCell is missing `()` – TheMaster Dec 09 '21 at 20:22
  • The alert don´t show anything, the problem is that the code no sort me the range. – Benjamin Urrutia Dec 09 '21 at 21:00
  • I had copied the code wrong, in my code the get cell is right, I just fixed it. – Benjamin Urrutia Dec 09 '21 at 21:02
  • In this `spreadsheet.getRange('cox:y').activate()` cox and y are no longer variables...they are just part of a string. Perhaps you meant this `${cox}:${y}` – Cooper Dec 09 '21 at 21:11
  • so I would have to express it like this? `spreadsheet.getRange(${cox}:${y}).activate() .sort({column: 4, ascending: true});` – Benjamin Urrutia Dec 09 '21 at 21:17
  • now the alert show me that this line have a error : `var col = e.range.getColumn();` – Benjamin Urrutia Dec 09 '21 at 22:22
  • why this variable is incorrect? – Benjamin Urrutia Dec 09 '21 at 22:23
  • getCell() requires two parameters – Cooper Dec 10 '21 at 02:43
  • Your code really makes no sense I think it would be better to explain what you want to accomplish is words and picture because you code makes no sense at all. – Cooper Dec 10 '21 at 02:44
  • I just upload a image, so my idea is: if someone change a number of the column D the range C386 until the column D and the row depending the amount of names of the column C, Which I Count with the function in B385, sort them in base to the column D . I hope you understand me @Cooper – Benjamin Urrutia Dec 10 '21 at 13:56
  • How can someone change a number in column d of C386 since C386 is a cell in Column c.? I have no idea what you are taking about. I am coming to think that your thinking is way beyond my ability to understand so I think I should move on and let someone more intelligent have an opportunity to assist you. – Cooper Dec 10 '21 at 14:53
  • Although I understand, you really need to work on your communication skills. Punctuation, grammar are all important. – TheMaster Dec 10 '21 at 15:29
  • I am sorry I do not have a good level of English, my native language is Spanish. I will try to express myself as clearly as possible. I will express my idea again: If someone change a number of the column D, the range "C386:D392" will sort by the Column D. This is the idea in simple words. But the problem is that in the whole sheet are a lot of tables like this, which I want that they sort by them self and not write code in the function on `edit()` for each of this tables. @TheMaster @Cooper – Benjamin Urrutia Dec 10 '21 at 16:04
  • Check my answer below. – TheMaster Dec 10 '21 at 16:10

2 Answers2

1

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

Cooper
  • 59,616
  • 6
  • 23
  • 54
  • I think it also should be `cox.getValue()` and `y.getValue()`, but not sure about those variables. OP needs to clarify. – TheMaster Dec 09 '21 at 22:05
  • @TheMaster it could be but before it was written in A1Notation. The whole script is rather foolish and the entire onedit is very poorly organized there is virtually no control on unwanted triggers. – Cooper Dec 09 '21 at 22:34
  • I invented a range to try if the code work, but i don´t why the part of getcell is giving me a mistake. so I Write the code of the following way and it´s work `if(col == 4){ 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); var cox = sheet.getRange(x,col-1); spreadsheet.getRange('C386:D389').activate() .sort({column: 4, ascending: true}); }; ` – Benjamin Urrutia Dec 09 '21 at 23:12
  • but if I don´t use the getcell I can´t give a reference to the next command to sort me the range with my variables – Benjamin Urrutia Dec 09 '21 at 23:13
0
  • 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);
  }
}
TheMaster
  • 45,448
  • 6
  • 62
  • 85
  • Finally... Works!!! Thank you so much for the solution and for putting up with me in spite of my poor English level A last question, what I need to do if I want to put a variable in the values of `c` and `d` in `sheet.getRange(`C${topRow}:D${bottomRow}`).sort(4);` because if you see my table have like anothers similar sections in where I need to do the same – Benjamin Urrutia Dec 10 '21 at 17:08
  • @BenjaminUrrutia Could you ask a new question? With representative sample(Your current screenshot only shows 1 table). Where would be the count of E386:F{count}? What if there are different number of names in column C and column D? Explain in detail in your new question. Also try to find the answer yourself, modify your code, test it before posting a new question. See https://meta.stackoverflow.com/a/284237/ and https://meta.stackoverflow.com/questions/261592/ – TheMaster Dec 10 '21 at 17:42
  • @BenjaminUrrutia Also [this](https://stackoverflow.com/questions/21229180/convert-column-index-into-corresponding-column-letter) (especially [this](https://stackoverflow.com/a/53678158/))will help in your testing – TheMaster Dec 10 '21 at 17:44