2024-04-12 Pārbaudes darbs: Datubāzes

[Rozkalns]

• PT1: 40min (11:00-11:40): https://docs.google.com/forms/d/e/1FAIpQLSfU97xU2cJZNhB2vpNyC0vtYK8ZlNYKyWxcruh0sqgqcoig0w/viewform
• PT2: 60 min (11:45-12:45): https://docs.google.com/forms/d/e/1FAIpQLSeW9D8UZ3Jm7Wyqd1my0Yqu7MZ_5uvfp8nhmkfvGJBs-9WpUA/viewform
• PT3: 60 min (13:15 - 14:15): https://docs.google.com/forms/d/e/1FAIpQLSf7aCqiSET774ATR6CnqXkeVbgxEhigyfxYplDnse5FD6qeCg/viewform

[Rozkalns]

Sidenotes #1:

Sidenote #2:

Sidenote #3:

Sidenote #4:

function getRandomContent() {
  countEvaluatedCells();

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Evaluation");

  var ev_sheet = ss.getSheetByName("Evaluate");

  var lastRow = sheet.getLastRow();

  var validRows = [];

  for (var i = 1; i <= lastRow; i++) {
    var cellValue = sheet.getRange("D" + i).getValue();
    if (cellValue.length <= 1) {
      continue;
    }

    var cellValue = sheet.getRange("E" + i).getValue();
    if (!Number.isInteger(cellValue)) {
      validRows.push(i);
    }
  }

  if (validRows.length > 0) {
    var randomRowIndex = validRows[Math.floor(Math.random() * validRows.length)];

    var question = sheet.getRange("C" + randomRowIndex).getValue();
    var targetCell = ev_sheet.getRange("B9");
    targetCell.setValue(question);

    var randomContent = sheet.getRange("D" + randomRowIndex).getValue();

    var targetCell = ev_sheet.getRange("B10");
    targetCell.setValue(randomContent);
    
    var range = "E" + randomRowIndex;
    var evalLink = ss.getUrl() + "#gid=" + sheet.getSheetId() + "&range=" + range;

    var targetCell = ev_sheet.getRange("B12");
    targetCell.setValue(range);

    var targetCell = ev_sheet.getRange("B11");
    targetCell.setValue(evalLink);


  } else {
    Browser.msgBox("All cells in column E contain integers.");
  }
}

function store() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Evaluate"); // Change the sheet name if it's different
  var cellAddress = sheet.getRange("B12").getValue(); // Get the cell address from B12
  var valueToWrite = sheet.getRange("B16").getValue(); // Get the value from B16

  // Check if the value to write is empty
  if (valueToWrite !== "") {
    // Split the cell address to get row and column indices
    var cellAddressParts = cellAddress.split(/([A-Z]+)(\d+)/);
    var row = parseInt(cellAddressParts[2]);
    var column = cellAddressParts[1];

    // Write the value to the specified cell
    var targetCell = ss.getSheetByName("Evaluation").getRange(column + row);
    targetCell.setValue(valueToWrite);

    var response = Browser.msgBox("Value '" + valueToWrite + "' written to cell " + cellAddress + " in the Evaluation sheet. " + "Do you want to fetch a new value and reset B16?", Browser.Buttons.YES_NO);

    if (response == "yes") {
      sheet.getRange("B16").clearContent(); // Reset B16 to empty
      getRandomContent();
    } else {
      return; // End the script
    }
  } else {
    Browser.msgBox("No value to write. Cell B16 is empty.");
  }
}

function countEvaluatedCells() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var evalSheet = ss.getSheetByName("Evaluation");
  
  // Get the range of the E column in the Evaluation sheet
  var evalRange = evalSheet.getRange("E:E");

  // Get the values in the E column
  var evalValues = evalRange.getValues();

  // Initialize counters for evaluated and not evaluated cells
  var evaluatedCount = 0;
  var notEvaluatedCount = 0;

  // Iterate through the values and count evaluated and not evaluated cells
  evalValues.forEach(function(row) {
    if (row[0] !== "") {
      evaluatedCount++;
    } else {
      notEvaluatedCount++;
    }
  });

  // Calculate the percentage of evaluated and not evaluated cells
  var totalCells = evaluatedCount + notEvaluatedCount;
  var evaluatedPercentage = (evaluatedCount / totalCells) * 100;
  var notEvaluatedPercentage = (notEvaluatedCount / totalCells) * 100;

  var resSheet = ss.getSheetByName("Evaluate");
  // Write the counts and percentages to cell B7
  var resultString = "Evaluated: " + evaluatedCount + " (" + evaluatedPercentage.toFixed(2) + "%), Not Evaluated: " + notEvaluatedCount + " (" + notEvaluatedPercentage.toFixed(2) + "%)";
  var resultCell = resSheet.getRange("B7");
  resultCell.setValue(resultString);
}

function storeNotableInfo() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var evalSheet = ss.getSheetByName("Evaluate");
  var notableSheet = ss.getSheetByName("Notable");

  // Get the question, answer, and link from cells B9, B10, and B11 respectively
  var question = evalSheet.getRange("B9").getValue();
  var answer = evalSheet.getRange("B10").getValue();
  var link = evalSheet.getRange("B11").getValue();

  // Find the last available row in the Notable sheet
  var lastRow = notableSheet.getLastRow();
  var targetRow = lastRow + 1;

  // Write the information to the last available row in the Notable sheet
  notableSheet.getRange("A" + targetRow).setValue(question);
  notableSheet.getRange("B" + targetRow).setValue(answer);
  notableSheet.getRange("C" + targetRow).setValue(link);
}

[Rozkalns]

Attieksmes mainīšanai: https://docs.google.com/forms/d/e/1FAIpQLSdk2TH-q9DUeZwIMSxWnA6vAGBMHjmkLiyd0u79RkN26y2_nA/viewform