mwitnessing/_doc/GoogleFormsScript.gs

function main() {
  var form = FormApp.openById("11VuGKjoywNA0ajrfeRVwdNYDXSM0ge2qAn6bhiXOZj0"); // Replace with your Form's ID
  var sheet = SpreadsheetApp.openById("17CFpEDZ2Bn5GJSfMUhliwBBdbCaghATxZ5lYwRSbGsY").getSheetByName("Sheet1");
  var sheetResults = SpreadsheetApp.openById("1jIW27zh-502WIBpFVWGuNSZp7vBuB-SW6L_NPKgf4Ys").getSheetByName("Form Responses 1");

  setNamesDropdownOptions(form, sheet);
  //updateWeeklyQuestions(form, new Date(2023,11 - 1,1));
  //mergeColumnsWithSameNames(sheetResults);
}

function setNamesDropdownOptions(form, sheet) {
  var names = sheet.getRange("A4:A").getValues(); // Replace 'Sheet1' and 'A:A' with your sheet name and range respectively
  var colAFontWeights = sheet.getRange("A4:A").getFontWeights();

  var flatList = names
    .flat()
    .map(function (name) {
      return normalizeName(name[0]);
    })
    .filter(String); // Flatten the array, normalize names, and remove any empty strings

  var title = "Име, Фамилия";
  var item = form.getItems(FormApp.ItemType.LIST); // or FormApp.ItemType.LIST or MULTIPLE_CHOICE
  var multipleChoiceItem = item[0].asListItem(); // or .asMultipleChoiceItem()

  var boldValues = [];
  for (var i = 0; i < colAFontWeights.length; i++) {
    if (colAFontWeights[i][0] === "bold") {
      boldValues.push(names[i][0]);
    }
  }
  var bulgarianOrder = "АБВГДЕЖЗИЙКЛМНОПРСТУФХЦЧШЩЪЬЮЯ";

  function customBulgarianSort(a, b) {
    for (var i = 0; i < Math.min(a.length, b.length); i++) {
      var indexA = bulgarianOrder.indexOf(a[i]);
      var indexB = bulgarianOrder.indexOf(b[i]);

      // Default behavior for characters not in the bulgarianOrder string
      if (indexA === -1 && indexB === -1) {
        if (a[i] < b[i]) return -1;
        if (a[i] > b[i]) return 1;
      }

      if (indexA === -1) return 1;
      if (indexB === -1) return -1;
      if (indexA < indexB) return -1;
      if (indexA > indexB) return 1;
    }

    if (a.length < b.length) return -1;
    if (a.length > b.length) return 1;
    return 0;
  }

  boldValues.sort(customBulgarianSort);

  multipleChoiceItem.setChoiceValues(boldValues);
}

// Helper function to remove leading, trailing, and consecutive spaces
function normalizeName(name) {
  if (typeof name !== "string") {
    return ""; // Return an empty string if the name is not a valid string
  }
  return name.split(" ").filter(Boolean).join(" ");
}

function ReverseNames(names, destination = "D") {
  // Reverse the name order for each name
  var reversedNames = names.map(function (name) {
    var splitName = name[0].trim().split(" ");
    if (splitName.length === 2) {
      // Ensure there's a Lastname and Firstname to swap
      return [splitName[1] + " " + splitName[0]];
    } else {
      return [name[0]]; // Return the original name if it doesn't fit the "Lastname Firstname" format
    }
  });

  // Write reversed names to column D
  sheet.getRange(destination + "4:" + destination + "" + (3 + reversedNames.length)).setValues(reversedNames);
}

