Imports raid-Helper Signups for use as Dropdown selection on google sheet

RaidHelperImport.gs

var RaidHelperCellRow = 1;
var RaidHelperCellColumn = 19;

var sheet;

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Raid-Helper')
      .addItem('Import Event', 'showPromptEvent')
      .addItem('Update', 'update')
      .addToUi();
}

function showPromptEvent() {
  var ui = SpreadsheetApp.getUi(); // Same variations.

  var result = ui.prompt(
      'Import Raid-Helper Event',
      'Raid-Helper ID (leave empty for last id):',
      ui.ButtonSet.OK_CANCEL);

  var button = result.getSelectedButton();
  var id = result.getResponseText();
  if (button == ui.Button.OK) {
    ImportRaidHelperEvent(id);
  }
}

function update() {
  ImportRaidHelperEvent('')
}

function ImportRaidHelperEvent(id) {
  sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var cell = sheet.getRange(RaidHelperCellRow+2, RaidHelperCellColumn+1);
  
  if(id == '') id = cell.getValue();
  if(id == '') {
    SpreadsheetApp.getUi().alert("Raid-Helper Id missing!");
    return;
  }
  cell.setValue(id);

  var url ="https://raid-helper.dev/api/event/" + id;
  var jsondata = UrlFetchApp.fetch(url);
  var parsedData = JSON.parse(jsondata.getContentText());

  clearSignups();

  parseEventData(parsedData);
}

function parseEventData(data) {

  var signups = data.signups;
  signups.sort(compareName);
  var i=5;
  signups.forEach(function(signup) {
    var position = signup.position+4;

    // Name Cell
    var raidslot = sheet.getRange(RaidHelperCellRow+i, RaidHelperCellColumn);
    var name = getPlayerNameWithSpec(signup);
    raidslot.setValue(name);
    
    var color = getClassColorFromSpec(signup.spec_emote);
    raidslot.setBackground(color);
    
    // Role Cell
    var raidslot = sheet.getRange(RaidHelperCellRow+i, RaidHelperCellColumn+3);
    raidslot.setValue(signup.role);


    // Color Cell
    var raidslot = sheet.getRange(RaidHelperCellRow+i, RaidHelperCellColumn+4);
    raidslot.setValue(color);
    i++;


  });
}

function compareName(a, b) {
  if(a.name.toUpperCase() < b.name.toUpperCase())
    return -1;
  if(a.name.toUpperCase() > b.name.toUpperCase())
    return 1;
  return 0;
}

function clearSignups() {
  var range = sheet.getRange(RaidHelperCellRow+5, RaidHelperCellColumn, 200,5);
  range.clearContent();
  range.setBackground("#cccccc");
}

function getPlayerNameWithSpec(item) {
  switch(item.class) {
    case 'Tank':
      return "(T) " + item.name;
    case 'Bench':
      return "(B) " + item.name;
    default:
      break;
  }

  switch(item.spec_emote) {
    case '637564379847458846':
    case '637564323530539019':
    case '637564297622454272':
    case '637564172007112723':
    case '637564323442720768':
      return "(H) " + item.name;
    default:
     return item.name;
  }

  
}

function getClassColorFromSpec(spec) {
  switch(spec) {
    // Pala
    case '637564297647489034':
    case '637564297953673216':
    case '637564297622454272':
      return "#f48cba";
    // Shaman
    case '637564379772223489':
    case '637564379847458846':
    case '637564379595931649':
      return "#0070dd";
    // Hunter
    case '637564202021814277':
    case '637564202130866186':
    case '637564202084466708':
      return "#aad372";
    // Warlock
    case '637564406682877964':
    case '637564406984867861':
    case '637564407001513984':
      return "#8788ee";
    // Druid
    case '637564171696734209':
    case '637564171994529798':
    case '637564172007112723':
    case '637564172061900820':
      return "#ff7c0a";
    // Mage
    case '637564231545389056':
    case '637564231239073802':
    case '637564231469891594':
      return "#3fc7eb";
    // Warrior
    case '637564445215948810':
    case '637564445031399474':
    case '637564444834136065':
      return "#c69b6d";
    // Priest
    case '637564323530539019':
    case '637564323291725825':
    case '637564323442720768':
      return "#ffffff";
    // Rogue
    case '637564352169508892':
    case '637564352333086720':
    case '637564351707873324':
      return "#fff468";
    case '612343589070045200':
      return "#FF0000";
    default:
      return "#cccccc";
  }
}

function onEdit(e) {
  var changedCell = e.source.getActiveRange();
  var color = getColorForPlayerName(e.value, e.range.getSheet());
  if(color != '0') {
    changedCell.setBackground(color);
  }

}

function getColorForPlayerName(name, sheet) {
  var color = "0";
  var rangeData = sheet.getRange(6,19, 100,5).getValues();

  for (var i = 0; i < rangeData.length; i++)
  {
    color = "Row: " + (i+1);
    if(rangeData[i].constructor == Array && rangeData[i].length > 0)
    {
      if (rangeData[i][0] == name)
      {
        color = rangeData[i][4];
        break;
      }
    }
  }
  
  return color;
}