Can anyone help me troubleshoot a script to move/delete a row from one sheet to another in google sheets apps script?

Ashley Constandi 0
Viewed 22

I have a google sheet called "Inventory" below

https://docs.google.com/spreadsheets/d/1hYkTQFhTREh-d-6kuKhDAMZUOYKu4pWyFPh61EmEIPo/edit?usp=sharing

Row K of Inventory contains a dropdown menu, I am attempting to move and delete the row if the value "Sold" is selected and add it to the next tabbed sheet called "Delivery Status." I would like it to append to the 2nd row below the headers on the destination sheet. I also want the action to be reversed if "Returned to Catalog" is selected on the Delivery Status sheet. Any help is greatly appreciated!

What am I missing here?:

function onEdit(e){
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sourceSheet = e.range.getSheet();
    if(sourceSheet.getSheetName() === 'Inventory'){
        var row = e.range.getRow();
        var rowRange = sourceSheet.getRange(row, 1, 1, sourceSheet.getLastColumn());
        var rowValues = rowRange.getValues()[0];
        if(rowValues[10] === "Sold"){
            var targetSheet = ss.getSheetByName("Delivery Status");
            targetSheet.insertRowAfter(1);
            var targetRange = targetSheet.getRange(2, 1);
            rowRange.copyTo(targetRange, SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);      
            sourceSheet.deleteRow(row);
        }     
    } 
}

function onEdit(e){
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sourceSheet = e.range.getSheet();
    if(sourceSheet.getSheetName() === 'Delivery Status'){
        var row = e.range.getRow();
        var rowRange = sourceSheet.getRange(row, 1, 1, sourceSheet.getLastColumn());
        var rowValues = rowRange.getValues()[0];
        if(rowValues[10] === "Return to catalog"){
            var targetSheet = ss.getSheetByName("Inventory");
            targetSheet.insertRowAfter(1);
            var targetRange = targetSheet.getRange(2, 1);
            rowRange.copyTo(targetRange, SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);      
            sourceSheet.deleteRow(row);
        }     
    } 
}
google-sheets google-apps-script
1 Answers

Modification points:

  • In your showing script, there are 2 onEdit functions. In this case, when a cell is edited, one of them is run. By this, I think that your script is not correctly run.
  • And, in your script, when the value is copied from "Inventory" to "Delivery Status", a new row is inserted into the 2nd row using targetSheet.insertRowAfter(1). By this, the dropdown list of column "K" is not existing.
  • In your Spreadsheet, the values of the dropdown list of column "K" of "Delivery Status" sheet are "Sold" and "Return to Catalog". But, in your script, Return to catalog is used for comparing the values. In this case, if (rowValues[10] === "Return to catalog") {} is always false.

When these points are reflected in your script, how about the following modification patterns?

Pattern 1:

In this pattern, your showing script is modified.

function onEdit(e) {
  if (!onEdit1(e)) onEdit2(e);
}

function onEdit1(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sourceSheet = e.range.getSheet();
  if (sourceSheet.getSheetName() === 'Inventory') {
    var row = e.range.getRow();
    var rowRange = sourceSheet.getRange(row, 1, 1, sourceSheet.getLastColumn());
    var rowValues = rowRange.getValues()[0];
    if (rowValues[10] === "Sold") {
      var targetSheet = ss.getSheetByName("Delivery Status");
      targetSheet.insertRowAfter(1);
      targetSheet.getRange(3, 11).copyTo(targetSheet.getRange(2, 11));
      var targetRange = targetSheet.getRange(2, 1);
      rowRange.copyTo(targetRange, SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
      sourceSheet.deleteRow(row);
    }
    return true;
  }
  return false;
}

function onEdit2(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sourceSheet = e.range.getSheet();
  if (sourceSheet.getSheetName() === 'Delivery Status') {
    var row = e.range.getRow();
    var rowRange = sourceSheet.getRange(row, 1, 1, sourceSheet.getLastColumn());
    var rowValues = rowRange.getValues()[0];
    if (rowValues[10] === "Return to Catalog") {
      var targetSheet = ss.getSheetByName("Inventory");
      targetSheet.insertRowAfter(1);
      targetSheet.getRange(3, 11).copyTo(targetSheet.getRange(2, 11));
      var targetRange = targetSheet.getRange(2, 1);
      rowRange.copyTo(targetRange, SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
      sourceSheet.deleteRow(row);
    }
  }
}

Pattern 2:

In this pattern, the above script might be able to be modified a bit simpler as follows.

function onEdit(e) {
  // This object is created from the explanation of your question.
  const obj = {
    "Inventory": { condition: "Sold", dst: "Delivery Status" },
    "Delivery Status": { condition: "Return to Catalog", dst: "Inventory" },
  };
  const { range, source } = e;
  const sheet = range.getSheet();
  const sheetName = sheet.getSheetName();
  const rowRange = sheet.getRange(range.rowStart, 1, 1, sheet.getLastColumn());
  const v = rowRange.getValues()[0];
  if (!Object.keys(obj).includes(sheetName) || obj[sheetName].condition != v[10]) return;
  const targetSheet = source.getSheetByName(obj[sheetName].dst);
  targetSheet.insertRowAfter(1);
  targetSheet.getRange(3, 11).copyTo(targetSheet.getRange(2, 11));
  const targetRange = targetSheet.getRange(2, 1);
  rowRange.copyTo(targetRange, SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  sheet.deleteRow(range.rowStart);
}
Tanaike0
Related