function updateWeeklyQuestions(form, forDate) {
  var items = form.getItems(FormApp.ItemType.CHECKBOX_GRID);

  var today = forDate;
  var firstDayOfMonth = new Date(today.getFullYear(), today.getMonth(), 1);
  var weeks = getWeeksInMonth(today.getMonth(), today.getFullYear());

  var columns = [];
  columns.push("Не мога");
  var weekDays = ["пон", "вт", "ср", "четв", "пет", "съб"];

  //Summer
  //for (var i = 8; i <= 18; i += 2) {
  //    columns.push("(" + i + "ч-" + (i + 2) + "ч)");
  //Winter
  for (var i = 9; i <= 18; i += 1.5) {
    columns.push("" + formatTime(i) + "-" + formatTime(i + 1.5) + "");
  }

  for (var i = 0; i < weeks.length; i++) {
    var week = weeks[i];
    // Skip weeks that started in the previous month
    if (week[0].getMonth() !== firstDayOfMonth.getMonth()) {
      continue;
    }
    console.log("Week from " + week[0].getDate());

    var title = "Седмица " + (i + 1) + " (" + week[0].getDate() + "-" + week[5].getDate() + " " + getMonthName(week[0].getMonth()) + ")"; // Always represent Monday to Saturday
    if (week[5].getMonth() !== week[0].getMonth()) {
      title = "Седмица " + (i + 1) + " (" + week[0].getDate() + " " + getMonthName(week[0].getMonth()) + " - " + week[5].getDate() + " " + getMonthName(week[5].getMonth()) + ")"; // Always represent Monday to Saturday
    }
    if (i < items.length) {
      // Modify existing checkbox grid
      var checkboxGrid = items[i].asCheckboxGridItem();
      checkboxGrid.setTitle(title);
      checkboxGrid.setColumns(columns);
      checkboxGrid.setRows(weekDays);
    } else {
      // Create a new checkbox grid
      var checkboxGrid = form.addCheckboxGridItem();
      checkboxGrid.setTitle(title);
      checkboxGrid.setColumns(columns);
      checkboxGrid.setRows(weekDays);
      checkboxGrid.setRequired(true);
    }
  }

  // Delete extra checkbox grids
  for (var j = weeks.length; j < items.length; j++) {
    form.deleteItem(items[j]);
  }
}

function getWeeksInMonth(month, year) {
  var weeks = [],
    firstDate = new Date(year, month, 1),
    lastDate = new Date(year, month + 1, 0);

  // Find the first Monday of the month
  while (firstDate.getDay() !== 1) {
    firstDate.setDate(firstDate.getDate() + 1);
  }

  var currentDate = new Date(firstDate);

  while (currentDate <= lastDate) {
    var week = [];

    // For 6 days (Monday to Saturday)
    for (var i = 0; i < 6; i++) {
      week.push(new Date(currentDate));
      currentDate.setDate(currentDate.getDate() + 1);
    }

    currentDate.setDate(currentDate.getDate() + 1); //Sunday
    weeks.push(week);

    // If we are already in the next month, break out of the loop
    if (currentDate.getMonth() !== month) {
      break;
    }
  }

  return weeks;
}

function getMonthName(monthIndex) {
  var months = ["януари", "февруари", "март", "април", "май", "юни", "юли", "август", "септември", "октомври", "ноември", "декември"];
  return months[monthIndex];
}

function formatTime(hourDecimal) {
  var hours = Math.floor(hourDecimal);
  var minutes = (hourDecimal - hours) * 60;
  if (minutes === 0) {
    return hours.toString().padStart(2, "0");
  }
  return hours.toString().padStart(2, "0") + ":" + minutes.toString().padStart(2, "0");
}

function mergeColumnsWithSameNames(sheet) {
  var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
  var uniqueHeaders = [...new Set(headers)]; // Get unique headers

  for (var u = 0; u < uniqueHeaders.length; u++) {
    var currentHeader = uniqueHeaders[u];
    var columnsToMerge = [];

    // Identify columns with the same name
    for (var i = 0; i < headers.length; i++) {
      if (headers[i] === currentHeader) {
        columnsToMerge.push(i + 1); // '+1' because column indices are 1-based
      }
    }

    if (columnsToMerge.length > 1) {
      // If there's more than one column with the same name
      var sumData = [];
      for (var col of columnsToMerge) {
        var columnData = sheet.getRange(2, col, sheet.getLastRow() - 1).getValues(); // '-1' to exclude the header
        for (var j = 0; j < columnData.length; j++) {
          if (!sumData[j]) sumData[j] = [];
          sumData[j][0] = (sumData[j][0] || 0) + (columnData[j][0] || 0);
        }
      }

      // Write back summed data to the first column in the list
      sheet.getRange(2, columnsToMerge[0], sumData.length, 1).setValues(sumData);

      // Delete the other duplicate columns
      for (var k = columnsToMerge.length - 1; k > 0; k--) {
        sheet.deleteColumn(columnsToMerge[k]);
      }

      // Adjust headers array to reflect the deleted columns
      headers = headers.slice(0, columnsToMerge[1] - 1).concat(headers.slice(columnsToMerge[columnsToMerge.length - 1], headers.length));
    }
  }
}