Solved

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

Posted on 2016-11-29
8
46 Views
Last Modified: 2016-11-29
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

0
Comment
Question by:bede123
  • 5
  • 2
8 Comments
 
LVL 33

Accepted Solution

by:
Norie earned 500 total points
ID: 41905841
Instead of using moveTo use copyTo to only copy/paste values.
s.getRange(row, 1, 1, numColumns).copyTo(target), {contentsOnly: true});
 

Open in new window

0
 
LVL 1

Author Comment

by:bede123
ID: 41906135
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

0
 
LVL 1

Author Comment

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

Open in new window


spot the subtle difference.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 1

Author Comment

by:bede123
ID: 41906197
please note i ment to close this question and award points to Norie.

please can admin assist with this

zac
0
 
LVL 33

Expert Comment

by:Norie
ID: 41906216
Oops, sorry about the typo - was posting from my phone.:)
0
 
LVL 1

Author Comment

by:bede123
ID: 41906222
not at all. not probs. i appreciate the help

zac
0
 
LVL 1

Author Comment

by:bede123
ID: 41906282
closed question without awarding points by mistake
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Explore the encryption capabilities built into Google Apps and how these features can help you meet privacy policy and regulatory compliance, but are not a full solution. Understand and compare the most popular email encryption services for Google A…
JavaScript can be used in a browser to change parts of a webpage dynamically. It begins with the following pattern: If condition W is true, do thing X to target Y after event Z. Below are some tips and tricks to help you get started with JavaScript …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now