Google Script To Copy row based on Drop down, Not working - Stack Overflow

admin管理员组

文章数量:1394778

I am trying to copy the entire row (From the calls tab) if certain items are selected in the J column. Basically if any of the items are selected in the drop down menu of the J column except "Check Ring Central Time". That row should be copied over to the Sheet Errors Tab. And it should stay on the Errors tab even if the original calls tab is cleared.

But the script is copying everything on every row. It does stay on the Sheet Errors tab. So I guess I got the first and the last part correct. It copy's and it stays. I just don't know why it is not copying based only on the J column

I tested the script and it worked perfect...lol But my sheet was already filled in when I got the script working. So the only column I edited was the J column. And it was perfect. I am terrible at this. I want to understand it. I try hard. But ... I copied the script to the working sheets and then figured out it does not work. It copies every single edit on every single cell on a new line per edit per cell. It is a mess. But I still feel like it is close???? Link to sheet: text

function onEdit(event) {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = event.source.getActiveSheet();
  var r = event.source.getActiveRange();

  if(s.getName() == "Calls" && r.getColumn() == 10 && r.getValue() == "Report not justified Daily","Special not listed Correctly","Phone number does not match Ring Central","Credit not listed in note section","Possibly double billed Call Connie","Possibly should have been refunded Call Connie","Amount in terminal does not match report","Error See Highlights") {
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var targetSheet = ss.getSheetByName("Sheet Errors");
    var target = targetSheet.getRange(targetSheet.getLastRow()+1,1);
    s.getRange(row, 1, 1, numColumns).copyTo(target);


  }
}type here

I am trying to copy the entire row (From the calls tab) if certain items are selected in the J column. Basically if any of the items are selected in the drop down menu of the J column except "Check Ring Central Time". That row should be copied over to the Sheet Errors Tab. And it should stay on the Errors tab even if the original calls tab is cleared.

But the script is copying everything on every row. It does stay on the Sheet Errors tab. So I guess I got the first and the last part correct. It copy's and it stays. I just don't know why it is not copying based only on the J column

I tested the script and it worked perfect...lol But my sheet was already filled in when I got the script working. So the only column I edited was the J column. And it was perfect. I am terrible at this. I want to understand it. I try hard. But ... I copied the script to the working sheets and then figured out it does not work. It copies every single edit on every single cell on a new line per edit per cell. It is a mess. But I still feel like it is close???? Link to sheet: text

function onEdit(event) {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = event.source.getActiveSheet();
  var r = event.source.getActiveRange();

  if(s.getName() == "Calls" && r.getColumn() == 10 && r.getValue() == "Report not justified Daily","Special not listed Correctly","Phone number does not match Ring Central","Credit not listed in note section","Possibly double billed Call Connie","Possibly should have been refunded Call Connie","Amount in terminal does not match report","Error See Highlights") {
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var targetSheet = ss.getSheetByName("Sheet Errors");
    var target = targetSheet.getRange(targetSheet.getLastRow()+1,1);
    s.getRange(row, 1, 1, numColumns).copyTo(target);


  }
}type here

• google-sheets
• google-apps-script
• google-sheets-formula

Share Improve this question Follow asked Dec 3, 2024 at 1:32 SueDoNymnSueDoNymn 333 bronze badges 0

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

Copy row values with exception

The main issue lies in the syntax of your if condition it commas does not work like you intended, if you want to check for multiples values on your condition, you should use the || symbol. See Apps Script conditional and look for the OR and !== section.

function onEdit(event) {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = event.source.getActiveSheet();
  var r = event.source.getActiveRange();

  if(s.getName() == "Calls" && r.getColumn() == 10 && r.getValue() !== "Check Ring Central Time") {
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var targetSheet = ss.getSheetByName("Sheet Errors");
    var target = targetSheet.getRange(targetSheet.getLastRow()+1,1);
    s.getRange(row, 1, 1, numColumns).copyTo(target);
  }
}

Sample Output

Calls sheet

Sheet Errors sheet

References: Apps Script conditions

本文标签： Google Script To Copy row based on Drop downNot workingStack Overflow