Modify Code - Select Certain Columns onEdit. Google Apps Script / Google Sheets

Question

I cannot get this code to do what I am trying to get it to do. If there is a TRUE in Column 10 then run the Sort Query on SendToTotalSales - NOT ONLINERELOCATION

function MoveDonations(e) {
var sh=e.range.getSheet();
if(sh.getName()!='ONLINERELOCATION')return
if(e.range.columnStart==10 && e.value=="TRUE") {

e.source.getActiveSheet().getRange(row,????).setFormula('=SORT(QUERY(ONLINERELOCATION!A2:J,"SELECT A, 
F, G, D",0))'); 

}
//var tsh=e.source.getSheetByName("SendToTotalSales");
//var trg=tsh.getRange(tsh.getLastRow()+1,1);
//sh.getRange(e.range.rowStart,1,1,4).copyTo(trg);
}

I am hoping I was Close !

Thanks in Advance

In your title, it seems that you want to do Select Certain Columns onEdit.. But in your question, it seems that you want to do If there is a TRUE in Column 10 then run the Sort Query on SendToTotalSales. I have to apologize for my poor English skill. Can I ask you about your current issue and goal? — Tanaike
Tanaike-列10 = trueの場合、SendToTotalSalesという名前のタブで並べ替えクエリを実行したい — Tom Sawkins
Thank you for replying. If you want to put the formula of '=SORT(QUERY(ONLINERELOCATION!A2:J,"SELECT A, F, G, D",0))' at the sheet of SendToTotalSales, when MoveDonations is run by the OnEdit event trigger, you want to modify to e.source.getSheetByName("SendToTotalSales").getRange(row,????).setFormula(###). But from your question, I'm not sure where range you want to put the formula. I apologize for this. — Tanaike
Tanaike-列10のチェックボックスをオンにすると、並べ替えクエリはA、F、D、G列をSendToTotalSalesにエクスポートする必要があります — Tom Sawkins
Tanaike - Thank you once again for your help. Code worked perfectly. — Tom Sawkins

Tanaike Tanaike · Accepted Answer · 2020-02-26T03:26:29

When the checkbox of the column "J" in the sheet of ONLINERELOCATION is checked, you want to copy the values of the columns "A,D,F,G" to the sheet of SendToTotalSales as "A,F,G,D".
You want to achieve this using Google Apps Script.

If my understanding is correct, how about this answer? Please think of this as just one of several possible answers.

Sample script:

In this case, you can also use the simple OnEdit event trigger (onEdit(e)).

function MoveDonations(e) {
  var range = e.range;
  var sheet = range.getSheet();
  if (sheet.getSheetName() == "ONLINERELOCATION" && range.columnStart == 10 && range.columnEnd == 10 && range.rowStart >= 2 && e.value == "TRUE") {
    var [[a,,,d,,f,g]] = sheet.getRange(range.rowStart, 1, 1, 7).getValues();
    e.source.getSheetByName("SendToTotalSales").appendRow([a, f, g, d]);
  }
}

In order to run the script, please check the checkbox of the column "J" in the sheet of ONLINERELOCATION. By this, the values of the row are copied to SendToTotalSales.

Note:

I think that when '=SORT(QUERY(ONLINERELOCATION!A2:J,"SELECT A, F, G, D",0))' is used, all values are put. So I proposed above script.
And I'm not sure whether the duplicate process is required for your situation.

Modify Code - Select Certain Columns onEdit. Google Apps Script / Google Sheets

1 Answers

Sample script:

Note: