Moving Rows to Another Tab based on criteria between 2 columns - Having Trouble

[Jake Scott-Hodes]

I could use some real help here.

I have 3 tabs: Pipeline, Success, Loss

I have 2 columns I want to trigger an event: Success, Loss

Success column has a checkbox. The value checked is "Success". 

Loss column has a dropdown with various text options.

When Success column is checked, I'd like the corresponding row to disappear from Pipeline Tab and move to Success Tab.

When any option is chosen from dropdown menu in Loss column, I'd like the corresponding row to disappear from Pipeline and move to Loss Tab.

I'd like each row-move to simply clear out the row in pipeline tab so I can keep my formatting, and for the new target tabs to add rows respective to the additions.

I can use the following script to make either Success or Loss work, but not both. And in some cases, checking the success box moves it to the Loss tab for no reason I can see.

Link to Google Sheet here

Move to Success script:

function onEdit(e) {

var ss = e.source;

var activatedSheetName = ss.getActiveSheet().getName();

var activatedCell = ss.getActiveSelection();

var activatedCellRow = activatedCell.getRow();

var activatedCellColumn = activatedCell.getColumn();

var activatedCellValue = activatedCell.getValue();

var Pipeline = ss.getSheetByName("Pipeline"); // source sheet

var Success = ss.getSheetByName("Success"); // target sheet

// if the value in column D is "Success", move the row to target sheet

if (activatedSheetName == Pipeline.getName() && activatedCellColumn == 9 && activatedCellValue == "Success") {

// insert a new row at the second row of the target sheet

Success.insertRows(2,1);

// move the entire source row to the second row of target sheet

var rangeToMove = Pipeline.getRange(/*startRow*/ activatedCellRow, /*startColumn*/ 1, /*numRows*/ 1, /*numColumns*/ Pipeline.getMaxColumns());

rangeToMove.moveTo(Success.getRange("A2"));

// delete row from source sheet

Pipeline.deleteRows(activatedCellRow,1);

}

}

Move to Loss script:

function onEdit(e) {

var ss = e.source;

var activatedSheetName = ss.getActiveSheet().getName();

var activatedCell = ss.getActiveSelection();

var activatedCellRow = activatedCell.getRow();

var activatedCellColumn = activatedCell.getColumn();

var activatedCellValue = activatedCell.getValue();

var Pipeline = ss.getSheetByName("Pipeline"); // source sheet

var Loss = ss.getSheetByName("Loss"); // target sheet

// if the value in column D is "Loss", move the row to target sheet

if (activatedSheetName == Pipeline.getName() && activatedCellColumn == 10 && activatedCellValue == "$", "Time", "$ and Time", "No Reply", "Stone") {

// insert a new row at the second row of the target sheet

Loss.insertRows(2,1);

// move the entire source row to the second row of target sheet

var rangeToMove = Pipeline.getRange(/*startRow*/ activatedCellRow, /*startColumn*/ 1, /*numRows*/ 1, /*numColumns*/ Pipeline.getMaxColumns());

rangeToMove.moveTo(Loss.getRange("A2"));

// delete row from source sheet

Pipeline.deleteRows(activatedCellRow,1);

}

}

[Adam Morris]

In this case, appsscript might be overkill. From what I can tell, you probably can achieve this by using a query formula on the spreadsheet.

--
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/84159c69-469d-4123-a5de-bfdd06afe7d2o%40googlegroups.com.

--

http://classroomtechtools.com

[Jean-Luc Vanhulst]

You can have only have onEdit function in one spreadsheet? So you need to combine them with a proper if statement ?

--


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/84159c69-469d-4123-a5de-bfdd06afe7d2o%40googlegroups.com.

--

Jean-Luc Vanhulst - President - Write2market.com
Cell: 347-453-7686

[jakea...@gmail.com]

Would you mind elaborating? I'm not familiar with how this function could be used. Thanks!

[jakea...@gmail.com]

How would I go about combining with a proper if statement? I'm not familiar with scripts

[cbmserv...@gmail.com]

I don’t think you need another if statement, what you have is enough, I just merged the 2 onEdit functions into 1 for you.

 

Replace your 2 onEdit functions with this:

 

function onEdit(e) {

var ss = e.source;

var activatedSheetName = ss.getActiveSheet().getName();

var activatedCell = ss.getActiveSelection();

var activatedCellRow = activatedCell.getRow();

var activatedCellColumn = activatedCell.getColumn();

var activatedCellValue = activatedCell.getValue();

var Pipeline = ss.getSheetByName("Pipeline"); // source sheet

var Success = ss.getSheetByName("Success"); // target sheet

var Loss = ss.getSheetByName("Loss"); // target sheet

 

// if the value in column D is "Success", move the row to target sheet

if (activatedSheetName == Pipeline.getName() && activatedCellColumn == 9 && activatedCellValue == "Success")

{

  // insert a new row at the second row of the target sheet

  Success.insertRows(2,1);

  // move the entire source row to the second row of target sheet

  var rangeToMove = Pipeline.getRange(/*startRow*/ activatedCellRow, /*startColumn*/ 1, /*numRows*/ 1, /*numColumns*/ Pipeline.getMaxColumns());

  rangeToMove.moveTo(Success.getRange("A2"));

  // delete row from source sheet

  Pipeline.deleteRows(activatedCellRow,1);

}

// if the value in column D is "Loss", move the row to target sheet

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/72179420-3bb9-43fb-b33b-3ce26c09ad37n%40googlegroups.com.