CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Nayte
Nayte

Reputation: 13

Moving data from one row to another in the same sheet with a check box

I currently have a row with dropdown lists and I want to move the data in this row to a separate list within the same sheet once the choices are selected and a checkbox at the end of the row is marked, then clear the initial row of choices.

It's only 1 row (row 3) with 5 columns of data (Col A, B, C, and D/E merged and the checkbox in F). I would like the data to be moved to row 10 and continue down the list each time data is entered and the box is checked in row 3.

function onEdit(event) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var s = event.source.getActiveSheet();
var r = event.source.getActiveRange();

if(s.getName() == "WarMistakes" && r.getColumn() == 6 && r.getValue() == true) {

var row = r.getRow();
var targetSheet = ss.getSheetByName("WarMistakes");
var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
s.getRange(row, 9, 1, numColumns).moveTo(target);
s.deleteRow(row);
}

}

Upvotes: 0

Views: 83

Answers (1)

Marios
Marios

Reputation: 27390

Try this:

function onEdit(e) {

  const row = e.range.getRow();
  const col = e.range.getColumn();
  const s = e.source.getSheetByName("SourceSheet");
  const ts = e.source.getSheetByName("WarMistakes");
  const as = e.source.getActiveSheet();

  if(as.getName() == "SourceSheet" && col == 6 && row == 3 && as.getRange(row,col).getDisplayValue() === "TRUE") {  
    
    const data = s.getRange(row, 1, 1, 5).getValues();  
    if(ts.getLastRow()>=10){ s.getRange(s.getLastRow()+1,1,data.length,data[0].length).setValues(data);}
    else{ts.getRange(10,1,data.length,data[0].length).setValues(data);}   
    s.getRange(3,6).setValue("FALSE");
    s.getRange(row, 1, 1, 5).clearContent();
    
  }
}

Explanation:

As soon as you click on cell F3, the data in SourceSheet from A-D/E are moved to WarMistakes row 10 and the script unticks the checkbox. If there is a content in row 10, then it appends it right after and so on. Please use the same sheet names that I used: SourceSheet, WarMistakes or adjust the code to your own needs.

Step 1 (you click the checkbox in cell F3 in SourceSheet):

Step1

Step 2 (data was moved to row 10 in WarMistakes):

Step2

Upvotes: 1

Related Questions