CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Tyler Newland
Tyler Newland

Reputation: 73

Trouble with an onEdit script utilizing if and else if

This script was written to be used in a company project log to help keep track and a history of old jobs.

All the "if" and "else if" statements work as they are intended, except one little snafu I can't seem to pinpoint and I am hoping some can see what I have obviously overlooked.

Example of my snafu:

Say I am on sheetName("Parking lot") and I change a name in column (4), it will fire my "if" statement and that's the problem: it should only be firing the "if else" associated with that sheetName. I only want that statement to fire when I am on the sheetName("Waiting to be assigned").

I have also tried using ss.getName() inside my statement and that didn't seem to make a difference. 

function onEdit() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var newDate = Utilities.formatDate(new Date(), "PST", "M/d/yyyy");
  var nonMemberTracking = memberTracking();

  var currentSheet = ss.getSheetByName("Current Projects");
  var currentRange = currentSheet.getRange(currentSheet.getLastRow() + 1, 1);
  var currentColumnToWatch = 15;
  var currentActiveCell = currentSheet.getActiveCell();

  var currentRangez = currentSheet.getRange("A3:T"); 


  var parkinglotSheet = ss.getSheetByName("Parking lot");
  var parkinglotRange = parkinglotSheet.getRange(parkinglotSheet.getLastRow() + 1, 1); 
  var parkinglotColumnToWatch = 15;
  var parkinglotActiveCell = parkinglotSheet.getActiveCell();

  var completedSheet = ss.getSheetByName("Completed");
  var completedRange = completedSheet.getRange(completedSheet.getLastRow() + 1, 1); 
  var completedColumnToWatch = completedSheet.getRange("O2:O").getValue();
  var completedActiveCell = completedSheet.getActiveCell();

  var waitingSheet = ss.getSheetByName("Waiting to be assigned");
  var waitingColumnToWatch = 4;
  var waitingActiveCell = waitingSheet.getActiveCell();
  var waitingRange = waitingSheet.getRange(waitingSheet.getLastRow() + 1, 1); 

  currentRangez.sort([{ column : 4, ascending: true},{ column : 11, ascending: true}])

  if(waitingActiveCell.getColumn() == waitingColumnToWatch && waitingActiveCell.getValue() !== ""){


    currentSheet.insertRowAfter(currentSheet.getLastRow());
    waitingSheet.getRange(waitingActiveCell.getRow(), 9, 1).setValue("YES");
    waitingSheet.getRange(waitingActiveCell.getRow(), 1, 1, waitingSheet.getLastColumn()).moveTo(currentRange);
    waitingSheet.deleteRow(waitingActiveCell.getRow());
    currentSheet.sort(4);


  } else if(currentActiveCell.getColumn() == currentColumnToWatch && currentActiveCell.getValue() == "Parking lot"){

    parkinglotSheet.insertRowAfter(parkinglotSheet.getLastRow());
    currentSheet.getRange(currentActiveCell.getRow(), 1, 1, currentSheet.getLastColumn()).moveTo(parkinglotRange);
    currentSheet.deleteRow(currentActiveCell.getRow());
    parkinglotSheet.sort(4);

  } else if(currentActiveCell.getColumn() == currentColumnToWatch && currentActiveCell.getValue() == "Complete"){

    completedSheet.insertRowAfter(completedSheet.getLastRow());
    currentSheet.getRange(currentActiveCell.getRow(), 1, 1, currentSheet.getLastColumn()).moveTo(completedRange);
    currentSheet.deleteRow(currentActiveCell.getRow());
    completedSheet.sort(4);
    nonMemberTracking;

  } else if(parkinglotActiveCell.getColumn() == parkinglotColumnToWatch && parkinglotActiveCell.getValue() !== "Parking lot"){

    currentSheet.insertRowAfter(currentSheet.getLastRow());
    parkinglotSheet.getRange(parkinglotActiveCell.getRow(), 1, 1, parkinglotSheet.getLastColumn()).moveTo(currentRange);
    parkinglotSheet.deleteRow(parkinglotActiveCell.getRow());
    currentSheet.sort(4);

  }}

Upvotes: 1

Views: 509

Answers (2)

iJay
iJay

Reputation: 4293

I agreed to @robin-gertenbach , Plus you would do this too.

  • Select Resources -> Current project's triggers. -> Click link to add one now.
  • Under Run, select the function you want executed by the trigger. (In your case "onEdit(e)")
  • Under Events, select From
  • Under Events, select From Spreadsheet.
  • From the next drop-down list, select On On edit. Click Save.

Have fun!

Upvotes: 0

Robin Gertenbach
Robin Gertenbach

Reputation: 10816

You probably tried to do something like this: 

function onEdit(e) {
  if (SpreadsheetApp.getActiveSheet().getName() == "Waiting to be assigned") {
    // Wall of text
  }
}

The issue here is that you got the name of the Spreadsheet, not the Worksheet.

A slightly more elegant solution is using the e parameter.
e is an event Object that contains various information about the event that activated the trigger.
So your function would look like this:

function onEdit(e) {
  if (e.range.getSheet().getName() == "Waiting to be assigned") {
    // Wall of text
  }
}

Obviously with the range you can also check if it was really column 4 that was edited.

Upvotes: 2

Related Questions