Backup

/************************************************************

* G20 — DAGELIJKS GELUKSBESEF

* NextLevel Coaching by Gabeler.eu

*

* VOLLEDIGE CODE.GS

*

* Deze versie is afgestemd op de huidige Index.html:

*

*   g20SaveDaily(payload)

*

* Payload:

*   UserID

*   Datum

*   G1_Geluksbesef

*   G2_Gevoelens

*   ...

*   G20_Geluksgetal

*   Inzicht

*   MooisteMoment

*   MorgenStap

*

* Spreadsheet:

*   1cl2uFtN05D6Yw78R9QtpiVMBjvXGINmMdI01A3Fhons

************************************************************/

/************************************************************

* CONFIGURATIE

************************************************************/

const SS_ID = '1cl2uFtN05D6Yw78R9QtpiVMBjvXGINmMdI01A3Fhons';

const DAILY_SHEET_NAME = 'G20_Daily';

const USERS_SHEET_NAME = 'G20_Users';

const QUESTIONS_SHEET_NAME = 'G20_Questions';

const CARDS_SHEET_NAME = 'G20_Cards';

const CODES_SHEET_NAME = 'G20_Codes';

const FAVORITES_SHEET_NAME = 'G20_Favorites';

/************************************************************

* DE 20 G-VELDEN

*

* Dit zijn EXACT de namen die de huidige Index gebruikt.

************************************************************/

const G20_KEYS_ = [

 'G1_Geluksbesef',

 'G2_Gevoelens',

 'G3_Gedroomd',

 'G4_Gemis',

 'G5_Gewenst',

 'G6_Gehoopt',

 'G7_Gedachten',

 'G8_Gedrag',

 'G9_Gezegd',

 'G10_Gedaan',

 'G11_Geschreven',

 'G12_Gelezen',

 'G13_Geleerd',

 'G14_Geweten',

 'G15_Gehoord',

 'G16_Gezien',

 'G17_Geproefd',

 'G18_Geroken',

 'G19_GedachtOver',

 'G20_Geluksgetal'

];

/************************************************************

* ALLE KOLOMMEN VAN G20_Daily

************************************************************/

function getDailyHeaders_() {

 return [

   'RecordID',

   'UserID',

   'Datum'

 ].concat(G20_KEYS_).concat([

   'Inzicht',

   'MooisteMoment',

   'MorgenStap',

   'CreatedAt',

   'UpdatedAt'

 ]);

}

/************************************************************

* WEBAPP

************************************************************/

function doGet(e) {

 return HtmlService

   .createHtmlOutputFromFile('Index')

   .setTitle(

     'G20 — Dagelijks Geluksbesef | NextLevel Coaching by Gabeler.eu'

   )

   .addMetaTag(

     'viewport',

     'width=device-width, initial-scale=1.0'

   );

}

/************************************************************

* SPREADSHEET OPENEN

************************************************************/

function getActiveSpreadsheet_() {

 try {

   var ss = SpreadsheetApp.getActiveSpreadsheet();

   if (ss) {

     return ss;

   }

 } catch (e) {

   // Webapp-context kan geen actieve spreadsheet hebben.

 }

 return SpreadsheetApp.openById(SS_ID);

}

/************************************************************

* HEADER NORMALISEREN

*

* Hiermee worden bijvoorbeeld:

*

* "G1_Geluksbesef "

* " G1_Geluksbesef"

* "G1_Geluksbesef"

*

* als dezelfde header behandeld.

*

* Ook worden onzichtbare BOM/spaties verwijderd.

************************************************************/

function normalizeHeader_(value) {

 if (value === null || value === undefined) {

   return '';

 }

 return String(value)

   .replace(/\uFEFF/g, '')

   .replace(/\u00A0/g, ' ')

   .trim();

}

/************************************************************

* HEADER-MAP MAKEN

*

* Geeft terug:

*

* {

*   G1_Geluksbesef: 4,

*   G2_Gevoelens: 5,

*   ...

* }

*

* Kolomnummers zijn 1-based voor SpreadsheetApp.

************************************************************/

function getHeaderMap_(sheet) {

 var lastColumn = sheet.getLastColumn();

 if (lastColumn < 1) {

   return {};

 }

 var headers = sheet

   .getRange(1, 1, 1, lastColumn)

   .getValues()[0];

 var map = {};

 for (var i = 0; i < headers.length; i++) {

   var header = normalizeHeader_(headers[i]);

   if (!header) {

     continue;

   }

   map[header] = i + 1;

 }

 return map;

}

