Fichier organisation

[Latelierdu7]

Bonjour à tous, j’ai commencé à faire mon fichier sheets avec des script sur chat gpt mais je tourne en rond je n’arrive pas à avancé, la structure est déjà bien commencé mais impossible de finir. j’aurais besoins d’un coup de mains svp

[reminc]

Bonjour Latelierdu7 !
C’est en effet un des grands problèmes de la conception de script par IA, si je peux vous donner quelques pistes pour améliorer votre script / votre manière de les concevoir :

• Renseignez-vous sur Google Apps Script à travers des articles de blog par exemple, avec des sujets comme : Automatisez vos Google Forms avec Google Apps Script, Tous nos articles autour de Google Apps Script
• Concevez quelques petits scripts à la main sans appuye de l’IA,
• Re-utilisez l’IA une fois les notions un peu plus claires de votre côté !

L’utilisation de l’intelligence artificielle est beaucoup plus précise et efficace lorsque nous disposons de quelques notions de base, afin de la ré-orienter vers des bonnes pistes une fois qu’elle commence à tourner en rond

PS : N’hésitez pas à utiliser ensuite d’autres modèles comme Claude ou Gemini pour la génération de vos codes.

Très belle journée à vous !

[Latelierdu7]

merci beaucoup Reminc, sur le forum il y a personne qui serait intéressé de me le faire moyennant finance ?

[Latelierdu7]

// Fichier : formulaire.gs

/**

• Ajoute les menus “ Agenda” et “ Formulaire” au chargement du classeur.
  */
  function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu(‹ 📅 Agenda ›)
  .addItem(‹ ➕ Ajouter un événement ›, ‹ openSidebar ›)
  .addToUi();
  ui.createMenu(‹ 📤 Formulaire ›)
  .addItem(‹ ✉️ Envoyer formulaire dispos ›, ‹ genererFormulaireIntelligent ›)
  .addItem(‹ 🗑️ Nettoyer événements passés ›, ‹ supprimerEvenementsPasses ›)
  .addToUi();

importerTousLesEvenementsDansStaff();
configureStaffSheet();
}

/**

• Ouvre la sidebar Agenda
  */
  function openSidebar() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const villes = […new Set(
  ss.getSheetByName(‹ Villes ›)
  .getRange(‹ A2:A › + ss.getSheetByName(‹ Villes ›).getLastRow())
  .getValues().flat().filter(String)
  )];
  const events = […new Set(
  ss.getSheetByName(‹ BaseÉvénements ›)
  .getRange(‹ D2:D › + ss.getSheetByName(‹ BaseÉvénements ›).getLastRow())
  .getValues().flat().filter(String)
  )];
  const tpl = HtmlService.createTemplateFromFile(‹ Sidebar ›);
  tpl.villesList = villes;
  tpl.eventsList = events;
  SpreadsheetApp.getUi()
  .showSidebar(tpl.evaluate().setTitle(‹ 📅 Agenda ›).setWidth(350));
  }

/**

• Gère la soumission de la sidebar et remplit BaseÉvénements, Événements et Staff
  */
  function setSidebarData(start, end, ville, evt, typeVal) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const base = ss.getSheetByName(‹ BaseÉvénements ›);
  const plan = ss.getSheetByName(‹ Événements ›);
  const staff = ss.getSheetByName(‹ Staff ›);
  if (!base || !plan || !staff) return;

ss.toast(Ajout: ${evt}, ‹ Agenda ›, 3);

// 1) Charger Staff J→O en map
const staffRows = staff.getRange(2,10,staff.getLastRow()-1,6).getValues();
const staffMap = {};
staffRows.forEach(r => {
const [evtName, city, startTime, hoursBefore, cutoffTime, nbPersons] = r;
if (evtName) staffMap[evtName] = { city, startTime, hoursBefore, cutoffTime, nbPersons };
});
const staffCfg = staffMap[evt] || {};
const cfg = loadCreneauxConfig();
const tz = Session.getScriptTimeZone();
const fmt = ‹ EEEE d MMMM ›;

