Copying data in Google sheet using dropdown

Question

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 31-May-23 15:49pm

Mohammad Haneef Ahmad

Updated 31-May-23 22:38pm

v3

Add a Solution

Comments

Richard MacCutchan 1-Jun-23 4:11am

"however this does not seem to work"
What exactly does that mean?

2 solutions

Add a Solution

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

Andre Oosthuizen · Accepted Answer · 2023-05-31T22:08:00

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.

Richard MacCutchan · Accepted Answer · 2023-05-31T22:38:00

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).

Copying data in Google sheet using dropdown

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Copying data in Google sheet using dropdown

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us