Pushing data to specific sheets depending upon cell information

0
votes

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!

3
google-apps-scriptgoogle-sheets
To debug your code, use a Logger.log("my variable value is: " + variableName);, and then VIEW the LOGS. You can also add a Logger.log('It ran!'); inside the condition code to see if the condition tested true. – Alan Wells

3 Answers

1
votes

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);
}
0
votes

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);
  }
}
0
votes
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")