// 2) BaseÉvénements : mois de start
const moisFr = [‹ janvier ›,‹ février ›,‹ mars ›,‹ avril ›,‹ mai ›,‹ juin ›,‹ juillet ›,‹ août ›,‹ septembre ›,‹ octobre ›,‹ novembre ›,‹ décembre ›];
const startDate = new Date(start);
const monthName = moisFr[startDate.getMonth()];
base.insertRows(2);
base.getRange(2,3).clearDataValidations();
base.getRange(2,1,1,8).setValues([[
‹  ›, // A
monthName, // B
typeVal, // C
evt, // D
ville, // E
‹  ›, // F
startDate, // G
new Date(end) // H
]]);
applyTypeMapping(2);

// 3) Générer créneaux selon Staff J→O
const d0 = new Date(start), d1 = new Date(end);
for (let d = new Date(d0); d <= d1; d.setDate(d.getDate()+1)) {
const dayLabel = d.toLocaleDateString(‹ fr-FR ›, { weekday: ‹ long ›, day: ‹ numeric ›, month: ‹ long › });
const isFirst = d.getTime() === d0.getTime();

// déterminer horaires
const st = staffCfg.startTime
  ? Utilities.formatDate(new Date(staffCfg.startTime), tz, 'HH:mm')
  : Utilities.formatDate(startDate, tz, 'HH:mm');
const cut = staffCfg.cutoffTime
  ? Utilities.formatDate(new Date(staffCfg.cutoffTime), tz, 'HH:mm')
  : Utilities.formatDate(
      new Date(d0).setHours(d0.getHours() - (staffCfg.hoursBefore || cfg[evt]?.hoursBefore || 0)),
      tz, 'HH:mm'
    );
const np = staffCfg.nbPersons || cfg[evt]?.personsNeeded || 1 || cfg[evt]?.personsNeeded || 1;

// a) Installation
if (isFirst) {
  plan.appendRow([
  dayLabel,          // A
  typeVal,           // B
  ville,             // C
  `Installation de ${cut} à ${st}`,  // D
  '', '', '', '', '', '', '', '',     // E-H: col5-12 blanks
  `0/${np}`,        // I: col13 status
  '',               // J: col14
  ''                // K: col15
]);
  staff.appendRow([ dayLabel, evt, '', np ]);
}
// b) Journée
if (typeVal === 'TERRASSE ÉPHÉMÈRE') {
  plan.appendRow([
  dayLabel,          // A
  typeVal,           // B
  ville,             // C
  `Journée de ${cut} à ${st}`,       // D
  '', '', '', '', '', '', '', '',     // E-H: col5-12 blanks
  '0/1',            // I: col13 status
  '',               // J: col14
  ''                // K: col15
]);
  staff.appendRow([ dayLabel, evt, '', 1 ]);
}
// c) Événement
plan.appendRow([
  dayLabel,          // A
  typeVal,           // B
  ville,             // C
  `Événement à ${st}`,              // D
  '', '', '', '', '', '', '', '',    // E-H: col5-12 blanks
  `0/${np}`,        // I: col13 status
  '',               // J: col14
  ''                // K: col15
]);
staff.appendRow([ dayLabel, evt, '', np ]);

}

// 4) Re-configurer statuts et dropdowns de dispo

updateStatusColumn();
updateAvailabilityDropdowns();
// (ne pas réappliquer updateStaffDropdowns pour éviter liste complète)
configureStaffSheet();
}

/**

• Génère et envoie le formulaire de disponibilités,
• et inscrit le numéro en colonne O de la feuille Événements.
  */
  function genererFormulaireIntelligent() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const props = PropertiesService.getScriptProperties();

// Purge des anciens déclencheurs handleFormSubmit
ScriptApp.getProjectTriggers()
.filter(t => t.getHandlerFunction() === ‹ handleFormSubmit ›)
.forEach(t => ScriptApp.deleteTrigger(t));

// Incrément du compteur
let count = parseInt(props.getProperty(‹ formCount ›) || ‹ 0 ›, 10) + 1;
props.setProperty(‹ formCount ›, String(count));

// Création du formulaire
const title = 'Dispos à remplir – Formulaire ’ + count;
const form = FormApp.create(title);
ScriptApp.newTrigger(‹ handleFormSubmit ›)
.forForm(form)
.onFormSubmit()
.create();

