Apps Script
/**
* Automatische synchronisatie van netwerkgegevens, dashboard-updates,
* Automatische nummering van Laptops/Desktops én automatische cel-notities
* Nootites op plattegronden én switchpoorten.
*
* Werkt op basis van Tabblad IDs (GID) in plaats van tabbladnamen.
*/
// CENTRALE GID INSTELLINGEN
var DASHBOARD_GID = 920820501;
var DATA_SHEET_GID = 1036667878;
var SWITCHPOORTEN_GID = 21574053; // Het tabblad 'switchpoorten'
// Tabbladen (bijv. plattegronden/ruimtes) waarop de standaard notities gegenereerd moeten worden
var NOTE_TARGET_GIDS = [1336191404, 1866341485, 881037858];
// Helper-functie om een tabblad te zoeken op basis van GID (ID)
function getSheetById(ss, id) {
var sheets = ss.getSheets();
for (var i = 0; i < sheets.length; i++) {
if (sheets[i].getSheetId() === id) {
return sheets[i];
}
}
return null;
}
// Helper-functie om de zoekwaarde op te schoon (verwijdert álle emoji's zoals 🟠, 🟣, etc. en spaties)
function opschonenZoekwaarde(waarde) {
if (!waarde) return "";
return waarde.toString().replace(/[\u{1F300}-\u{1F9FF}]/gu, "").trim();
}
// =========================================================================
// DEEL 1 & 2: ONEDIT TRIGGER
// =========================================================================
function onEdit(e) {
var sheet = e.source.getActiveSheet();
var range = e.range;
var currentSheetId = sheet.getSheetId();
// -----------------------------------------------------------------------
// DEEL 1: GEGEVENS AANPASSEN VIA HET DASHBOARD -> DATABASE & NOTITIES
// -----------------------------------------------------------------------
if (currentSheetId === DASHBOARD_GID) {
var rangeA1 = range.getA1Notation();
// Als er een nieuw computernummer of WP-ID in B2 wordt getypt/geplakt
if (rangeA1 === "B2") {
var ruweWaarde = range.getValue();
var schoneWaarde = opschonenZoekwaarde(ruweWaarde);
// Als er een emoji of extra spatie in de cel stond, pas direct aan op het dashboard
if (ruweWaarde !== schoneWaarde) {
range.setValue(schoneWaarde);
}
haalWerkplekGegevensOp(e.source);
return;
}
// Cellen op het dashboard die gekoppeld zijn aan de database (B4 t/m B14)
var luisterCellen = ["B4", "B5", "B6", "B7", "B8", "B9", "B10", "B11", "B12", "B13", "B14"];
if (luisterCellen.indexOf(rangeA1) === -1) return;
var dashSheet = sheet;
var dataSheet = getSheetById(e.source, DATA_SHEET_GID);
if (!dataSheet) return;
var gezochteWerkplek = opschonenZoekwaarde(dashSheet.getRange("B2").getValue());
if (!gezochteWerkplek) {
SpreadsheetApp.getUi().alert("Let op: Er is geen zoekwaarde ingevuld in B2!");
return;
}
// Zoek de juiste rij op in de database (match op Werkplek-ID of Computernummer)
var data = dataSheet.getRange("A1:B" + dataSheet.getLastRow()).getValues();
var gevondenRij = -1;
for (var i = 0; i < data.length; i++) {
var pcInDb = opschonenZoekwaarde(data[i][1]);
var wpInDb = opschonenZoekwaarde(data[i][0]);
if (pcInDb == gezochteWerkplek || wpInDb == gezochteWerkplek) {
gevondenRij = i + 1;
break;
}
}
// Als de rij is gevonden, schrijf de nieuwe waarde naar de juiste databasekolom
if (gevondenRij !== -1) {
var nieuweWaarde = (e.value !== undefined) ? e.value : range.getValue();
var doelKolom = -1;
switch (rangeA1) {
case "B4": dataSheet.getRange(gevondenRij, 2).setValue(nieuweWaarde); break; // Kolom B: Computernummer
case "B5": dataSheet.getRange(gevondenRij, 1).setValue(nieuweWaarde); break; // Kolom A: WP-ID
case "B6": dataSheet.getRange(gevondenRij, 3).setValue(nieuweWaarde); break; // Kolom C: Patchpoort
case "B7": dataSheet.getRange(gevondenRij, 4).setValue(nieuweWaarde); break; // Kolom D: Switchpoort (AU)
case "B8": dataSheet.getRange(gevondenRij, 5).setValue(nieuweWaarde); doelKolom = 5; break; // Kolom E: IP / Subnet
case "B9": dataSheet.getRange(gevondenRij, 6).setValue(nieuweWaarde); doelKolom = 6; break; // Kolom F: VLAN Naam
case "B10": dataSheet.getRange(gevondenRij, 7).setValue(nieuweWaarde); doelKolom = 7; break; // Kolom G: VLAN ID
case "B11": dataSheet.getRange(gevondenRij, 8).setValue(nieuweWaarde); break; // Kolom H: Huidige Status
case "B12": dataSheet.getRange(gevondenRij, 9).setValue(nieuweWaarde.toString()); break; // Kolom I: Categorie
case "B13": dataSheet.getRange(gevondenRij, 10).setValue(nieuweWaarde); break; // Kolom J: Ticketnummer
case "B14": dataSheet.getRange(gevondenRij, 11).setValue(nieuweWaarde); break; // Kolom K: Specifieke Actie
}
// Als IP, VLAN Naam of VLAN ID verandert, trigger direct de automatische cross-sync
if (doelKolom === 5 || doelKolom === 6 || doelKolom === 7) {
vulNetwerkGegevensAan(e.source, dataSheet, gevondenRij, doelKolom, nieuweWaarde);
}
// Update eveneens de cel-notities op de gekoppelde ruimtes én switchpoorten
updateWerkplekNotities(e.source);
e.source.toast("Wijziging opgeslagen & notities bijgewerkt!", "Database Update", 1.5);
// Ververs het dashboard direct zodat de gesyncte netwerkdata in beeld springt
haalWerkplekGegevensOp(e.source);
} else {
SpreadsheetApp.getUi().alert("Werkplek/Computer '" + gezochteWerkplek + "' niet gevonden.");
}
return;
}
// -----------------------------------------------------------------------
// DEEL 2: RECHTSTREEKSE WIJZIGING IN DATABASE -> LIVE UPDATE DASHBOARD & NOTITIES
// -----------------------------------------------------------------------
if (currentSheetId === DATA_SHEET_GID) {
var row = range.getRow();
var col = range.getColumn();
if (row < 2) return; // Sla de koptekst over
var editedValue = e.value;
// AUTOMATISCHE NUMMERING BIJ INVOER 'LAPTOP' OF 'DESKTOP' IN KOLOM B
if (col === 2 && editedValue) {
var ingevoerdeTekst = editedValue.toString().trim().toLowerCase();
if (ingevoerdeTekst === "laptop" || ingevoerdeTekst === "desktop") {
var bValues = sheet.getRange("B2:B" + sheet.getLastRow()).getValues();
var maxNummer = 0;
for (var i = 0; i < bValues.length; i++) {
var celTekst = String(bValues[i][0]).trim();
var match = celTekst.match(new RegExp("^" + ingevoerdeTekst + "\\s*(\\d+)", "i"));
if (match) {
var nummer = parseInt(match[1], 10);
if (nummer > maxNummer) {
maxNummer = nummer;
}
}
}
var nieuwNummer = maxNummer + 1;
var nettype = ingevoerdeTekst.charAt(0).toUpperCase() + ingevoerdeTekst.slice(1);
editedValue = nettype + " " + nieuwNummer;
range.setValue(editedValue);
}
}
// 1. Voer de VLAN Netwerk-Sync uit als er in kolom E, F of G wordt getypt
if (editedValue && (col === 5 || col === 6 || col === 7)) {
vulNetwerkGegevensAan(e.source, sheet, row, col, editedValue);
}
// 2. Update altijd de notities op de target sheets (plattegronden + switchpoorten) bij database edit
updateWerkplekNotities(e.source);
// 3. Controleer of deze rij momenteel openstaat op het Dashboard
var dashSheet = getSheetById(e.source, DASHBOARD_GID);
if (!dashSheet) return;
var gezochteWerkplek = opschonenZoekwaarde(dashSheet.getRange("B2").getValue());
if (!gezochteWerkplek) return;
var huidigeWpId = opschonenZoekwaarde(sheet.getRange(row, 1).getValue());
var huidigePcNummer = opschonenZoekwaarde(sheet.getRange(row, 2).getValue());
if (huidigeWpId == gezochteWerkplek || huidigePcNummer == gezochteWerkplek) {
haalWerkplekGegevensOp(e.source);
e.source.toast("Dashboard & Notities live bijgewerkt!", "Live Sync", 1.5);
}
}
}
// =========================================================================
// DEEL 3: DE VLAN EN NETWERK-SYNC ENGINE
// =========================================================================
function vulNetwerkGegevensAan(ss, sheet, row, col, editedValue) {
var refSheet = ss.getSheetByName("Netwerk Info");
if (!refSheet) return;
var refData = refSheet.getRange("A2:C" + refSheet.getLastRow()).getValues();
// Kolom 5 = IP / Subnet (Kolom A in Netwerk Info)
if (col === 5) {
for (var i = 0; i < refData.length; i++) {
if (refData[i][0] == editedValue) {
sheet.getRange(row, 6).setValue(refData[i][1]); // VLAN Naam -> Kolom F (6)
sheet.getRange(row, 7).setValue(refData[i][2]); // VLAN ID -> Kolom G (7)
break;
}
}
}
// Kolom 6 = VLAN Naam (Kolom B in Netwerk Info)
if (col === 6) {
for (var i = 0; i < refData.length; i++) {
if (refData[i][1] == editedValue) {
sheet.getRange(row, 5).setValue(refData[i][0]); // IP / Subnet -> Kolom E (5)
sheet.getRange(row, 7).setValue(refData[i][2]); // VLAN ID -> Kolom G (7)
break;
}
}
}
// Kolom 7 = VLAN ID (Kolom C in Netwerk Info)
if (col === 7) {
for (var i = 0; i < refData.length; i++) {
if (refData[i][2] == editedValue) {
sheet.getRange(row, 5).setValue(refData[i][0]); // IP / Subnet -> Kolom E (5)
sheet.getRange(row, 6).setValue(refData[i][1]); // VLAN Naam -> Kolom F (6)
break;
}
}
}
}
// =========================================================================
// DEEL 4: AUTOMATISCHE NOTITIE SYNCHRONISATIE ENGINE (INCL. SWITCHPOORTEN)
// =========================================================================
function updateWerkplekNotities(ss) {
var ssObj = ss || SpreadsheetApp.getActiveSpreadsheet();
var dataSheet = getSheetById(ssObj, DATA_SHEET_GID);
if (!dataSheet) return;
var lastRow = dataSheet.getLastRow();
if (lastRow < 2) return;
// Haal alle data op uit Werkplekken Data (Kolom A t/m K = 11 kolommen)
var dbValues = dataSheet.getRange(2, 1, lastRow - 1, 11).getDisplayValues();
// Maak twee lookup-kaarten:
// 1. dbMapMap: Voor de plattegronden (zonder verplichte WP-ID bovenaan, want WP-ID is vaak de cel-sleutel)
// 2. dbMapSwitch: Voor het 'switchpoorten' tabblad (met extra Werkplek ID bovenaan)
var dbMapMap = {};
var dbMapSwitch = {};
for (var i = 0; i < dbValues.length; i++) {
var wpId = opschonenZoekwaarde(dbValues[i][0]);
var pcNummer = opschonenZoekwaarde(dbValues[i][1]);
var patchPoort = opschonenZoekwaarde(dbValues[i][2]);
var switchPoort = opschonenZoekwaarde(dbValues[i][3]);
var vlan = dbValues[i][5] ? dbValues[i][5] + " (ID: " + dbValues[i][6] + ")" : dbValues[i][6];
// Standaard notitieblok (zoals op de kaarten/plattegronden)
var basisNotitie =
"💻 PC: " + (dbValues[i][1] || "-") + "\n" +
"🔌 Patchpoort: " + (dbValues[i][2] || "-") + "\n" +
"🔀 Switchpoort: " + (dbValues[i][3] || "-") + "\n" +
"🌐 IP: " + (dbValues[i][4] || "-") + "\n" +
"🏷️ VLAN: " + (vlan || "-") + "\n" +
"-------------------------------\n" +
"📌 Status: " + (dbValues[i][7] || "-") + "\n" +
"📁 Categorie: " + (dbValues[i][8] || "-") + "\n" +
"🎫 Ticket: " + (dbValues[i][9] || "-") + "\n" +
"⚡ Actie: " + (dbValues[i][10] || "-");
// Specifieke notitie voor switchpoorten (inclusief Werkplek ID)
var switchNotitie =
"🏢 Werkplek ID: " + (dbValues[i][0] || "-") + "\n" + basisNotitie;
// Vul Plattegrond-map
if (wpId) dbMapMap[wpId] = basisNotitie;
if (pcNummer) dbMapMap[pcNummer] = basisNotitie;
// Vul Switchpoort-map (gekoppeld op Patchpoort, Switchpoort, WP-ID en PC-nummer)
if (wpId) dbMapSwitch[wpId] = switchNotitie;
if (pcNummer) dbMapSwitch[pcNummer] = switchNotitie;
if (patchPoort) dbMapSwitch[patchPoort] = switchNotitie;
if (switchPoort) dbMapSwitch[switchPoort] = switchNotitie;
}
// A. UPDATE PLATTEGRONDEN / RUIMTES
for (var g = 0; g < NOTE_TARGET_GIDS.length; g++) {
var targetSheet = getSheetById(ssObj, NOTE_TARGET_GIDS[g]);
if (!targetSheet) continue;
verwerkNotitiesVoorSheet(targetSheet, dbMapMap, false);
}
// B. UPDATE HET TABBLAD 'SWITCHPOORTEN' (GID 21574053)
var switchSheet = getSheetById(ssObj, SWITCHPOORTEN_GID);
if (switchSheet) {
verwerkNotitiesVoorSheet(switchSheet, dbMapSwitch, true);
}
}
/**
* Helper-functie voor het verwerken en wegschrijven van notities naar een specifiek tabblad.
* @param {Sheet} sheet - Het doel tabblad
* @param {Object} dbMap - De notitie-opzoekkaart
* @param {Boolean} isSwitchSheet - True als het filter op G0, G1 of G2 toegepast moet worden
*/
function verwerkNotitiesVoorSheet(sheet, dbMap, isSwitchSheet) {
var numRows = sheet.getLastRow();
var numCols = sheet.getLastColumn();
if (numRows < 1 || numCols < 1) return;
var range = sheet.getRange(1, 1, numRows, numCols);
var cellValues = range.getDisplayValues();
var cellFormulas = range.getFormulas();
var currentNotes = range.getNotes();
var hasChanges = false;
for (var r = 0; r < numRows; r++) {
for (var c = 0; c < numCols; c++) {
var celTekst = cellValues[r][c] ? cellValues[r][c].toString().trim() : "";
var formule = cellFormulas[r][c];
// Op de switchpoorten-pagina alleen verwerken als de tekst of formule begint met G0, G1 of G2
if (isSwitchSheet) {
var matchG = /^G[0-2]/i.test(celTekst) || (formule && /^=.*G[0-2]/i.test(formule));
if (!matchG) continue;
}
var gewensteNotitie = null;
// 1. Probeer sleutel uit de LET / XLOOKUP formule te achterhalen
if (formule && formule.indexOf("XLOOKUP") !== -1) {
for (var key in dbMap) {
if (formule.indexOf(key) !== -1) {
gewensteNotitie = dbMap[key];
break;
}
}
}
// 2. Fallback: match op de getoonde celwaarde
if (!gewensteNotitie) {
var schoneCelWaarde = opschonenZoekwaarde(celTekst);
if (schoneCelWaarde && dbMap[schoneCelWaarde]) {
gewensteNotitie = dbMap[schoneCelWaarde];
}
}
// 3. Pas notitie toe als er een match is en de notitie gewijzigd is
if (gewensteNotitie && currentNotes[r][c] !== gewensteNotitie) {
currentNotes[r][c] = gewensteNotitie;
hasChanges = true;
}
}
}
if (hasChanges) {
range.setNotes(currentNotes);
}
}
// =========================================================================
// DEEL 5: HET LIVE OPHALEN / LEEGMAKEN VAN DE GEGEVENS
// =========================================================================
function haalWerkplekGegevensOp(ss) {
var dashSheet = getSheetById(ss, DASHBOARD_GID);
var dataSheet = getSheetById(ss, DATA_SHEET_GID);
if (!dashSheet || !dataSheet) return;
var ruweWaarde = dashSheet.getRange("B2").getDisplayValue();
var geselecteerdeWaarde = opschonenZoekwaarde(ruweWaarde);
if (ruweWaarde !== geselecteerdeWaarde) {
dashSheet.getRange("B2").setValue(geselecteerdeWaarde);
}
if (!geselecteerdeWaarde) {
dashSheet.getRange("B4:B14").clearContent();
return;
}
var lastRow = dataSheet.getLastRow();
if (lastRow < 2) return;
// Haal 11 kolommen aan data op (Kolom A t/m K)
var data = dataSheet.getRange(1, 1, lastRow, 11).getDisplayValues();
var gevonden = false;
for (var i = 0; i < data.length; i++) {
var wpId = opschonenZoekwaarde(data[i][0]); // Kolom A
var pcNummer = opschonenZoekwaarde(data[i][1]); // Kolom B
if (wpId === geselecteerdeWaarde || pcNummer === geselecteerdeWaarde) {
dashSheet.getRange("B4").setValue(data[i][1]); // Computernummer (Kolom B)
dashSheet.getRange("B5").setValue(data[i][0]); // Werkplek ID (Kolom A)
dashSheet.getRange("B6").setValue(data[i][2]); // Patchpoort (Kolom C)
dashSheet.getRange("B7").setValue(data[i][3]); // Switchpoort (AU) (Kolom D)
dashSheet.getRange("B8").setValue(data[i][4]); // IP / Subnet (Kolom E)
dashSheet.getRange("B9").setValue(data[i][5]); // VLAN Naam (Kolom F)
dashSheet.getRange("B10").setValue(data[i][6]); // VLAN ID (Kolom G)
dashSheet.getRange("B11").setValue(data[i][7]); // Huidige Status (Kolom H)
dashSheet.getRange("B12").setValue(data[i][8]); // Categorie (Kolom I)
dashSheet.getRange("B13").setValue(data[i][9]); // Ticketnummer (Kolom J)
dashSheet.getRange("B14").setValue(data[i][10]); // Specifieke Actie (Kolom K)
gevonden = true;
break;
}
}
if (!gevonden) {
dashSheet.getRange("B4").setValue("Niet gevonden");
dashSheet.getRange("B5:B14").clearContent();
}
}