CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Tom Sawkins
Tom Sawkins

Reputation: 361

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

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

Upvotes: 1

Views: 84

Answers (1)

Tanaike
Tanaike

Reputation: 201553

  • 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.

Upvotes: 2

Related Questions