eurovision-2023.exe

eurovision2023.gs

function GetAllSheetNames(time) {
  var out = new Array()
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  for (var i=0 ; i<sheets.length ; i++) {
    if (sheets[i].getName() != "Overview") {

      out.push( [ sheets[i].getName() ] )
    }

  }
  return out 
}

function onOpen() {
  // refreshOverview();
}

function onEdit(e) {
  if (e.source.getActiveSheet().getName() == "Overview"){ 
    return;
  }
  refreshOverview();
}

function onChange(e) {
  if (e.source.getActiveSheet().getName() == "Overview"){ 
    return;
  }
  refreshOverview(); 
}

function createTimeDrivenTriggers() {
  // Trigger every minute
  // ScriptApp.newTrigger('refreshOverview')
  //     .timeBased()
  //     .everyMinutes(1)
  //     .create();

}

function refreshOverview(e) {
  Logger.log('I ran'); // can see this in the logs
  var currentDateTime = Utilities.formatDate(new Date(), "GMT", "yyyy-MM-dd'T'HH:mm:ss'Z'");
  SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Overview").getRange(2,1).setFormula(`GetAllSheetNames("${currentDateTime}")`)
  SpreadsheetApp.flush();
}

function VotingCompleted(sheetName) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  var count = 0;
  for (var x = 0; x < 9; x++) {
    for (var y = 0; y < 27; y++) {
      var cellValue = sheet.getRange(y + 1, x + 5).getValue();
      if (cellValue == "-") {
        return false;
      }
    }
  }
  return true;
}

function GetTotalTens(sheetName) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  var count = 0;
  for (var x = 0; x < 9; x++) {
    for (var y = 0; y < 27; y++) {
      var cellValue = sheet.getRange(y + 1, x + 4).getValue();
      if (cellValue.toString() == "10") {
        count++;
      }
    }
  }

  console.log(count)
  return count;
}

function HighestScoringCountry(sheetName){
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  var count = 0;

  var highestCountryName = "";
  var highestScore = 0;
  
  for (var y = 0; y < 27; y++) {
    var cellValue = sheet.getRange(y + 1, 13).getValue();
    if (cellValue > highestScore) { 
      highestCountryName = sheet.getRange(y + 1, 2).getValue()
    }
  }

  return highestCountryName;
}