/************************************************************

* G20_Daily STRUCTUUR CONTROLEREN

*

* BELANGRIJK:

* We verwijderen GEEN bestaande kolommen.

*

* We voegen ontbrekende kolommen toe.

*

* Bestaande headers met spaties worden gecorrigeerd.

************************************************************/

function ensureDailySheet_() {

 var ss = getActiveSpreadsheet_();

 var sheet = ss.getSheetByName(DAILY_SHEET_NAME);

 if (!sheet) {

   sheet = ss.insertSheet(DAILY_SHEET_NAME);

   var headers = getDailyHeaders_();

   sheet

     .getRange(1, 1, 1, headers.length)

     .setValues([headers]);

   formatDailyHeader_(sheet);

   return sheet;

 }

 /**********************************************************

  * Als het blad helemaal leeg is.

  **********************************************************/

 if (sheet.getLastColumn() === 0) {

   var emptyHeaders = getDailyHeaders_();

   sheet

     .getRange(1, 1, 1, emptyHeaders.length)

     .setValues([emptyHeaders]);

   formatDailyHeader_(sheet);

   return sheet;

 }

 /**********************************************************

  * Bestaande headers lezen.

  **********************************************************/

 var lastColumn = sheet.getLastColumn();

 var existingHeaders = sheet

   .getRange(1, 1, 1, lastColumn)

   .getValues()[0];

 /**********************************************************

  * Headers normaliseren.

  **********************************************************/

 for (var i = 0; i < existingHeaders.length; i++) {

   var normalized = normalizeHeader_(existingHeaders[i]);

   if (normalized !== existingHeaders[i]) {

     sheet

       .getRange(1, i + 1)

       .setValue(normalized);

     existingHeaders[i] = normalized;

   }

 }

 /**********************************************************

  * Ontbrekende gewenste headers toevoegen.

  **********************************************************/

 var required = getDailyHeaders_();

 var currentMap = {};

 for (var j = 0; j < existingHeaders.length; j++) {

   var h = normalizeHeader_(existingHeaders[j]);

   if (h) {

     currentMap[h] = j + 1;

   }

 }

 var missing = [];

 for (var k = 0; k < required.length; k++) {

   if (!currentMap[required[k]]) {

     missing.push(required[k]);

   }

 }

 if (missing.length > 0) {

   var startColumn = sheet.getLastColumn() + 1;

   sheet

     .getRange(

       1,

       startColumn,

       1,

       missing.length

     )

     .setValues([missing]);

 }

 formatDailyHeader_(sheet);

 return sheet;

}

/************************************************************

* HEADER OPMAAK

************************************************************/

function formatDailyHeader_(sheet) {

 try {

   var lastColumn = sheet.getLastColumn();

   if (lastColumn < 1) {

     return;

   }

   sheet

     .getRange(1, 1, 1, lastColumn)

     .setFontWeight('bold')

     .setWrap(true);

   sheet.setFrozenRows(1);

 } catch (e) {

   // Opmaak mag opslag nooit blokkeren.

 }

}

/************************************************************

* SETUP ALLE TABBLADEN

************************************************************/

function setupG20() {

 try {

   var ss = getActiveSpreadsheet_();

   ensureSheetsExist_(ss);

   return {

     success: true,

     message:

       'Alle G20 tabbladen en kolommen zijn gecontroleerd.'

   };

 } catch (e) {

   return {

     success: false,

     message:

       'Fout bij setupG20: ' + e.toString()

   };

 }

}

/************************************************************

* TABBLADEN AANMAKEN

************************************************************/

function ensureSheetsExist_(ss) {

 var required = {

   'G20_Users': [

     'UserID',

     'Naam',

     'Email',

     'PasswordHash',

     'Status',

     'CreatedAt',

     'LastLogin',

     'ProfileQuote',

     'FavoriteTheme',

     'Phone',

     'Bio'

   ],

   'G20_Daily':

     getDailyHeaders_(),

   'G20_Questions': [

     'Nummer',

     'Code',

     'Titel',

     'Emoji',

     'Vraag',

     'Reflectie'

   ],

   'G20_Cards': [

     'CardID',

     'G20',

     'Titel',

     'Vraag',

     'Reflectie',

     'Emoji',

     'Active'

   ],

   'G20_Codes': [

     'CodeID',

     'Email',

     'Code',

     'Purpose',

     'CreatedAt',

     'ExpiresAt',

     'Used'

   ],

   'G20_Favorites': [

     'FavoriteID',

     'UserID',

     'Type',

     'ReferenceID',

     'Title',

     'G20',

     'Note',

     'CreatedAt'

   ]

 };

 for (var name in required) {

   var sheet = ss.getSheetByName(name);

   if (!sheet) {

     sheet = ss.insertSheet(name);

     sheet

       .getRange(

         1,

         1,

         1,

         required[name].length

       )

       .setValues([required[name]]);

     sheet

       .getRange(

         1,

         1,

         1,

         required[name].length

       )

       .setFontWeight('bold');

     sheet.setFrozenRows(1);

     if (name === QUESTIONS_SHEET_NAME) {

       seedQuestions_(sheet);

     }

   } else {

     ensureSheetHeaders_(sheet, required[name]);

   }

 }

 /**********************************************************

  * Extra controle voor G20_Daily

  **********************************************************/

 ensureDailySheet_();

}

