Solved

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

Posted on 2016-11-29
8
21 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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
If your app took Google’s lash recently, here are the 5 most likely reasons.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial demonstrates in Google Analytics how to create a custom report that shows you traffic over time using the month of year dimensions. There are also instructions on how to fix Google's odd month of year formatting, which Microsoft …

760 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

18 Experts available now in Live!

Get 1:1 Help Now