// Prénom et e-mail
form.addListItem()
.setTitle(‹ Quel est ton prénom ? ›)
.setChoiceValues(getStaffList());
form.addTextItem()
.setTitle(‹ Quelle est ton adresse e-mail ? ›)
.setRequired(true);

// Récupérer créneaux par date
const sh = ss.getSheetByName(‹ Événements ›);
const data = sh.getDataRange().getValues();
const today = new Date(); today.setHours(0,0,0,0);
const datesMap = new Map();
data.slice(1).forEach(r => {
const date = r[0], slot = r[3], formId = r[14];
if (!(date instanceof Date) || date < today || formId) return;
const ds = Utilities.formatDate(date, ss.getSpreadsheetTimeZone(), ‹ EEEE d MMMM yyyy ›);
if (!datesMap.has(ds)) datesMap.set(ds, );
datesMap.get(ds).push(String(slot).trim());
});
datesMap.forEach((slots, ds) => {
form.addCheckboxItem()
.setTitle(ds)
.setChoiceValues([…new Set(slots)]);
});

// Envoi du formulaire
const url = form.getPublishedUrl() + ‹ ?form= › + count;
MailApp.sendEmail(
Session.getActiveUser().getEmail(),
title,
'Merci de répondre : ’ + url
);

// Inscrire le compteur en colonne O
data.slice(1).forEach((r,i) => {
if (r[0] instanceof Date && r[0] >= today && !r[14]) {
sh.getRange(i + 2, 15).setValue(count);
}
});

supprimerEvenementsPasses();
}

/**

• Traite la soumission du formulaire
  */
  function handleFormSubmit(e) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const staff = ss.getSheetByName(‹ Staff ›);
  const resp = e.response.getItemResponses();
  let prenom = ‹  ›, email = ‹  ›;
  const selections = ;
  resp.forEach(ir => {
  const title = ir.getItem().getTitle();
  const ans = ir.getResponse();
  if (title === ‹ Quel est ton prénom ? ›) prenom = ans;
  else if (title === ‹ Quelle est ton adresse e-mail ? ›) email = ans;
  else (ans || ).forEach(slot => selections.push({ date: title, slot }));
  });
  if (!prenom || !email) return;

// Mise à jour du staff
const names = staff.getRange(‹ G2:G ›).getValues().flat().filter(Boolean);
if (!names.includes(prenom)) {
staff.appendRow([‹  ›,‹  ›,‹  ›,‹  ›,‹  ›,‹  ›,prenom,email]);
} else {
const idx = names.indexOf(prenom) + 2;
staff.getRange(idx, 8).setValue(email);
}

// Enregistrer les dispos en A/B/C
const colA = staff.getRange(‹ A2:A ›).getValues().flat();
selections.forEach(sel => {
const row = colA.findIndex(v => !v) + 2;
staff.getRange(row,1,1,3).setValues([[prenom, sel.date, sel.slot]]);
colA[row - 2] = prenom;
});

updateStatusColumn();
updateAvailabilityDropdowns();
}

/**

• Importe tous les événements uniques dans Staff
  */
  function importerTousLesEvenementsDansStaff() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const base = ss.getSheetByName(‹ BaseÉvénements ›);
  const staff = ss.getSheetByName(‹ Staff ›);
  if (!base || !staff) return;
  const events = base.getRange(‹ D2:D › + base.getLastRow()).getValues().flat().filter(String);
  const uniques = Array.from(new Set(events)).sort();
  const colJ = staff.getRange(‹ J2:J › + staff.getLastRow()).getValues().flat();
  uniques.forEach(evt => {
  if (!colJ.includes(evt)) {
  const idx = colJ.findIndex(v => !v);
  if (idx >= 0) staff.getRange(idx + 2, 10).setValue(evt);
  }
  });
  }

/**

• Configure les colonnes J→O de Staff
  */
  function configureStaffSheet() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const staff = ss.getSheetByName(‹ Staff ›);
  const base = ss.getSheetByName(‹ BaseÉvénements ›);
  if (!staff || !base) return;
  const last = staff.getLastRow(); if (last < 2) return;
  const raw = base.getRange(2, 3, base.getLastRow() - 1, 3).getValues();
  const map = raw.filter(r => r[1]).map(r => ({ event: r[1], city: r[2], type: r[0] }));

