In my project I have a sheet that contains a list of names.
From a script, I copy a model sheet which I rename according to the names present in the list of names: no problem for that, including the copying of protections thanks in particular to the transferProtections function.
Secondly, I create a file per tab (with the same name): the copy goes without problem but the data protection is not copied.
Where is the problem ?
THANKS
<code>const wbk = SpreadsheetApp.getActiveSpreadsheet();
const shModeleClub = wbk.getSheetByName('ModeleClub');
const shListeClub = wbk.getSheetByName('ListeClub');
const FOLDER_ID = ""; //noter ici l'ID du dossier qui va contenir les licences par club (apparait après /d/ ds l'URL)
function transfererProtections(sh1,sh2){
// cas d'une protection de plages
sh1.getProtections(SpreadsheetApp.ProtectionType.RANGE).forEach(p1 => {
var p2 = sh2.getRange(p1.getRange().getA1Notation()).protect();
p2.setDescription(p1.getDescription());
p2.setWarningOnly(p1.isWarningOnly());
if (!p1.isWarningOnly()) {
p2.removeEditors(p2.getEditors());
p2.addEditors(p1.getEditors());
}
})
}
//---FONCTION POUR CREER UN FICHIER CLUB INDEPENDANT AU LIEU DES ONGLETS -
function creationClub1(){
let shClubLastRow = shListeClub.getLastRow();
let liste_club = shListeClub.getRange(3, 1, shClubLastRow -1, 1).getValues(); //la liste des clubs débute en A3
//console.log(liste_club);
let valeurClub = liste_club.filter(String).length+1;
//console.log(valeurClub);
if (valeurClub%2 ==0){
valeurClub=valeurClub/2;
} else {
valeurClub=(valeurClub+1)/2;
}
//console.log(valeurClub);
let sheets = wbk.getSheets();
let folder = DriveApp.getFolderById(FOLDER_ID);
for (var i = 2; i<valeurClub ; i++ ) {
let sheet = sheets[i];
let name = sheet.getName()
fichierInitial = wbk.getSheetByName(name)
if (!folder.getFilesByName(name).hasNext()) {
let newSpreadSheet = SpreadsheetApp.create(name);
sheet.copyTo(newSpreadSheet).setName(name);
sheet.copyTo(newSpreadSheet).setName("Feuille 2");
defaultSheet = newSpreadSheet.getSheetByName("Feuille 1");
transfererProtections(fichierInitial,defaultSheet)
newSpreadSheet.deleteSheet(defaultSheet);
defaultSheet = newSpreadSheet.getSheetByName(name);
newSpreadSheet.deleteSheet(defaultSheet);
let newFile = DriveApp.getFileById(newSpreadSheet.getId());
newFile.moveTo(DriveApp.getFolderById(folder.getId()))
}
}
}
</code>
<code>const wbk = SpreadsheetApp.getActiveSpreadsheet();
const shModeleClub = wbk.getSheetByName('ModeleClub');
const shListeClub = wbk.getSheetByName('ListeClub');
const FOLDER_ID = ""; //noter ici l'ID du dossier qui va contenir les licences par club (apparait après /d/ ds l'URL)
function transfererProtections(sh1,sh2){
// cas d'une protection de plages
sh1.getProtections(SpreadsheetApp.ProtectionType.RANGE).forEach(p1 => {
var p2 = sh2.getRange(p1.getRange().getA1Notation()).protect();
p2.setDescription(p1.getDescription());
p2.setWarningOnly(p1.isWarningOnly());
if (!p1.isWarningOnly()) {
p2.removeEditors(p2.getEditors());
p2.addEditors(p1.getEditors());
}
})
}
//---FONCTION POUR CREER UN FICHIER CLUB INDEPENDANT AU LIEU DES ONGLETS -
function creationClub1(){
let shClubLastRow = shListeClub.getLastRow();
let liste_club = shListeClub.getRange(3, 1, shClubLastRow -1, 1).getValues(); //la liste des clubs débute en A3
//console.log(liste_club);
let valeurClub = liste_club.filter(String).length+1;
//console.log(valeurClub);
if (valeurClub%2 ==0){
valeurClub=valeurClub/2;
} else {
valeurClub=(valeurClub+1)/2;
}
//console.log(valeurClub);
let sheets = wbk.getSheets();
let folder = DriveApp.getFolderById(FOLDER_ID);
for (var i = 2; i<valeurClub ; i++ ) {
let sheet = sheets[i];
let name = sheet.getName()
fichierInitial = wbk.getSheetByName(name)
if (!folder.getFilesByName(name).hasNext()) {
let newSpreadSheet = SpreadsheetApp.create(name);
sheet.copyTo(newSpreadSheet).setName(name);
sheet.copyTo(newSpreadSheet).setName("Feuille 2");
defaultSheet = newSpreadSheet.getSheetByName("Feuille 1");
transfererProtections(fichierInitial,defaultSheet)
newSpreadSheet.deleteSheet(defaultSheet);
defaultSheet = newSpreadSheet.getSheetByName(name);
newSpreadSheet.deleteSheet(defaultSheet);
let newFile = DriveApp.getFileById(newSpreadSheet.getId());
newFile.moveTo(DriveApp.getFolderById(folder.getId()))
}
}
}
</code>
const wbk = SpreadsheetApp.getActiveSpreadsheet();
const shModeleClub = wbk.getSheetByName('ModeleClub');
const shListeClub = wbk.getSheetByName('ListeClub');
const FOLDER_ID = ""; //noter ici l'ID du dossier qui va contenir les licences par club (apparait après /d/ ds l'URL)
function transfererProtections(sh1,sh2){
// cas d'une protection de plages
sh1.getProtections(SpreadsheetApp.ProtectionType.RANGE).forEach(p1 => {
var p2 = sh2.getRange(p1.getRange().getA1Notation()).protect();
p2.setDescription(p1.getDescription());
p2.setWarningOnly(p1.isWarningOnly());
if (!p1.isWarningOnly()) {
p2.removeEditors(p2.getEditors());
p2.addEditors(p1.getEditors());
}
})
}
//---FONCTION POUR CREER UN FICHIER CLUB INDEPENDANT AU LIEU DES ONGLETS -
function creationClub1(){
let shClubLastRow = shListeClub.getLastRow();
let liste_club = shListeClub.getRange(3, 1, shClubLastRow -1, 1).getValues(); //la liste des clubs débute en A3
//console.log(liste_club);
let valeurClub = liste_club.filter(String).length+1;
//console.log(valeurClub);
if (valeurClub%2 ==0){
valeurClub=valeurClub/2;
} else {
valeurClub=(valeurClub+1)/2;
}
//console.log(valeurClub);
let sheets = wbk.getSheets();
let folder = DriveApp.getFolderById(FOLDER_ID);
for (var i = 2; i<valeurClub ; i++ ) {
let sheet = sheets[i];
let name = sheet.getName()
fichierInitial = wbk.getSheetByName(name)
if (!folder.getFilesByName(name).hasNext()) {
let newSpreadSheet = SpreadsheetApp.create(name);
sheet.copyTo(newSpreadSheet).setName(name);
sheet.copyTo(newSpreadSheet).setName("Feuille 2");
defaultSheet = newSpreadSheet.getSheetByName("Feuille 1");
transfererProtections(fichierInitial,defaultSheet)
newSpreadSheet.deleteSheet(defaultSheet);
defaultSheet = newSpreadSheet.getSheetByName(name);
newSpreadSheet.deleteSheet(defaultSheet);
let newFile = DriveApp.getFileById(newSpreadSheet.getId());
newFile.moveTo(DriveApp.getFolderById(folder.getId()))
}
}
}