Question 179621 in Javascript Question posted in
A very good W3school tutorial can be found here.

Javascript – Copying data in Google sheet using dropdown

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.

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);
}
}
Tags:

2

Answers


  1. 0

    In the “Active” tab, cell C3 put:

    =ARRAYFORMULA(
   FILTER(Setup!B3:D,Setup!F3:F))

    And in the “Active” tab, cell O3 put:

    =ARRAYFORMULA(
   FILTER(Setup!I3:K,Setup!M3:M))

    A checked checkbox is interpreted as “TRUE” so basically these formulas look at columns with the checkboxes and if check return the values in the specified cells next to them.

    If you want to make it cleaner you can also wrap each of them in an IFNA or IFERROR function to avoid showing an error when nothing is checked.

    Login or Signup to reply.
  2. 0

    Try this:

    function onEdit(e) {
  const src = e.source.getActiveSheet();
  const r = e.range;
  const v = e.value
  //Logger.log("DEBUG: source name = "+src.getName()+", row = "+r.rowStart + ", column = "+r.columnStart+", value = "+v)
  
  if (src.getName() != "New" || r.columnStart != 6 || r.rowStart == 1 || v != "TRUE") return;
  const dest = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Active");
  // Logger.log("DEBUG: source range = "+src.getRange(r.rowStart,2,1,3).getA1Notation()+" destination range = "+dest.getRange(dest.getLastRow()+1,1,1,3).getA1Notation())
  src.getRange(r.rowStart,1,1,3).copyTo(dest.getRange(dest.getLastRow()+1,2,));
}

    Notes

    • r.columnStart != 6 – tests for an edit in Column F
    • v != "TRUE" – tests for value of "TRUE" (i.e. checkbox is checked)
    • .copyTo – copies the data rather than moves it.
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search