Code.gs

function doGet() {

  return HtmlService.createHtmlOutputFromFile('Index')

      .setTitle('Stijn Gabeler - Tabblad Manager Portal')

      .setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL)

      .addMetaTag('viewport', 'width=device-width, initial-scale=1');

}

function getSpreadsheet() {

  try {

    return SpreadsheetApp.getActiveSpreadsheet() || SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1DSq2ZAsSE7rjrFdgfKpEN_CAelpqceB6VwKy6efaAkw/edit#gid=0");

  } catch (e) {

    return SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1DSq2ZAsSE7rjrFdgfKpEN_CAelpqceB6VwKy6efaAkw/edit#gid=0");

  }

}

// Haalt alle werkelijke tabbladnamen op uit de Sheet

function getSheetNames() {

  var ss = getSpreadsheet();

  var sheets = ss.getSheets();

  var names = [];

  sheets.forEach(function(sheet) {

    var name = sheet.getName();

    if (name !== "KlikStatistieken") {

      names.push(name);

    }

  });

  return names;

}

// Laadt inhoud en unieke links van een specifiek tabblad op basis van de echte naam

function getSheetDataByName(sheetName) {

  var ss = getSpreadsheet();

  var sheet = ss.getSheetByName(sheetName);

  if (!sheet) return [];

  var range = sheet.getDataRange();

  var values = range.getValues();

  var formulas = range.getFormulas();

  var richTexts = range.getRichTextValues();

  var rows = [];

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

    var rowItems = [];

    for (var j = 0; j < values[i].length; j++) {

      var text = values[i][j];

      if (text === "" || text === null) continue;

      var url = "";

      try {

        var link = richTexts[i][j].getLinkUrl();

        if (link) url = link;

      } catch(err) {}

      if (!url) {

        var form = formulas[i][j];

        if (form && form.toUpperCase().indexOf("HYPERLINK") !== -1) {

          var match = form.match(/HYPERLINK\s*\(\s*"([^"]+)"/i);

          if (match && match[1]) url = match[1];

        }

      }

      if (!url) {

        url = "https://docs.google.com/spreadsheets/d/1DSq2ZAsSE7rjrFdgfKpEN_CAelpqceB6VwKy6efaAkw/edit?gid=" + sheet.getSheetId();

      }

      rowItems.push({ text: String(text), url: url });

    }

    if (rowItems.length > 0) {

      rows.push(rowItems);

    }

  }

  return rows;

}

// Registreert kliks in de Google Sheet

function registerClick(linkName, url) {

  var ss = getSpreadsheet();

  var sheet = ss.getSheetByName("KlikStatistieken");

  if (!sheet) {

    sheet = ss.insertSheet("KlikStatistieken");

    sheet.appendRow(["LinkNaam", "URL", "AantalKliks", "LaatsteKlik"]);

  }

  var data = sheet.getDataRange().getValues();

  var found = false;

  for (var i = 1; i < data.length; i++) {

    if (data[i][0] === linkName) {

      var count = data[i][2] + 1;

      sheet.getRange(i + 1, 3).setValue(count);

      sheet.getRange(i + 1, 4).setValue(new Date());

      found = true;

      break;

    }

  }

  if (!found) {

    sheet.appendRow([linkName, url, 1, new Date()]);

  }

  return "OK";

}