/************************************************************

* GENERIEKE SHEET HEADERS CONTROLEREN

************************************************************/

function ensureSheetHeaders_(sheet, requiredHeaders) {

 if (sheet.getLastColumn() === 0) {

   sheet

     .getRange(

       1,

       1,

       1,

       requiredHeaders.length

     )

     .setValues([requiredHeaders]);

   return;

 }

 var lastColumn = sheet.getLastColumn();

 var existing = sheet

   .getRange(

     1,

     1,

     1,

     lastColumn

   )

   .getValues()[0];

 for (var i = 0; i < existing.length; i++) {

   var normalized = normalizeHeader_(existing[i]);

   if (normalized !== existing[i]) {

     sheet

       .getRange(1, i + 1)

       .setValue(normalized);

     existing[i] = normalized;

   }

 }

 var existingMap = {};

 for (var j = 0; j < existing.length; j++) {

   var h = normalizeHeader_(existing[j]);

   if (h) {

     existingMap[h] = true;

   }

 }

 var missing = [];

 for (var k = 0; k < requiredHeaders.length; k++) {

   if (!existingMap[requiredHeaders[k]]) {

     missing.push(requiredHeaders[k]);

   }

 }

 if (missing.length > 0) {

   sheet

     .getRange(

       1,

       sheet.getLastColumn() + 1,

       1,

       missing.length

     )

     .setValues([missing]);

 }

}

/************************************************************

* TEST

************************************************************/

function testG20() {

 return {

   success: true,

   app:

     'G20 — Dagelijks Geluksbesef',

   coach:

     'Coach Stijn Gabeler',

   spreadsheetId:

     SS_ID,

   dailyHeaders:

     getDailyHeaders_(),

   message:

     'NextLevel Code.gs werkt correct.'

 };

}

/************************************************************

* TEST SPECIFIEK OPSLAAN

*

* Deze functie kan vanuit Apps Script worden uitgevoerd

* om te controleren of alle 20 velden daadwerkelijk

* naar de juiste kolommen worden geschreven.

************************************************************/

function testG20DailyStorage() {

 var payload = {

   UserID: 'TEST_USER',

   Datum:

     Utilities.formatDate(

       new Date(),

       Session.getScriptTimeZone(),

       'yyyy-MM-dd'

     ),

   G1_Geluksbesef: 'TEST G1',

   G2_Gevoelens: 'TEST G2',

   G3_Gedroomd: 'TEST G3',

   G4_Gemis: 'TEST G4',

   G5_Gewenst: 'TEST G5',

   G6_Gehoopt: 'TEST G6',

   G7_Gedachten: 'TEST G7',

   G8_Gedrag: 'TEST G8',

   G9_Gezegd: 'TEST G9',

   G10_Gedaan: 'TEST G10',

   G11_Geschreven: 'TEST G11',

   G12_Gelezen: 'TEST G12',

   G13_Geleerd: 'TEST G13',

   G14_Geweten: 'TEST G14',

   G15_Gehoord: 'TEST G15',

   G16_Gezien: 'TEST G16',

   G17_Geproefd: 'TEST G17',

   G18_Geroken: 'TEST G18',

   G19_GedachtOver: 'TEST G19',

   G20_Geluksgetal: '20',

   Inzicht: 'TEST INZICHT',

   MooisteMoment: 'TEST MOOISTE MOMENT',

   MorgenStap: 'TEST MORGEN STAP'

 };

 return g20SaveDaily(payload);

}

/************************************************************

* G20 DAILY OPSLAAN

*

* BELANGRIJKSTE FUNCTIE

*

* De huidige Index roept exact deze functie aan:

*

*     g20SaveDaily(payload)

*

* De opslag gebeurt KOLOM VOOR KOLOM op basis van

* de HEADERNAAM.

*

* Daardoor maakt de fysieke kolompositie niet uit.

************************************************************/