// Col J : dropdown événements
const evts = […new Set(map.map(o => o.event))].sort();
staff.getRange(2, 10, last - 1)
.setDataValidation(
SpreadsheetApp.newDataValidation()
.requireValueInList(evts, true)
.build()
);

// Col L : dropdown heures HH:mm
const rawTimes = staff.getRange(‹ E22:E47 ›).getValues().flat();
const timesList = rawTimes
.map(t => t instanceof Date
? Utilities.formatDate(t, ss.getSpreadsheetTimeZone(), ‹ HH:mm ›)
: String(t)
)
.filter(s => s && s !== ‹ 0:00 ›);
staff.getRange(2, 12, last - 1)
.setDataValidation(
SpreadsheetApp.newDataValidation()
.requireValueInList(timesList, true)
.build()
);

const cfg = loadCreneauxConfig();
const tz = ss.getSpreadsheetTimeZone();

for (let i = 2; i <= last; i++) {
const evtType = staff.getRange(i, 10).getValue();
const be = map.find(o => o.event === evtType);
if (!be) continue;

// Col K : ville
staff.getRange(i, 11).setValue(be.city || '');

// Col M : heures avant
if (!staff.getRange(i, 13).getValue()) {
  const h = (be.type === 'BUVETTE' || be.type === 'TERRASSE ÉPHÉMÈRE') ? 3
          : (be.type === 'MARCHE' ? 2 : (cfg[evtType]?.hoursBefore || 1));
  staff.getRange(i, 13).setValue(h);
}

// Col N : heure début - M
const Lval = staff.getRange(i, 12).getValue();
const Mval = Number(staff.getRange(i, 13).getValue());
if (Lval) {
  const str = Lval instanceof Date
    ? Utilities.formatDate(Lval, tz, 'HH:mm')
    : Lval;
  const [h, m] = String(str).split(':').map(Number);
  const d = new Date(0, 0, 0, h, m);
  d.setHours(d.getHours() - Mval);
  staff.getRange(i, 14).setValue(Utilities.formatDate(d, tz, 'HH:mm'));
}

// Col O : nombre de personnes
if (!staff.getRange(i, 15).getValue()) {
  const p = (be.type === 'BUVETTE' || be.type === 'TERRASSE ÉPHÉMÈRE') ? 4
          : (be.type === 'MARCHE' ? 1 : (cfg[evtType]?.personsNeeded || 1));
  staff.getRange(i, 15).setValue(p);
}

}
}

function updateStatusColumn() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const evtSh = ss.getSheetByName(‹ Événements ›);
const staff = ss.getSheetByName(‹ Staff ›);
if (!evtSh || !staff) return;

const staffData = staff.getRange(2, 10, staff.getLastRow() - 1, 6).getValues();
const neededMap = {};
staffData.forEach(r => { if (r[0]) neededMap[r[0]] = Number(r[5]) || 0; });
const cfg = loadCreneauxConfig();

const rows = evtSh.getDataRange().getValues().slice(1);
rows.forEach((r, i) => {
const slot = r[3];
const type = r[1];
let needed;
if (typeof slot === ‹ string › && slot.startsWith(‹ Installation ›)) {
if (type === ‹ BUVETTE › || type === ‹ TERRASSE ÉPHÉMÈRE ›) needed = 3;
else if (type === ‹ MARCHE ›) needed = 1;
else needed = cfg[type]?.personsNeeded || 1;
} else if (typeof slot === ‹ string › && slot.startsWith(‹ Événement ›)) {
needed = neededMap[type] || 0;
} else if (typeof slot === ‹ string › && slot.startsWith(‹ Journée ›)) {
needed = (type === ‹ TERRASSE ÉPHÉMÈRE › ? 1 : cfg[type]?.personsNeeded || 1);
} else {
needed = cfg[type]?.personsNeeded || 1;
}
const assigned = needed > 0 ? r.slice(4, 4 + needed).filter(Boolean).length : 0;
evtSh.getRange(i + 2, 13).setValue(${assigned}/${needed});
});
}

function updateAvailabilityDropdowns() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const evtSh = ss.getSheetByName(‹ Événements ›);
if (!evtSh) return;
const staffData = ss.getSheetByName(‹ Staff ›).getDataRange().getValues().slice(1);

