CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
jlSta
jlSta

Reputation: 324

bug in Array : last value is 'true' of the first line

i would like to create a script to -archive people left the building from sheet "registre" to "archive" -delete empty rows or clear row for people left (cell not empty in OUT)

Herer is m y script:

   function archive() {
  //variables pour la feuille registre
  var registre = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Registre"); // le script se positionne sur la feuille Registre
  var lastRowReg = registre.getLastRow();   // obtenir le numero de la dernière ligne éditée
  var data = registre.getRange(2, 1, lastRowReg-1,7 ).getValues();
  //variables pour la feuille archive
  var archive = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Archive");
  var lastRowArch = archive.getLastRow();
  var target = new Array();
  //recherche
  for (var i = 0; i < data.length; ++i) { //création de la boucle qui va analyser chaque ligne du tableau

    if (data[i][6]=!'') { //pour les lignes la cellule heure de sortie est vide, 
      target.push(data[i]);
      egistre.getRange(i+2,1,1,7).clearContent();
      }
    if(target.length>0){
    archive.getRange(lastRowArch+1, 1, target.length,target[0].length).setValues(target); 
    }
  }

}

DEBUG: data=

[["miss", "muss", "gh", "bazar", "da45adazd54", "13h00", true], ["miss", "muss", "gh", "bazar", "da45adazd54", "13h00", "14h00"]]

Why true and not 13h45?

The document link

Upvotes: 0

Views: 126

Answers (1)

Serge insas
Serge insas

Reputation: 46802

Please try it like that :

function archive() {
  //variables pour la feuille registre
  var registre = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Registre"); // le script se positionne sur la feuille Registre
  var lastRowReg = registre.getLastRow();   // obtenir le numero de la dernière ligne éditée
  var data = registre.getRange(2, 1, lastRowReg-1,7 ).getValues();
  //variables pour la feuille archive
  var archive = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Archive");
  var lastRowArch = archive.getLastRow();
  var target = new Array();
  //recherche
  for (var i = 0; i < data.length; ++i) { //création de la boucle qui va analyser chaque ligne du tableau

    if (data[i][6]=='') { //pour les lignes la cellule heure de sortie est vide, 
      target.push(data[i]);
      registre.getRange(i+2,1,1,7).clearContent();
      }
    }
    if(target.length>0){
    archive.getRange(lastRowArch+1, 1, target.length,target[0].length).setValues(target); 
  }
}
  1. the write back from the array to the sheet must be placed outside of the loop
  2. The if statement was wrong (at least not corresponding to your comment and description...)

I test on a copy of your doc and it seems to work as expected... I didn't have the 'true' value.

EDIT following your comments:

Now I think I understood correctly what you were willing to do. I used a slightly different approach that works faster and more clearly (in my opinion)

I used 2 arrays, one with data for sheet 2 (archive) and one for the rows to keep (I called it newsource) so now I can clear the whole source sheet (except headers) and write the whole data in one shot without blank rows.

Here is the code, tested and working.

function archive() {
  //variables pour la feuille registre
  var registre = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Registre"); // le script se positionne sur la feuille Registre
  var lastRowReg = registre.getLastRow();   // obtenir le numero de la dernière ligne éditée
  var data = registre.getRange(2, 1, lastRowReg-1,7 ).getValues();
  //variables pour la feuille archive
  var archive = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Archive");
  var lastRowArch = archive.getLastRow();
  var target = new Array();
  var newsource = new Array();
  //recherche
  for (var i = 0; i < data.length; ++i) { //création de la boucle qui va analyser chaque ligne du tableau

    if (data[i][6]!='') { //pour les lignes la cellule heure de sortie est vide, 
      target.push(data[i]);
      }else{
      newsource.push(data[i]);
    }
  }
  if(target.length>0){
   archive.getRange(lastRowArch+1, 1, target.length,target[0].length).setValues(target); 
   }
  Logger.log(lastRowReg-1+' = '+newsource.length+' + '+target.length)
  registre.getRange(2, 1, lastRowReg-1,7 ).clearContent();
   if(newsource.length>0){
     registre.getRange(2,1,newsource.length,newsource[0].length).setValues(newsource);
    }
}

I added a Logger.log that shows a sort of "verification" of row counts to be sure no row is lost...

Upvotes: 1

Related Questions