function g20SaveDaily(payload) {

 try {

   /********************************************************

    * Payload controleren

    ********************************************************/

   if (!payload || typeof payload !== 'object') {

     return {

       success: false,

       message:

         'Geen geldige G20-gegevens ontvangen.'

     };

   }

   /********************************************************

    * Spreadsheet / sheet

    ********************************************************/

   var sheet = ensureDailySheet_();

   /********************************************************

    * Headers opnieuw ophalen

    ********************************************************/

   var lastColumn = sheet.getLastColumn();

   var headers = sheet

     .getRange(

       1,

       1,

       1,

       lastColumn

     )

     .getValues()[0];

   /********************************************************

    * Headers normaliseren

    ********************************************************/

   for (var h = 0; h < headers.length; h++) {

     headers[h] =

       normalizeHeader_(headers[h]);

   }

   /********************************************************

    * Header map

    *

    * header -> kolomnummer

    ********************************************************/

   var headerMap = {};

   for (var i = 0; i < headers.length; i++) {

     if (headers[i]) {

       headerMap[headers[i]] =

         i + 1;

     }

   }

   /********************************************************

    * Controleer alle 20 G-kolommen

    ********************************************************/

   var missingG = [];

   for (var g = 0; g < G20_KEYS_.length; g++) {

     if (!headerMap[G20_KEYS_[g]]) {

       missingG.push(G20_KEYS_[g]);

     }

   }

   /********************************************************

    * Als er iets ontbreekt: toevoegen.

    ********************************************************/

   if (missingG.length > 0) {

     var startColumn =

       sheet.getLastColumn() + 1;

     sheet

       .getRange(

         1,

         startColumn,

         1,

         missingG.length

       )

       .setValues([missingG]);

     /******************************************************

      * Header map opnieuw maken.

      ******************************************************/

     lastColumn =

       sheet.getLastColumn();

     headers =

       sheet

         .getRange(

           1,

           1,

           1,

           lastColumn

         )

         .getValues()[0];

     headerMap = {};

     for (var hm = 0; hm < headers.length; hm++) {

       var normalizedHeader =

         normalizeHeader_(headers[hm]);

       if (normalizedHeader) {

         headerMap[normalizedHeader] =

           hm + 1;

       }

     }

   }

   /********************************************************

    * Datum

    ********************************************************/

   var targetDateStr = '';

   if (

     payload.Datum !== undefined &&

     payload.Datum !== null &&

     payload.Datum !== ''

   ) {

     targetDateStr =

       String(payload.Datum)

         .substring(0, 10);

   } else {

     targetDateStr =

       Utilities.formatDate(

         new Date(),

         Session.getScriptTimeZone(),

         'yyyy-MM-dd'

       );

   }

   /********************************************************

    * UserID

    ********************************************************/

   var userId =

     payload.UserID !== undefined &&

     payload.UserID !== null

       ? String(payload.UserID)

       : '';

   /********************************************************

    * Bestaand record zoeken

    *

    * Unieke combinatie:

    *

    * UserID + Datum

    ********************************************************/

   var data =

     sheet

       .getDataRange()

       .getValues();

   var targetRow = -1;

   var userColumn =

     headerMap['UserID'];

   var dateColumn =

     headerMap['Datum'];

   if (

     data.length > 1 &&

     userColumn &&

     dateColumn

   ) {

     for (

       var r = 1;

       r < data.length;

       r++

     ) {

       var rowUser =

         data[r][userColumn - 1];

       var rowDate =

         data[r][dateColumn - 1];

       var rowUserString =

         rowUser === null ||

         rowUser === undefined

           ? ''

           : String(rowUser);

       var rowDateString =

         normalizeDate_(rowDate);

       if (

         rowUserString === userId &&

         rowDateString === targetDateStr

       ) {

         targetRow =

           r + 1;

         break;

       }

     }

   }

   /********************************************************

    * RecordID

    ********************************************************/

   var recordId = '';

   var createdAt = null;

   if (targetRow > 0) {

     var existingRecordIdColumn =

       headerMap['RecordID'];

     var existingCreatedColumn =

       headerMap['CreatedAt'];

     if (existingRecordIdColumn) {

       recordId =

         sheet

           .getRange(

             targetRow,

             existingRecordIdColumn

           )

           .getValue();

     }

     if (existingCreatedColumn) {

       createdAt =

         sheet

           .getRange(

             targetRow,

             existingCreatedColumn

           )

           .getValue();

     }

   }

   if (!recordId) {

     recordId =

       'REC_' +

       new Date().getTime() +

       '_' +

       Math.floor(

         Math.random() * 10000

       );

   }

   if (!createdAt) {

     createdAt =

       new Date();

   }

   /********************************************************

    * NIEUWE / BESTAANDE RIJ

    *

    * We maken een array ter grootte van ALLE kolommen.

    ********************************************************/

   var rowData =

     new Array(headers.length);

   /********************************************************

    * Eerst bestaande waarden behouden bij update.

    ********************************************************/

   if (targetRow > 0) {

     var existingRow =

       sheet

         .getRange(

           targetRow,

           1,

           1,

           headers.length

         )

         .getValues()[0];

     for (

       var copy = 0;

       copy < existingRow.length;

       copy++

     ) {

       rowData[copy] =

         existingRow[copy];

     }

   }

   /********************************************************

    * RecordID

    ********************************************************/

   setColumnValue_(

     rowData,

     headerMap,

     'RecordID',

     recordId

   );

   /********************************************************

    * UserID

    ********************************************************/

   setColumnValue_(

     rowData,

     headerMap,

     'UserID',

     userId

   );

   /********************************************************

    * Datum

    ********************************************************/

   setColumnValue_(

     rowData,

     headerMap,

     'Datum',

     targetDateStr

   );

   /********************************************************

    * ALLE 20 G-VELDEN

    *

    * Hier gebeurt de daadwerkelijke opslag.

    ********************************************************/

   for (

     var gIndex = 0;

     gIndex < G20_KEYS_.length;

     gIndex++

   ) {

     var gKey =

       G20_KEYS_[gIndex];

     var gValue =

       getPayloadValue_(

         payload,

         gKey

       );

     setColumnValue_(

       rowData,

       headerMap,

       gKey,

       gValue

     );

   }

   /********************************************************

    * Inzicht

    ********************************************************/

   setColumnValue_(

     rowData,

     headerMap,

     'Inzicht',

     getPayloadValue_(

       payload,

       'Inzicht'

     )

   );

   /********************************************************

    * MooisteMoment

    ********************************************************/

   setColumnValue_(

     rowData,

     headerMap,

     'MooisteMoment',

     getPayloadValue_(

       payload,

       'MooisteMoment'

     )

   );

   /********************************************************

    * MorgenStap

    ********************************************************/

   setColumnValue_(

     rowData,

     headerMap,

     'MorgenStap',

     getPayloadValue_(

       payload,

       'MorgenStap'

     )

   );

   /********************************************************

    * CreatedAt

    ********************************************************/

   setColumnValue_(

     rowData,

     headerMap,

     'CreatedAt',

     createdAt

   );

   /********************************************************

    * UpdatedAt

    ********************************************************/

   setColumnValue_(

     rowData,

     headerMap,

     'UpdatedAt',

     new Date()

   );

   /********************************************************

    * Lege arraywaarden voorkomen

    ********************************************************/

   for (

     var empty = 0;

     empty < rowData.length;

     empty++

   ) {

     if (

       rowData[empty] === undefined

     ) {

       rowData[empty] = '';

     }

   }

   /********************************************************

    * OPSLAAN

    ********************************************************/

   if (targetRow > 0) {

     sheet

       .getRange(

         targetRow,

         1,

         1,

         rowData.length

       )

       .setValues([rowData]);

   } else {

     sheet

       .getRange(

         sheet.getLastRow() + 1,

         1,

         1,

         rowData.length

       )

       .setValues([rowData]);

   }

   /********************************************************

    * Controleer na schrijven

    ********************************************************/

   SpreadsheetApp.flush();

   /********************************************************

    * Geef terug welke G-velden daadwerkelijk zijn

    * ontvangen.

    *

    * Dit is ook handig voor debugging.

    ********************************************************/

   var received = {};

   for (

     var check = 0;

     check < G20_KEYS_.length;

     check++

   ) {

     var checkKey =

       G20_KEYS_[check];

     received[checkKey] =

       getPayloadValue_(

         payload,

         checkKey

       );

   }

   return {

     success: true,

     message:

       'Alle 20 G-velden en de overige dagvelden zijn opgeslagen.',

     recordId:

       recordId,

     row:

       targetRow > 0

         ? targetRow

         : sheet.getLastRow(),

     received:

       received

   };

 } catch (e) {

   return {

     success: false,

     message:

       'Fout bij opslaan G20: ' +

       e.toString()

   };

 }

}

