Solved

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

Posted on 2016-11-29
8
63 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
8 Comments
 
LVL 34

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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
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 34

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Today, the web development industry is booming, and many people consider it to be their vocation. The question you may be asking yourself is – how do I become a web developer?
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

739 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