I have a problem when i when to get the range of a tab in the folowing code. When i=4, regardless of the tab i want to get, i have this error : "Exception: Cliquez ici et saisissez une des valeurs de la plage Ajouter date HT.gs:35" translated by "Exception: Click here and enter one of the range values Ajouter date HT.gs:35"
I tried in first time the getDataRange() then i tried with getLastRow and getLastColumn with a getRange() i had the same problem and now i tried the folowing code but i have the same problem.
I don't understand where come from the problem and chatGPT also.
The variables are in french but i translated the comments
function ajouter_date_HT() {
// The purpose of this code is to add the HT and nominal dates in the collab tabs according to certain conditions
var ss = SpreadsheetApp.getActiveSpreadsheet();
var onglets = ss.getSheets(); //We recover the tabs of the file
var nom_onglets = [];
var onglets_a_retirer = ["Param", "HT", "Compilation", "FLArchivDe", "ANDON", "Liste", "Dérogations", "Base Formules", "ImportedData"]; //We specify the tabs we want to remove
var date = new Date();
date.toLocaleDateString("fr");
var lignes_changees = "";
var formule_prio = '=IF(IFERROR(VLOOKUP("/!\";F2;1;0)>0);"HOT Topic";IF(ISBLANK(N2);"";IFERROR(VLOOKUP(N2;ANDON!A:F;6;FALSE); "Nominal")))';
var formule_supplier = "=IF(E2=\"\";\"\";VLOOKUP(E2;'Base Formules'!A$2:B$241;2;FALSE))";
var formule_appareil = '=IF(ISBLANK(N2);"";IFNA(VLOOKUP(LEFT(N2;1);\'Base Formules\'!L$2:M$16;2;FALSE);IF(COUNTIF(\'Base Formules\'!L:M; LEFT(N2;2)*1)=0;"Divers";VLOOKUP(LEFT(N2;2)*1;\'Base Formules\'!L$2:M$16;2;FALSE))))';
var formule_BE = '=IF(ISBLANK(O2);"";IFERROR(IF(ISNUMBER(VALUE(LEFT(O2;1)));IFERROR(VLOOKUP(CONCATENATE(MID(O2;5;2);"-";MID(O2;1;3));IMPORTRANGE("https://docs.google.com/spreadsheets/d/1ZgkOBXbnZE3ZCMVzdAjsvsBTSZgplpnkkATOHb4TeNE/edit#gid=1697538978";"BNAé!$A$2:$H$500");8;FALSE);VLOOKUP(CONCATENATE(MID(O2;5;2);"-");IMPORTRANGE("https://docs.google.com/spreadsheets/d/1ZgkOBXbnZE3ZCMVzdAjsvsBTSZgplpnkkATOHb4TeNE/edit#gid=1697538978";"BNAé!$A$2:$H$500");8;FALSE));IFERROR(VLOOKUP(CONCATENATE(MID(O2;2;3);"-";MID(O2;1;1));IMPORTRANGE("https://docs.google.com/spreadsheets/d/1ZgkOBXbnZE3ZCMVzdAjsvsBTSZgplpnkkATOHb4TeNE/edit#gid=1697538978";"ATA!$A$2:$H$500");6;FALSE);IFERROR(VLOOKUP(MID(O2;2;3);IMPORTRANGE("https://docs.google.com/spreadsheets/d/1ZgkOBXbnZE3ZCMVzdAjsvsBTSZgplpnkkATOHb4TeNE/edit#gid=1697538978";"ATA!$A$2:$H$500");6;FALSE);VLOOKUP(MID(O2;2;2);IMPORTRANGE("https://docs.google.com/spreadsheets/d/1ZgkOBXbnZE3ZCMVzdAjsvsBTSZgplpnkkATOHb4TeNE/edit#gid=1697538978";"ATA!$A$2:$H$500");6;FALSE))));"BE pas trouvé"))';
var formule_NC_mere = '=IF(D2<>"";"";IFERROR(IF(INDEX(ImportedData!M:M; MATCH(E2&"-"&O2&"-"&W2&"-"&X2; ImportedData!AP:AP; 0)) = L2; ""; INDEX(ImportedData!M:M; MATCH(E2&"-"&O2&"-"&W2&"-"&X2; ImportedData!AP:AP; 0))); "Pas de NC mère"))';
var formule_drive = '=IF(A2="Nominal"; ""; ' + 'IF(ISBLANK(L2); ""; ' + 'IFERROR(IF(COUNTIF(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1VTyKEcgNiHfj3KUOAWqn_qIohGtWt4JOUMIarzo-Hkk/edit#gid=0"; "HT follow-up!E:E"); L2)>=1; "OUI"; "NON"))))'+ '';
var lien_fichier_BE = '=HYPERLINK("' + "https://docs.google.com/spreadsheets/d/1ZgkOBXbnZE3ZCMVzdAjsvsBTSZgplpnkkATOHb4TeNE/edit#gid=0" + '"; "' + "Responsable action NOM" + '")';
onglets.forEach(function (sheet) { //We get only the name of the tabs that we put in "nom_onglets"
nom_onglets.push(sheet.getName());
});
for (let i = 0; i <= onglets_a_retirer.length; i++){ //We loop for each tab we want to remove and we look for it in the table. If so, we remove it from the list.
let index = nom_onglets.indexOf(onglets_a_retirer[i]);
if (index > -1) { //Seulement si on trouve l'élément
nom_onglets.splice(index, 1);
};
};
for (let i = 0; i < nom_onglets.length; i++){ //We loop on the tabs
let onglet_collab = ss.getSheetByName(nom_onglets[i]);
Logger.log(nom_onglets[i]);
let numRows = onglet_collab.getRange("A:A").getValues().filter(String).length;
let numCols = onglet_collab.getLastColumn();
let onglet_data_range = onglet_collab.getRange(1, 1, numRows, numCols);
let onglet_boucle = onglet_data_range.getValues();
for (let j = 1; j < onglet_boucle.length; j++){ //We loop on each rows of the tabs
if(onglet_boucle[j][3] == "" && onglet_boucle[j][32] == "" && onglet_boucle[j][34] != "DA Refused"){ //If it’s not a CO or a closure or a refusal then we continue
let prio = onglet_boucle[j][0]; //We get the prio of the row
let date_HT = onglet_boucle[j][1]; //We get the HT date of the row
let date_nominale = onglet_boucle[j][2]; //We get the nominal date of the row
if (prio == "" && date_HT == "" && date_nominale == ""){ //If it’s an empty row, we do nothing
} else if (prio != "Nominal" && date_HT != "" && date_nominale != ""){ //If it is an HT row with HT date and nominal date, the nominal date is deleted
onglet_boucle[j].splice(2, 1, "");
lignes_changees += "- onglet " + nom_onglets[i] + " ligne " + (j + 1) + "\n";
} else if (prio != "Nominal" && date_HT == ""){ //If it is a HT row without HT date, we put the date
onglet_boucle[j].splice(1, 1, date);
lignes_changees += "- onglet " + nom_onglets[i] + " ligne " + (j + 1) + "\n";
} else if (prio == "Nominal" && date_HT != "" && date_nominale == ""){ //If it is a nominal row with HT date, a nominal date is set
onglet_boucle[j].splice(2, 1, date);
lignes_changees += "- onglet " + nom_onglets[i] + " ligne " + (j + 1) + "\n";
}
}
}
Logger.log(onglet_boucle.length + " " + onglet_boucle[0].length);
onglet_collab.getRange(1, 1, onglet_boucle.length, onglet_boucle[0].length).setValues(onglet_boucle);
if(nom_onglets[i] != "Damien" && nom_onglets[i] != "Christophe"){ //We do not apply this formula for Damien and Christophe tabs
onglet_collab.getRange("U2:U").setFormula(formule_BE);
}
onglet_collab.getRange("A2:A").setFormula(formule_prio);
onglet_collab.getRange("F2:F").setFormula(formule_supplier);
onglet_collab.getRange("P2:P").setFormula(formule_appareil);
onglet_collab.getRange("AA2:AA").setFormula(formule_NC_mere);
onglet_collab.getRange("AN2:AN").setFormula(formule_drive);
onglet_collab.getRange("U1").setFormula(lien_fichier_BE);
}
Logger.log(lignes_changees);
}
Try this:
function ajouter_date_HT() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var excl = ["Param", "HT", "Compilation", "FLArchivDe", "ANDON", "Liste", "Dérogations", "Base Formules", "ImportedData"];
var shts = ss.getSheets().filter(sh => !~excl.indexOf(sh.getName())).filter(e => e);
var dt = new Date();
dt.toLocaleDateString("fr");
var v1 = "";
var f1 = '=IF(IFERROR(VLOOKUP("/!\";F2;1;0)>0);"HOT Topic";IF(ISBLANK(N2);"";IFERROR(VLOOKUP(N2;ANDON!A:F;6;FALSE); "Nominal")))';
var f2 = "=IF(E2=\"\";\"\";VLOOKUP(E2;'Base Formules'!A$2:B$241;2;FALSE))";
var f3 = '=IF(ISBLANK(N2);"";IFNA(VLOOKUP(LEFT(N2;1);\'Base Formules\'!L$2:M$16;2;FALSE);IF(COUNTIF(\'Base Formules\'!L:M; LEFT(N2;2)*1)=0;"Divers";VLOOKUP(LEFT(N2;2)*1;\'Base Formules\'!L$2:M$16;2;FALSE))))';
var f4 = '=IF(ISBLANK(O2);"";IFERROR(IF(ISNUMBER(VALUE(LEFT(O2;1)));IFERROR(VLOOKUP(CONCATENATE(MID(O2;5;2);"-";MID(O2;1;3));IMPORTRANGE("https://docs.google.com/spreadsheets/d/1ZgkOBXbnZE3ZCMVzdAjsvsBTSZgplpnkkATOHb4TeNE/edit#gid=1697538978";"BNAé!$A$2:$H$500");8;FALSE);VLOOKUP(CONCATENATE(MID(O2;5;2);"-");IMPORTRANGE("https://docs.google.com/spreadsheets/d/1ZgkOBXbnZE3ZCMVzdAjsvsBTSZgplpnkkATOHb4TeNE/edit#gid=1697538978";"BNAé!$A$2:$H$500");8;FALSE));IFERROR(VLOOKUP(CONCATENATE(MID(O2;2;3);"-";MID(O2;1;1));IMPORTRANGE("https://docs.google.com/spreadsheets/d/1ZgkOBXbnZE3ZCMVzdAjsvsBTSZgplpnkkATOHb4TeNE/edit#gid=1697538978";"ATA!$A$2:$H$500");6;FALSE);IFERROR(VLOOKUP(MID(O2;2;3);IMPORTRANGE("https://docs.google.com/spreadsheets/d/1ZgkOBXbnZE3ZCMVzdAjsvsBTSZgplpnkkATOHb4TeNE/edit#gid=1697538978";"ATA!$A$2:$H$500");6;FALSE);VLOOKUP(MID(O2;2;2);IMPORTRANGE("https://docs.google.com/spreadsheets/d/1ZgkOBXbnZE3ZCMVzdAjsvsBTSZgplpnkkATOHb4TeNE/edit#gid=1697538978";"ATA!$A$2:$H$500");6;FALSE))));"BE pas trouvé"))';
var f5 = '=IF(D2<>"";"";IFERROR(IF(INDEX(ImportedData!M:M; MATCH(E2&"-"&O2&"-"&W2&"-"&X2; ImportedData!AP:AP; 0)) = L2; ""; INDEX(ImportedData!M:M; MATCH(E2&"-"&O2&"-"&W2&"-"&X2; ImportedData!AP:AP; 0))); "Pas de NC mère"))';
var f6 = '=IF(A2="Nominal"; ""; ' + 'IF(ISBLANK(L2); ""; ' + 'IFERROR(IF(COUNTIF(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1VTyKEcgNiHfj3KUOAWqn_qIohGtWt4JOUMIarzo-Hkk/edit#gid=0"; "HT follow-up!E:E"); L2)>=1; "OUI"; "NON"))))'+ '';
var f7 = '=HYPERLINK("' + "https://docs.google.com/spreadsheets/d/1ZgkOBXbnZE3ZCMVzdAjsvsBTSZgplpnkkATOHb4TeNE/edit#gid=0" + '"; "' + "Responsable action NOM" + '")';
for (let i = 0; i < shts.length; i++){
let sh = shts[i];
let numRows = sh.getRange("A1:A" + getColumnHeight1(1,sh,ss)).getValues().length;
let numCols = sh.getLastColumn();
let rg = sh.getRange(1, 1, numRows, numCols);
let vs = rg.getValues();
for (let j = 1; j < vs.length; j++){
if(vs[j][3] == "" && vs[j][32] == "" && vs[j][34] != "DA Refused"){
let prio = vs[j][0];
let date_HT = vs[j][1];
let date_nominale = vs[j][2];
if (prio == "" && date_HT == "" && date_nominale == ""){
} else if (prio != "Nominal" && date_HT != "" && date_nominale != ""){
vs[j].splice(2, 1, "");
v1 += "- onglet " + sh.getName() + " ligne " + (j + 1) + "\n";
} else if (prio != "Nominal" && date_HT == ""){
vs[j].splice(1, 1, dt);
v1 += "- onglet " + sh.getName() + " ligne " + (j + 1) + "\n";
} else if (prio == "Nominal" && date_HT != "" && date_nominale == ""){
vs[j].splice(2, 1, dt);
v1 += "- onglet " + sh.getName() + " ligne " + (j + 1) + "\n";
}
}
}
Logger.log(vs.length + " " + vs[0].length);
sh.getRange(1, 1, vs.length, vs[0].length).setValues(vs);
if(sh.getName() != "Damien" && sh.getName() != "Christophe"){
sh.getRange("U2:U").setFormula(f4);
}
sh.getRange("A2:A" + sh.getLastRow()).setFormula(f1);
sh.getRange("F2:F" + sh.getLastRow()).setFormula(f2);
sh.getRange("P2:P" + sh.getLastRow()).setFormula(f3);
sh.getRange("AA2:AA" + sh.getLastRow()).setFormula(f5);
sh.getRange("AN2:AN" + sh.getLastRow()).setFormula(f6);
sh.getRange("U1").setFormula(f7);
}
Logger.log(v1);
}
function getColumnHeight1(col, sh, ss) {
var ss = ss || SpreadsheetApp.getActive();
var sh = sh || ss.getActiveSheet();
var col = col || sh.getActiveCell().getColumn();
var rcA = [];
if (sh.getLastRow()) { rcA = sh.getRange(1, col, sh.getLastRow(), 1).getValues().flat().reverse(); }
let s = 0;
for (let i = 0; i < rcA.length; i++) {
if (rcA[i].toString().length == 0) {
s++;
} else {
break;
}
}
return rcA.length - s;
//const h = Utilities.formatString('col: %s len: %s', col, rcA.length - s);
//Logger.log(h);
//SpreadsheetApp.getUi().showModelessDialog(HtmlService.createHtmlOutput(h).setWidth(150).setHeight(100), 'Col Length')
}