Move Row of Data Between Tabs Based on Checkbox (T/F) Columns

[Jennifer Wynkoop]

Hey all--

I'm working on updating a tracking system for a video production pipeline. The functionality I'm trying to achieve is to advance an entire row of data from one tab to another based on a column of checkboxes (T/F data). I also want the row to revert back to the original tab if the box gets unchecked.

I sourced the script I'm using from another thread I found on here. It worked for the first hour or so while I was reformatting the sheet, but abruptly stopped working and I'm not sure why. I've tried re-creating my sheet from the original template several times with the same results.

Can anyone assist in fixing this sheet's functionality, and help me understand how I might have been breaking the script? 

Here is a trouble-shooter copy of the document (view-only; please copy):

• https://docs.google.com/spreadsheets/d/1iXYDeflJhZT5QGwm3OfcqfeRzRHmJHsmLZr8GlMbPCU/edit?usp=sharing
  

Here is the script I'm using:

function onEdit(event) {

// assumes source data in sheet named Pre-Award

// target sheet of move to named Awarded

// getColumn with check-boxes is currently set to column 16 or P

var ss = SpreadsheetApp.getActiveSpreadsheet();

var s = event.source.getActiveSheet();

var r = event.source.getActiveRange();

if(s.getName() == "Pre-Award" && r.getColumn() == 16 && r.getValue() == true) {

var row = r.getRow();

var numColumns = s.getLastColumn();

var targetSheet = ss.getSheetByName("Awarded");

var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);

s.getRange(row, 1, 1, numColumns).moveTo(target);

s.deleteRow(row);

} else if(s.getName() == "Awarded" && r.getColumn() == 16 && r.getValue() == false) {

var row = r.getRow();

var numColumns = s.getLastColumn();

var targetSheet = ss.getSheetByName("Pre-Award");

var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);

s.getRange(row, 1, 1, numColumns).moveTo(target);

s.deleteRow(row);

}

}

function onEdit(event) {

// assumes source data in sheet named Awarded

// target sheet of move to named Delivered

// getColumn with check-boxes is currently set to column 26 or Z

var ss = SpreadsheetApp.getActiveSpreadsheet();

var s = event.source.getActiveSheet();

var r = event.source.getActiveRange();

if(s.getName() == "Awarded" && r.getColumn() == 26 && r.getValue() == true) {

var row = r.getRow();

var numColumns = s.getLastColumn();

var targetSheet = ss.getSheetByName("Delivered");

var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);

s.getRange(row, 1, 1, numColumns).moveTo(target);

s.deleteRow(row);

} else if(s.getName() == "Delivered" && r.getColumn() == 26 && r.getValue() == false) {

var row = r.getRow();

var numColumns = s.getLastColumn();

var targetSheet = ss.getSheetByName("Awarded");

var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);

s.getRange(row, 1, 1, numColumns).moveTo(target);

s.deleteRow(row);

}

}

Thank you for your help!

-Jen

[Michael O'Shaughnessy]

Hello Jen,

I think what you are running into is you have 2 "onEdit()" simple triggers that are "competing" with each other.  If you take a look at what you are needing to determine when something is edited is the sheet, the column and value(true or false).

Also you are "violating" a common coding axiom: DRY (Don't Repeat Yourself).  Look at this bit of your code:

if(s.getName() == "Pre-Award" && r.getColumn() == 16 && r.getValue() == true) {

var row = r.getRow();

var numColumns = s.getLastColumn();

var targetSheet = ss.getSheetByName("Awarded");

var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);

s.getRange(row, 1, 1, numColumns).moveTo(target);

s.deleteRow(row);

} else if(s.getName() == "Awarded" && r.getColumn() == 16 && r.getValue() == false) {

var row = r.getRow();

var numColumns = s.getLastColumn();

var targetSheet = ss.getSheetByName("Pre-Award");

var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);

s.getRange(row, 1, 1, numColumns).moveTo(target);

s.deleteRow(row);

}

The only difference in the "if/then/else" structure is sheet name. You can take some of this code and make it a function that takes the sheet name as input like this:

function updateInfo(ss,s,r,sheetName){

var row = r.getRow();

var numColumns = s.getLastColumn();

var targetSheet = ss.getSheetByName(sheetName);

var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);

s.getRange(row, 1, 1, numColumns).moveTo(target);

s.deleteRow(row);

}

And your code could change to:

if(s.getName() == "Pre-Award" && r.getColumn() == 16 && r.getValue() == true) {

updateInfo(ss,s,r,"Awarded")

} else if(s.getName() == "Awarded" && r.getColumn() == 16 && r.getValue() == false) {

updateInfo(ss,s,r,"Pre-Award")

}

Now, the issue of 2 onEdit's.. You can probably use a "switch" statement (info here: https://www.w3schools.com/js/js_switch.asp)

You would need to find out what was "edited" and select the case from there. For example

var caseNum

if(s.getName() == "Pre-Award" && r.getColumn() == 16 && r.getValue() == true) {caseNum = 1}

if(s.getName() == "Awarded" && r.getColumn() == 16 && r.getValue() == false) {caseNum = 2}

switch (caseNum){

case 1:

updateInfo(ss,s,r,"Awarded");

break;

case 2:

updateInfo(ss,s,r,"Pre-Award")

break;

}

Hope this helps and feel free to reach out if you have any questions.

Thanks,

Michael

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/8ffdf0d8-f456-4c57-913a-bbfcd1f5a861n%40googlegroups.com.