Pushing data to specific sheets depending upon cel

问题:

Ok so I have some code here that I think should work, but doesn't. I want it so that if the first column of any sheet has the status "complete" it pushes to the "Completed Tasks" sheet. However for some reason it doesn't work. I'm not sure if it's a syntactical problem or not.

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

  if((s.getName() == "Beau" || "Derek" || "Jay" || "Steven" || "Terence" || "Victor") && r.getColumn() == 1 && r.getValue() == "Complete") {
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var targetSheet = ss.getSheetByName("Completed Tasks");
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
    s.getRange(row, 1, 1, numColumns).moveTo(target);
    s.deleteRow(row);
  }
}

Here is the link to my sheet. As you can see on the "Beau" sheet there is some information, but it hasn't pushed it anywhere.

https://docs.google.com/spreadsheets/d/1_HQJ2UK6R3QTLY9oeVe-PIaZngPN1luQAz633taE4Oo/edit?usp=sharing

I would also like it so that if information is entered onto the front "To Do" sheet, it checks the "Assigned To" cell and pushes it to the correct person's sheet. We had it much simpler before but by separating out the sheets it has made it very complicated for me (With my little experience)

Thank you!

回答1:

See if this works (disable your current onEdit first):

function onEdit(e) {
var ss = e.source,
    s = ss.getActiveSheet(),
    sheets = ["Beau", "Derek", "Jay", "Steven", "Terence", "Victor"];
if (sheets.indexOf(s.getName()) === -1 || e.range.columnStart !== 1 || e.value !== 'Complete') return;
ss.getSheetByName('Complete Tasks')
    .appendRow(e.range.offset(0, 0, 1, 7)
        .getValues()[0])
s.deleteRow(e.range.rowStart);
}

回答2:

I've never seen the syntax s.getName() == "Beau" || "Derek" || "Jay" || "Steven" || "Terence" || "Victor" and aparently it doesn't work, if you log the above, it will always return "Derek", the second name. To overcome this create an object/array with the names and check if the name exist on them.

Eg.

function onEdit(event) {
  var ss = SpreadsheetApp.getActiveSpreadsheet(),
      s = event.source.getActiveSheet(),
      r = event.source.getActiveRange(),
      allowed = {"Beau" : 1 , "Derek" : 1 , "Jay" : 1 , "Steven" : 1 , "Terence" : 1 , "Victor" : 1};

  if( allowed[ s.getName() ] && r.getColumn() == 1 && r.getValue() == "Complete") {
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var targetSheet = ss.getSheetByName("Completed Tasks");
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
    s.getRange(row, 1, 1, numColumns).moveTo(target);
    s.deleteRow(row);
  }
}

回答3:

s.getName() == "Beau" || "Derek" || "Jay" || "Steven" || "Terence" || "Victor"

Won't work. You have to use:

s.getName() == "Beau" || s.getName() == "Derek" || s.getName() == "Jay" || s.getName() == "Steven" || s.getName() == "Terence" || s.getName() == "Victor"

But, there's no reason to keep using the getName() method over and over again. Create a variable:

var nameToCheck = s.getName();
Logger.log('the name is: ' + nameToCheck);

if (nameToCheck == "Beau" || nameToCheck === "Derek" || nameToCheck === "Jay" || nameToCheck === "Steven" || nameToCheck === "Terence" || nameToCheck === "Victor")