Google sheet app script - Copy data from one sheet to another based on condition with loop-CodePudding

I am new to the forum and new to Google App Script. I have a sheet with multiple tabs. I want to do is probably very simple, but seems quite complex to me, coming from VBA...

I would like to: IF Facture(lastrow, 5) = 1 THEN copy-paste DetailDevis(i,1) to DetailFacture(lastrow,3) every time DetailDevis(i,2) = Facture(lastrow,2)

I have made the following, and i would love to get feed back as of why it does not work...

    function onEdit()
{
 var ss = SpreadsheetApp.getActiveSheet()
 var Facture = ss.getSheetByName("FactureDevis") ;
 var FactureDetail = ss.getSheetByName("DetailFactureDevis") ;
 var DevisDetail = ss.getSheetByName("DetailDevis") ;
 var FactureLR = Facture.getLastrow() ;
 var Situ1R = Facture.getRange(FactureLR,5) ;
 var Situ1V = Situ1R.getValues() ;
 var FactureDevisR = Facture.getRange(FactureLR,2) ;
 var FactureDevisV = FactureDevisR.getValues() ;
 var DevisDetailLR = DevisDetail.getLastrow() ;
 var FactureDetailLR = FactureDetail.getLastrow() ;
if (Situ1V == 1)
{
    for (var i = 2 ; i <= DevisDetalLR ; i  ) {
 if (DevisDetail.getRange(i,2).getValue() == FactureDevisV)
 {
DevisDetail.getRange(i,1).copyTo(FactureDetail.getRange(FactureDetailLR,3);
}}}

Here are some additional questions:

Is getLastrow() dynamic?
Is the place you set your var crucial? Or can you set them all at the beginning?

CodePudding user response：

Try this:

function onEdit(e) {
  var sh1 = e.source.getSheetByName("FactureDevis");
  var sh2 = e.source.getSheetByName("DetailFactureDevis");
  var sh3 = e.source.getSheetByName("DetailDevis");
  var sh1lr = sh1.getLastrow();
  var Situ1V = sh1.getRange(sh1lr, 5).getValue();
  var FactureDevisV = sh1.getRange(sh1lr, 2).getValue();
  var sh3lr = sh3.getLastrow();
  var sh2lr = sh2.getLastrow();
  if (Situ1V == 1) {
    for (var i = 2; i <= sh3lr; i  ) {
      if (sh3.getRange(i, 2).getValue() == FactureDevisV) {
        sh3.getRange(i, 1).copyTo(sh2.getRange(sh2lr, 3));
      }
    }
  }
}

sheet.getLastRow() is a function so each time you call it, it recalculates the value.
I create my variables before I need them.

CodePudding user response：

I have changed the whole idea. Instead of copying each row in one sheet and pasting int into another sheet, i have gone for a filtering option:

function create_filter(){
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    const s1 = ss.getSheetByName("DetailDevis");
    const s2 = ss.getSheetByName("FactureDevis");
    const s3 = ss.getSheetByName("DetailFactureDevis");
    const s3lr = s3.getLastRow();
    const range = s1.getRange("B:B");
    const datatocheck = s2.getRange(s2.getLastRow(),2).getValue();
    const filter = range.createFilter();
    const Filter_Criteria1 = SpreadsheetApp.newFilterCriteria().whenTextContains(datatocheck);
    const coll1 = 2;
    
    const add_filter1 =  filter.setColumnFilterCriteria(coll1,Filter_Criteria1);

  const range2 = s1.getRange("A2:A");


  range2.copyTo(s3.getRange(s3lr,3));

filter.remove();
}

A new question has arisen now. How can I trigger this function if a row is added to the FactureDevis sheet?

Thanks