CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Spacey
Spacey

Reputation: 27

Move row to new tab after sheet updates automatically

I need to move rows from a tab based on a cell including "lep". The problem is that it doesn't read the info already on the sheet when it's opened (since it is a sheet that automatically updates new sales from Shopify via Zapier).

What would be the solution for this? I tried onEdit & onOpen already and neither work.

function onOpen(e) {
  // assumes source data in sheet named SHOPIFY
  // target sheet of move to named ORG
  // test column with yes is col 17 or Q
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = e.source.getActiveSheet();
  var r = e.source.getActiveRange();

  if(s.getName() == "SHOPIFY" && r.getColumn() == 17 && r.getValue().includes("lep")) {
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var targetSheet = ss.getSheetByName("ORG");
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
    s.getRange(row, 1, 1, numColumns).moveTo(target);
    s.deleteRow(row);
  }
}

Upvotes: 1

Views: 107

Answers (2)

Cooper
Cooper

Reputation: 64040

function moveRows() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('SHOPIFY');
  const tsh = ss.getSheetByName('ORG')
  const vs = sh.getDataRange().getValues();//you may wish to change the range to not include headers
  let d=0;
  vs.forEach((r,i)=>{
    if(r[16].toString().includes("lep")) {
      tsh.appendRow(r);
      sh.deleteRow(i+1-d++);
    }
  });
}

function onMyOpen() {
  //needs installable trigger
  moveRows();
}

Upvotes: 2

doubleunary
doubleunary

Reputation: 18766

An onEdit() simple trigger will only run when the user hand edits the spreadsheet.

An onOpen() simple trigger will only run when you open the spreadsheet in a web browser, and its event object does not include information about any recent changes in the spreadsheet.

To detect changes made by Zapier you will need an on change installable trigger. See the moveRowsFromSpreadsheetToSpreadsheet_ script for an example of how to do that.

Upvotes: 2

Related Questions