Click here to Skip to main content
15,945,119 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
In Google Sheets, I have been trying to copy data from Sheet "Main" to Sheets (Completed, Pending, Hold) based on selection from dropdown in column 6 named (Status) in Main Sheet.

Column 6 DropDown List (Completed, Pending, Main)

Once a specific record has been changed it copies the record to its specific sheet without deleting the record from the Main sheet.

So far I have got this from elsewhere, however this does not seem to work, can someone please help.

What I have tried:

function onEdit(event) {

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

If(s.getName() == "Main" && r.getColumn() == 6 && r.getValue() == "Completed"); {
  var row = r.getRow();
	var numColumns = s.getLastColumn();
	var targetSheet = ss.getSheetByName("Completed");
	var target = targetSheet.getRange(targetSheet.getLastRow() +1, 1);
	s.getRange(row, 1, 1, numColumns).moveTo(target);
	s.deleteRow(row);
}

If(s.getName() == "Main" && r.getColumn() == 6 && r.getValue() == "Pending"); {
  var row = r.getRow();
var numColumns = s.getLastColumn();
var targetSheet = ss.getSheetByName("Pending");
var target = targetSheet.getRange(targetSheet.getLastRow() +1, 1);
s.getRange(row, 1, 1, numColumns).moveTo(target);
s.deleteRow(row);
}

If(s.getName() == "Main" && r.getColumn() == 6 && r.getValue() == "Hold"); {
  var row = r.getRow();
	var numColumns = s.getLastColumn();
	var targetSheet = ss.getSheetByName("Hold");
	var target = targetSheet.getRange(targetSheet.getLastRow() +1, 1);
	s.getRange(row, 1, 1, numColumns).moveTo(target);
	s.deleteRow(row);
	}
}
Posted
Updated 31-May-23 22:38pm
v3
Comments
Richard MacCutchan 1-Jun-23 4:11am    
"however this does not seem to work"
What exactly does that mean?

I see a few syntax errors -
1. Remove the semicolon (;) after each if statement.
2. Add curly braces ({}) to group the code blocks within each if statement.
3. Add an additional if block to check if the edited cell is in the correct sheet and column -
(s.getName() == "Main" && r.getColumn() == 6)

4. Create a new variable 'status' to store the value of the edited cell, which will be used to determine the correct target sheet (Completed, Pending, Main).
5. Change the logic to set the targetSheet based on the status value returned.
6. Add a check to ensure a valid target sheet is found when selected in dropdown.
7. Uncommented the s.deleteRow(row); line as you specified that the line should not be deleted from the Main sheet.

The following should work fine -
function onEdit(event) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = event.source.getActiveSheet();
  var r = event.source.getActiveRange();

  if (s.getName() == "Main" && r.getColumn() == 6) {
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var targetSheet;
    var status = r.getValue();

    if (status == "Completed") {
      targetSheet = ss.getSheetByName("Completed");
    } else if (status == "Pending") {
      targetSheet = ss.getSheetByName("Pending");
    } else if (status == "Hold") {
      targetSheet = ss.getSheetByName("Hold");
    }

    if (targetSheet) {
      var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
      s.getRange(row, 1, 1, numColumns).moveTo(target);
      // Uncomment the following line if you want to delete the row from the Main sheet
      // s.deleteRow(row);
    }
  }
}


Make sure to save the script and enable the corresponding trigger in the Google Sheets interface.
 
Share this answer
 
v2
Comments
Mohammad Haneef Ahmad 1-Jun-23 4:25am    
Tried the sheet however it deletes the record from the Main Sheet, I did uncomment and then commented the last line of the code, but it does not work. the record gets deleted anyway, any suggestion, appreciate yr help
Andre Oosthuizen 1-Jun-23 6:15am    
You can change the 'movrTo to copyTo which should keep the row in your Main sheet -
s.getRange(row, 1, 1, numColumns).copyTo(target);
In addition to Andre's suggestion, you could simplify this function by removing all the duplicate statements:
JavaScript
function onEdit(event) {

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

  if(s.getName() == "Main" && r.getColumn() == 6) {
    if(r.getValue() == "Completed") {
      var targetSheet = ss.getSheetByName("Completed");
    }
    else if(r.getValue() == "Pending") {
      var targetSheet = ss.getSheetByName("Pending");
    }
    else if (r.getValue() == "Hold") {
      var targetSheet = ss.getSheetByName("Hold");
    }
    else {
      // invalid selection
    }
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
    s.getRange(row, 1, 1, numColumns).moveTo(target);
    s.deleteRow(row);
  }
}

Also I notice in your original you are using If with a capital I instead of if (all lower case).
 
Share this answer
 
Comments
Mohammad Haneef Ahmad 1-Jun-23 6:13am    
@Richard MacCutchan The solution works however why does the line(record) from the Main Sheet gets deleted, I simply want them to move to their respective sheet without erasing it from the Main sheet, i tried uncommenting the last line code //s.deleteRow(row);
Richard MacCutchan 1-Jun-23 7:54am    
If you move an item from A to B then it will no longer exist in A. If you want it to remain in A then use CopyTo instead. And remove the deleteRow call as it is redundant if you are using moveTo, and not necessary if you want to keep the row in Main.
Andre Oosthuizen 1-Jun-23 6:17am    
See my comment above about the copyTo and MoveTo.
Richard MacCutchan 1-Jun-23 7:55am    
Snap.
Andre Oosthuizen 1-Jun-23 15:48pm    
:)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900