Solved

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

Posted on 2016-11-29
8
70 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
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
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

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
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…

623 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