/************************************************************

* PAYLOAD WAARDE OPHALEN

*

* Normaal:

*

* payload.G1_Geluksbesef

*

* Maar deze functie accepteert ook eenvoudige varianten.

************************************************************/

function getPayloadValue_(payload, key) {

 if (

   payload[key] !== undefined &&

   payload[key] !== null

 ) {

   return payload[key];

 }

 /**********************************************************

  * Alternatieve schrijfwijzen voor extra robuustheid.

  **********************************************************/

 var alternatives = [];

 if (

   key.indexOf('G') === 0 &&

   key.indexOf('_') !== -1

 ) {

   var numberPart =

     key.substring(

       1,

       key.indexOf('_')

     );

   alternatives.push(

     'G' + numberPart

   );

   alternatives.push(

     'g' + numberPart

   );

 }

 for (

   var i = 0;

   i < alternatives.length;

   i++

 ) {

   if (

     payload[alternatives[i]] !== undefined &&

     payload[alternatives[i]] !== null

   ) {

     return payload[alternatives[i]];

   }

 }

 return '';

}

/************************************************************

* WAARDE IN JUISTE KOLOM ZETTEN

************************************************************/

function setColumnValue_(

 rowData,

 headerMap,

 header,

 value

) {

 var column =

   headerMap[header];

 if (!column) {

   return;

 }

 rowData[column - 1] =

   value === null ||

   value === undefined

     ? ''

     : value;

}

