Link to home
Start Free TrialLog in
Avatar of Zac123
Zac123Flag for United Kingdom of Great Britain and Northern Ireland

asked on

remove data validation/drop downs when moving row to another sheet

Hi All,

i have this bit of code that moves a row of data if the user selects 'Yes'. it moves the row to a tab called 'Completed' and deletes the row from the original tab, called 'dataEntry'.
it works fine but what i'd like to do now is have all the data validation removed so that when 'completed' sheet is just data, no drop downs.

here is my code
 function runOnEdit2(event) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = event.source.getActiveSheet();
  var r = event.source.getActiveRange();

  if(s.getName() == "dataEntry" && r.getColumn() == 9 && r.getValue() == "Yes") {
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var targetSheet = ss.getSheetByName("Completed");
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1); 
    s.getRange(row, 1, 1, numColumns).moveTo(target);
    
    //This puts the completion date into the last column of data currently col H
    targetSheet.getRange(targetSheet.getLastRow(),numColumns  + 0).setValue(new Date());
    //Calculates the turn around time in days for column I on the Completed sheet
    targetSheet.getRange(targetSheet.getLastRow(),numColumns  + 1).setFormula("=DATEDIF(R[0]C[-9],R[0]C[-1],\"D\")+1");
    s.deleteRow(row);
  }
}

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Norie
Norie

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Zac123

ASKER

hiya, thanks for that. when i click save it says there is a missing ; on that line.  my code looks like this:
function runOnEdit2(event) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = event.source.getActiveSheet();
  var r = event.source.getActiveRange();

  if(s.getName() == "dataEntry" && r.getColumn() == 9 && r.getValue() == "Yes") {
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var targetSheet = ss.getSheetByName("Completed");
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1); 
    s.getRange(row, 1, 1, numColumns).copyTo(target), {contentsOnly: true});
    
    //This puts the completion date into the last column of data currently col H
    targetSheet.getRange(targetSheet.getLastRow(),numColumns  + 0).setValue(new Date());
    //Calculates the turn around time in days for column I on the Completed sheet
    targetSheet.getRange(targetSheet.getLastRow(),numColumns  + 1).setFormula("=DATEDIF(R[0]C[-9],R[0]C[-1],\"D\")+1");
    s.deleteRow(row);
  }
}

Open in new window

Avatar of Zac123

ASKER

a ha:
 s.getRange(row, 1, 1, numColumns).copyTo(target, {contentsOnly:true});

Open in new window


spot the subtle difference.
Avatar of Zac123

ASKER

please note i ment to close this question and award points to Norie.

please can admin assist with this

zac
Avatar of Norie
Norie

Oops, sorry about the typo - was posting from my phone.:)
Avatar of Zac123

ASKER

not at all. not probs. i appreciate the help

zac
Avatar of Zac123

ASKER

closed question without awarding points by mistake