dimanche 9 août 2020

Loop through sheet1, copying rows that have a sales date to sheet2, then delete entry on sheet1

So I'm trying to create a script that will search through all rows on sheet1 and any row that has a sales date will be copied over to sheet2. All results that are copied over are deleted from sheet1. Could anyone help?

Here's a sample sheet with both what I have and what I'd like to be the result:

https://docs.google.com/spreadsheets/d/1UvhOIx6dZv8CqIBVvb8B1Sl9sHkxiJqB0_PwEncoKCo/edit?usp=sharing

my code so far: (I've tried many things but I'm still pretty new to this stuff)

function myFunction() {
  var ss = SpreadsheetApp.openById("[SheetID]");
  var startSheet = ss.getSheetByName("Sheet1");
  var saleSheet = ss.getSheetByName("Sheet2");
  var lastRow = startSheet.getLastRow();
  var lastCol = startSheet.getLastColumn();
  var range = startSheet.getRange(2, 1, lastRow-1, lastCol).getValues();
  var saleDates = startSheet.getRange(2, 11,lastRow-1).getValues();
  for(var i=lastRow;i>1;i--){
    if(saleDates[i] != "") {
      saleSheet.appendRow(range[i]);
      startSheet.deleteRow([i]);
    }else{
      continue;
    }
    
    
  };
};

Aucun commentaire:

Enregistrer un commentaire