/************************************************************

* DATUM NORMALISEREN

************************************************************/

function normalizeDate_(value) {

 if (

   value instanceof Date &&

   !isNaN(value.getTime())

 ) {

   return Utilities.formatDate(

     value,

     Session.getScriptTimeZone(),

     'yyyy-MM-dd'

   );

 }

 if (

   value === null ||

   value === undefined ||

   value === ''

 ) {

   return '';

 }

 var str =

   String(value).trim();

 if (!str) {

   return '';

 }

 /**********************************************************

  * ISO datum.

  **********************************************************/

 if (

   /^\d{4}-\d{2}-\d{2}/.test(str)

 ) {

   return str.substring(0, 10);

 }

 /**********************************************************

  * Nederlandse datum dd-mm-yyyy.

  **********************************************************/

 var dutch =

   str.match(

     /^(\d{1,2})-(\d{1,2})-(\d{4})$/

   );

 if (dutch) {

   return (

     dutch[3] +

     '-' +

     ('0' + dutch[2]).slice(-2) +

     '-' +

     ('0' + dutch[1]).slice(-2)

   );

 }

 return str.substring(0, 10);

}

/************************************************************

* G20 DAILY OPHALEN

************************************************************/

function g20GetDaily(

 userId,

 datum

) {

 try {

   var sheet =

     ensureDailySheet_();

   var data =

     sheet

       .getDataRange()

       .getValues();

   if (data.length <= 1) {

     return {

       success: false,

       data: null

     };

   }

   var headers = data[0];

   var headerMap = {};

   for (

     var h = 0;

     h < headers.length;

     h++

   ) {

     var normalized =

       normalizeHeader_(headers[h]);

     if (normalized) {

       headerMap[normalized] =

         h;

     }

   }

   var userIndex =

     headerMap['UserID'];

   var dateIndex =

     headerMap['Datum'];

   if (

     userIndex === undefined ||

     dateIndex === undefined

   ) {

     return {

       success: false,

       message:

         'UserID of Datum kolom ontbreekt.'

     };

   }

   var targetDate =

     normalizeDate_(datum);

   for (

     var i = 1;

     i < data.length;

     i++

   ) {

     var rowUser =

       data[i][userIndex];

     var rowDate =

       data[i][dateIndex];

     if (

       String(rowUser) === String(userId) &&

       normalizeDate_(rowDate) === targetDate

     ) {

       var record = {};

       for (

         var c = 0;

         c < headers.length;

         c++

       ) {

         var key =

           normalizeHeader_(headers[c]);

         if (key) {

           var value =

             data[i][c];

           /**********************************************

            * Date-objecten veilig naar de browser.

            **********************************************/

           if (

             value instanceof Date &&

             !isNaN(value.getTime())

           ) {

             record[key] =

               value.toISOString();

           } else {

             record[key] =

               value;

           }

         }

       }

       return {

         success: true,

         data:

           record

       };

     }

   }

   return {

     success: false,

     data: null

   };

 } catch (e) {

   return {

     success: false,

     message:

       'Fout bij ophalen dagboek: ' +

       e.toString()

   };

 }

}

/************************************************************

* G20 GESCHIEDENIS

************************************************************/

