Need to check the First empty column cellule ( Column B ) not the entire First empty row (if Column C is not empty)

[anne marie Gay]

Hello I have a script that works fine but I need a little modification This script scans the first totally empty line Ex if one of the cells C2, D2, E2 ect... are filled but B2 empty Then position yourself in B2 I have to modify the Script to position itself in the spreadsheet at the first Empty cell of Column B only and not on the completely Empty Row Thank you for your help

Hello 

j 'ai un script qui fonctionne très bien mais j'ai besoin d'une petite modification 

Ce script scrute la première ligne totalement vide

Ex si une des cellule C2,D2,E2 ect...   sont remplies mai B2 vide Alors se positionner en B2

Je dois modifier le Script pour se positionner dans le tableur à la première cellule Vide de la Colonne B uniquement et non sur la Ligne complètement Vide

Merci de votre aide

https://script.google.com/home/projects/1hzfPghhxVuNLvJOGcHcX-9QSmcN0jv-vbxV83Qy7S0nnYa1ij82AnkGG/edit

function FacturerActe() {

// var doc = DocumentApp.getActiveDocument().getText();

// traceurpharma();

var regexp = /[^0-9]*/g ;// extrait la chaine de caractère avant la chaine numérique

var doc = DocumentApp.getActiveDocument().getText();

var result = regexp.exec(doc);

var PrenomNom = new RegExp(result,"gm");

Logger.log(PrenomNom.getText);

var ss = SpreadsheetApp.openByUrl(" https://docs.google.com/spreadsheets/d/JsDLhFc-Ruw1xxxxxxxxxxIFSQBdit#gid=918750899");//Février 2022

//var date = Utilities.formatDate(new Date(), ss.getSpreadsheetTimeZone() , "0"+"d"+"-0"+"M"+"-"+"y");

var date = Utilities.formatDate(new Date(), ss.getSpreadsheetTimeZone() , "dd"+"-"+"MM"+"-"+"y");

var sheet = ss.getSheetByName(date);

ss.setActiveSheet(sheet);

var cell = sheet.getRange("A40");

cell.setNote("Aujourd'hui est un nouveau jour ! Nous sommes le :"+date);

selectFirstEmptyRow (); // Place le curseur sur la premiere ligne Vide de la Colonne "B"

}

//* Placez le curseur de l'utilisateur actuel dans la première cellule de la première ligne vide.

//*

function selectFirstEmptyRow () {

var ss = SpreadsheetApp.openByUrl(" https://docs.google.com/spreadsheets/d/JsDLhFc-Ruw1xxxxxxxxxxIFSQBdit#gid=918750899");//Février 2022

var date = Utilities.formatDate(new Date(), ss.getSpreadsheetTimeZone() , "dd"+"-"+"MM"+"-"+"y");

var time = Utilities.formatDate(new Date(), ss.getSpreadsheetTimeZone() , "HH"+":"+"mm");

var sheet = ss.getSheetByName(date);

var regexp = /[^0-9]*/g ;// extrait la chaine de caractère avant la chaine numérique

var doc = DocumentApp.getActiveDocument().getText()

var result = regexp.exec(doc);

//* Extrait les blancs

// var regexp = /\s[A-Z a-z]+/g ;// insert un sautde ligne devant la chaine et extrait les espaces devant et derriere Nom Prenom

var regexp = /[A-Z a-z]+/g ;// extrait les espaces devant et derriere Nom Prenom

// var regexp = /[^a-z\s-]+[A-Za-z\s-]+/g; // extrait les espaces devant et derriere Nom Prenom Demande la Présence de XX Ans (caché en blanc dans l'ordo paramédical)

// Accepte les Prenoms minuscules avec la premiere lettre en Maj Ex Prenom Nom Prenom Prenom2-Prenom3 Nom

var regexp2 = new RegExp("[^a-zA-Z\\s]", "g");

//var result = result.replace(regexp2,'');

var result = regexp.exec(result);

// var regexp = /\s[A-Z a-z]+/g ;// extrait les espaces devant et derriere Nom Prenom

// var result = regexp.exec(result);

sheet . setActiveSelection ( sheet . getRange ( "B" + getFirstEmptyRowWholeRow ())).setValue(result[0]) ;

Logger.log(result.getText);

sheet . setActiveSelection ( sheet . getRange ( "H" + getFirstEmptyRowWholeRowI ())).setValue(time) ;

Logger.log(time.getText);

sheet . setActiveSelection ( sheet . getRange ( "I" + getFirstEmptyRowWholeRowI ()).setFormulaR1C1('=R[0]C[-1]-R[-1]C[-1]')) ;

Logger.log(time.getText);

}

/**

* " Trouve la première ligne vide la Colonne "B" " de checker de Mogsdad.

*/

function getFirstEmptyRowWholeRow () {

var ss = SpreadsheetApp.openByUrl(" https://docs.google.com/spreadsheets/d/JsDLhFc-Ruw1xxxxxxxxxxIFSQBdit#gid=918750899");//Février 2022

var date = Utilities.formatDate(new Date(), ss.getSpreadsheetTimeZone() , "d"+"-0"+"M"+"-"+"y");

var date = Utilities.formatDate(new Date(), ss.getSpreadsheetTimeZone() , "dd"+"-"+"MM"+"-"+"y");

var sheet = ss.getSheetByName(date);

var range = sheet . getDataRange ();

var values = range . getValues ();

var row = 1 ;

for ( var row = 1 ; row < values . length ; row ++) {

if (! values [ row ]. join ( "" )) break ;

}

return ( row + 1 );

}

function getFirstEmptyRowWholeRowI () {

var ss = SpreadsheetApp.openByUrl(" https://docs.google.com/spreadsheets/d/JsDLhFc-Ruw1xxxxxxxxxxIFSQBdit#gid=918750899");//Février 2022

var date = Utilities.formatDate(new Date(), ss.getSpreadsheetTimeZone() , "d"+"-0"+"M"+"-"+"y");

var date = Utilities.formatDate(new Date(), ss.getSpreadsheetTimeZone() , "dd"+"-"+"MM"+"-"+"y");

var sheet = ss.getSheetByName(date);

var range = sheet . getDataRange ();

var values = range . getValues ();

var row = 1 ;

for ( var row = 1 ; row < values . length ; row ++) {

if (! values [ row ]. join ( "" )) break ;

}

return ( row );

}

///// Fin Facturer Acte ////

[Brett Grear]

I'm not sure I understood you correctly but if you are just looking for the first row in Column B that is blank you could do something like:

var sheet = ss.getActiveSheet();

var data = sheet.getRange(1,2,sheet.getLastRow()).getValues() //This gets every cell in Column B

for (var i in data) {
  if(data[i][0]=='') {

     var firstBlankCell = sheet.getRange(+i+1,2);

     break;
  }
}

Then you can do whatever you want with the first blank cell.

There is probably even faster ways to do this.

[Secrétariat]

Thanks a lot Brett 🙏

Please Can I insert into my current script and where?

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/36bd8789-16e2-440c-9058-97b1fc9cde6dn%40googlegroups.com.