// colonnes slots D’après E→L (8 colonnes maxi)
const startCol = 5; // colonne E
const maxCols = 8;
const data = evtSh.getDataRange().getValues().slice(1);

data.forEach((r, i) => {
const rowIndex = i + 2;
// 1) vider validations et contenus sur E→L
evtSh.getRange(rowIndex, startCol, 1, maxCols)
.clearDataValidations()
.clearContent();

// 2) ne poser dropdown que sur needed cases
const needed = parseInt(String(r[12]).split('/')[1], 10) || 0;
if (needed > 0) {
  const date = r[0];
  const slot = r[3];
  // filtrer staff ayant coché
  const avail = staffData
    .filter(s => s[1] === date && s[2] === slot)
    .map(s => s[0]);
  const rule = SpreadsheetApp.newDataValidation()
    .requireValueInList(avail, true)
    .build();
  // appliquer sur E→E+needed-1
  evtSh.getRange(rowIndex, startCol, 1, needed)
    .setDataValidation(rule);
}

});
}
/**

• Met à jour statut assigned/needed dans Événements
  */
  function updateStatusColumn() {
  // … unchanged …
  }

/**

• Met à jour dropdowns dispos dans Événements
  */
  function updateAvailabilityDropdowns() {
  // … unchanged …
  }

/**

• onEdit → recalcul de N (col 14) si L (12) ou M (13) changent,
• puis mise à jour Événements si O (15) change.
  */
  function onEdit(e) {
  const sh = e.range.getSheet();
  if (sh.getName() !== ‹ Staff ›) return;
  const col = e.range.getColumn();
  const row = e.range.getRow();
  if (row < 2) return;
  const tz = SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetTimeZone();

// recalcul heure démarrage - M
if (col === 12 || col === 13) {
const Lval = sh.getRange(row,12).getValue();
const Mval = Number(sh.getRange(row,13).getValue())||0;
const str = Lval instanceof Date ? Utilities.formatDate(Lval,tz,‹ HH:mm ›) : Lval;
const [h,m] = String(str).split(‹ : ›).map(Number);
if (!isNaN(h) && !isNaN(m)) {
const d = new Date(0,0,0,h,m);
d.setHours(d.getHours() - Mval);
sh.getRange(row,14).setValue(Utilities.formatDate(d,tz,‹ HH:mm ›));
}
}

// si nombre de personnes change → mise à jour Événements
if (col === 15) {
updateStatusColumn();
updateAvailabilityDropdowns();
}
}

/**

• Nettoyage BaseÉvénements : clear G/H si date fin passée
  */
  function cleanBaseEventsDates() {
  const sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‹ BaseÉvénements ›);
  if (!sh) return;
  const today = new Date(), last = sh.getLastRow();
  if (last < 2) return;
  sh.getRange(2,7,last-1,2).getValues().forEach((r,i) => {
  if (r[1] instanceof Date && r[1] < today) {
  sh.getRange(i+2,7,1,2).clearContent();
  }
  });
  }

/**

• Supprime événements passés de Événements
  */
  function supprimerEvenementsPasses() {
  const sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‹ Événements ›);
  if (!sh) return;
  const data = sh.getDataRange().getValues();
  const today = new Date();
  const kept = [data[0]];
  data.slice(1).forEach(r => {
  if (!(r[0] instanceof Date) || r[0] >= today) kept.push(r);
  });
  sh.clearContents();
  sh.getRange(1,1,kept.length,kept[0].length).setValues(kept);
  }

/**

• Charge configuration des créneaux
  */
  function loadCreneauxConfig() {
  const sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‹ CreneauxConfig ›);
  if (!sh) return {};
  return Object.fromEntries(
  sh.getRange(‹ A2:C › + sh.getLastRow()).getValues()
  .filter(r => r[0])
  .map(([evt,h,p]) => [evt,{hoursBefore:h,personsNeeded:p}])
  );
  }

/**

• Récupère les prénoms en G2:G de Staff
  */
  function getStaffList() {
  const sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‹ Staff ›);
  return sh ? sh.getRange(‹ G2:G ›).getValues().flat().filter(Boolean) : ;
  }