function g20GetUserHistory(userId) {

 try {

   var sheet =

     ensureDailySheet_();

   var data =

     sheet

       .getDataRange()

       .getValues();

   if (data.length <= 1) {

     return {

       success: true,

       history: []

     };

   }

   var headers =

     data[0];

   var userIndex =

     -1;

   for (

     var h = 0;

     h < headers.length;

     h++

   ) {

     if (

       normalizeHeader_(headers[h]) ===

       'UserID'

     ) {

       userIndex = h;

       break;

     }

   }

   if (userIndex === -1) {

     return {

       success: true,

       history: []

     };

   }

   var history = [];

   for (

     var i = 1;

     i < data.length;

     i++

   ) {

     if (

       String(data[i][userIndex]) ===

       String(userId)

     ) {

       var rowObj = {};

       for (

         var c = 0;

         c < headers.length;

         c++

       ) {

         var key =

           normalizeHeader_(headers[c]);

         if (!key) {

           continue;

         }

         var value =

           data[i][c];

         if (

           value instanceof Date &&

           !isNaN(value.getTime())

         ) {

           rowObj[key] =

             value.toISOString();

         } else {

           rowObj[key] =

             value;

         }

       }

       history.push(rowObj);

     }

   }

   return {

     success: true,

     history:

       history

   };

 } catch (e) {

   return {

     success: false,

     message:

       'Fout bij ophalen geschiedenis: ' +

       e.toString()

   };

 }

}

/************************************************************

* G20 TITELS

************************************************************/

function getGTitle(num) {

 var titles = [

   '',

   'Geluksbesef',

   'Gevoelens',

   'Gedroomd',

   'Gemis',

   'Gewenst',

   'Gehoopt',

   'Gedachten',

   'Gedrag',

   'Gezegd',

   'Gedaan',

   'Geschreven',

   'Gelezen',

   'Geleerd',

   'Geweten',

   'Gehoord',

   'Gezien',

   'Geproefd',

   'Geroken',

   'GedachtOver',

   'Geluksgetal'

 ];

 return titles[num] || '';

}

/************************************************************

* DEFAULT VRAGEN

************************************************************/

function getDefaultQuestions_() {

 return [

   {

     number: 1,

     code: 'G1_Geluksbesef',

     title: 'Geluksbesef',

     emoji: '😊',

     question:

       'Waar werd je vandaag gelukkig, blij of dankbaar van?',

     reflection:

       'Geluk wordt zichtbaar wanneer je er bewust aandacht aan geeft.'

   },

   {

     number: 2,

     code: 'G2_Gevoelens',

     title: 'Gevoelens',

     emoji: '❤️',

     question:

       'Welke gevoelens waren vandaag aanwezig?',

     reflection:

       'Gevoelens mogen eerst worden opgemerkt.'

   },

   {

     number: 3,

     code: 'G3_Gedroomd',

     title: 'Gedroomd',

     emoji: '💭',

     question:

       'Waar heb je vandaag over gedroomd of gefantaseerd?',

     reflection:

       'Een droom vertelt wat belangrijk is.'

   },

   {

     number: 4,

     code: 'G4_Gemis',

     title: 'Gemis',

     emoji: '🫶',

     question:

       'Wat of wie heb je vandaag gemist?',

     reflection:

       'Gemis maakt waardes zichtbaar.'

   },

   {

     number: 5,

     code: 'G5_Gewenst',

     title: 'Gewenst',

     emoji: '🌱',

     question:

       'Wat had je vandaag graag anders of beter gewild?',

     reflection:

       'Een wens wijst een richting aan.'

   },

   {

     number: 6,

     code: 'G6_Gehoopt',

     title: 'Gehoopt',

     emoji: '✨',

     question:

       'Waar hoopte je vandaag op?',

     reflection:

       'Hoop geeft ruimte voor de toekomst.'

   },

   {

     number: 7,

     code: 'G7_Gedachten',

     title: 'Gedachten',

     emoji: '🔍',

     question:

       'Welke gedachten beheersden vandaag je hoofd?',

     reflection:

       'Gedachten zijn feiten noch bevelen.'

   },

   {

     number: 8,

     code: 'G8_Gedrag',

     title: 'Gedrag',

     emoji: '☕',

     question:

       'Hoe handelde je vandaag; welk gedrag liet je zien?',

     reflection:

       'Je gedrag volgt je intentie.'

   },

   {

     number: 9,

     code: 'G9_Gezegd',

     title: 'Gezegd',

     emoji: '🎁',

     question:

       'Wat heb je vandaag uitgesproken of gedeeld?',

     reflection:

       'Woorden hebben impact.'

   },

   {

     number: 10,

     code: 'G10_Gedaan',

     title: 'Gedaan',

     emoji: '🤝',

     question:

       'Welke concrete daden heb je vandaag verricht?',

     reflection:

       'Daden spreken dieper dan intenties.'

   },

   {

     number: 11,

     code: 'G11_Geschreven',

     title: 'Geschreven',

     emoji: '🏆',

     question:

       'Wat heb je vandaag opgeschreven of vastgelegd?',

     reflection:

       'Schrijven structureert je binnenwereld.'

   },

   {

     number: 12,

     code: 'G12_Gelezen',

     title: 'Gelezen',

     emoji: '📚',

     question:

       'Wat heb je vandaag gelezen of tot je genomen?',

     reflection:

       'Inspiratie voedt je blik.'

   },

   {

     number: 13,

     code: 'G13_Geleerd',

     title: 'Geleerd',

     emoji: '🚀',

     question:

       'Wat heb je vandaag geleerd of ingezien?',

     reflection:

       'Groei ontstaat door reflectie.'

   },

   {

     number: 14,

     code: 'G14_Geweten',

     title: 'Geweten',

     emoji: '😄',

     question:

       'Wat zei je geweten vandaag over je keuzes?',

     reflection:

       'Je geweten is je zuivere kompas.'

   },

   {

     number: 15,

     code: 'G15_Gehoord',

     title: 'Gehoord',

     emoji: '🧘',

     question:

       'Wat heb je vandaag opgevangen of gehoord?',

     reflection:

       'Luisteren brengt verbinding.'

   },

   {

     number: 16,

     code: 'G16_Gezien',

     title: 'Gezien',

     emoji: '🎉',

     question:

       'Wat is je vandaag opgevallen of scherp gezien?',

     reflection:

       'Aandacht maakt details zichtbaar.'

   },

   {

     number: 17,

     code: 'G17_Geproefd',

     title: 'Geproefd',

     emoji: '🌧️',

     question:

       'Waar heb je van geproefd, letterlijk of figuurlijk?',

     reflection:

       'Ervaringen kleuren je dag.'

   },

   {

     number: 18,

     code: 'G18_Geroken',

     title: 'Geroken',

     emoji: '⏳',

     question:

       'Welke geuren of indrukken kwamen binnen?',

     reflection:

       'Geur brengt je bij het moment.'

   },

   {

     number: 19,

     code: 'G19_GedachtOver',

     title: 'GedachtOver',

     emoji: '⭐',

     question:

       'Waar heb je vandaag diep over nagedacht?',

     reflection:

       'Overpeinzing brengt helderheid.'

   },

   {

     number: 20,

     code: 'G20_Geluksgetal',

     title: 'Geluksgetal',

     emoji: '🌊',

     question:

       'Wat was jouw persoonlijke geluksgetal of score vandaag?',

     reflection:

       'Cijfers geven structuur aan beleving.'

   }

 ];

}

/************************************************************

* VRAGEN OPHALEN

************************************************************/

function g20GetQuestions() {

 try {

   var ss =

     getActiveSpreadsheet_();

   var sheet =

     ss.getSheetByName(

       QUESTIONS_SHEET_NAME

     );

   if (

     !sheet ||

     sheet.getLastRow() <= 1

   ) {

     return {

       success: true,

       questions:

         getDefaultQuestions_()

     };

   }

   var data =

     sheet

       .getDataRange()

       .getValues();

   var questions = [];

   for (

     var i = 1;

     i < data.length;

     i++

   ) {

     if (!data[i][0]) {

       continue;

     }

     questions.push({

       number:

         data[i][0],

       code:

         data[i][1],

       title:

         data[i][2],

       emoji:

         data[i][3],

       question:

         data[i][4],

       reflection:

         data[i][5]

     });

   }

   if (questions.length === 0) {

     questions =

       getDefaultQuestions_();

   }

   return {

     success: true,

     questions:

       questions

   };

 } catch (e) {

   return {

     success: true,

     questions:

       getDefaultQuestions_()

   };

 }

}

/************************************************************

* VRAGEN INITIALISEREN

************************************************************/

function seedQuestions_(sheet) {

 var questions =

   getDefaultQuestions_();

 for (

   var i = 0;

   i < questions.length;

   i++

 ) {

   var q =

     questions[i];

   sheet.appendRow([

     q.number,

     q.code,

     q.title,

     q.emoji,

     q.question,

     q.reflection

   ]);

 }

}

/************************************************************

* KAARTEN

************************************************************/

function g20GetCards() {

 try {

   var ss =

     getActiveSpreadsheet_();

   var sheet =

     ss.getSheetByName(

       CARDS_SHEET_NAME

     );

   if (!sheet) {

     return {

       success: true,

       cards: []

     };

   }

   var data =

     sheet

       .getDataRange()

       .getValues();

   var cards = [];

   for (

     var i = 1;

     i < data.length;

     i++

   ) {

     if (!data[i][0]) {

       continue;

     }

     cards.push({

       cardId:

         data[i][0],

       category:

         data[i][1],

       title:

         data[i][2],

       description:

         data[i][4] ||

         data[i][3]

     });