dimanche 12 mai 2019

Is there any way to write these if statements more efficiently?

I have a spreadsheet where there are a large number of sheets detailing information for each "job" the person filling out the sheet can have. To clean this up, I wrote a script to hide or show the sheets based on which jobs they chose on the first page of the sheet - they can choose up to 3.

The script... works, but I've gotten errors saying it's trying to do too much at once and failed. I'm not exactly a great programmer so how to clean this up is, as of yet, fairly beyond me. I'm not looking for the most efficient, I'm just looking for something that works easily enough.

I googled the problem a few times, but a lot of the solutions I saw didn't seem to exactly fit what I was doing, and involved things like arrays and dictionaries?

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet1 = ss.getSheetByName("Character Sheet");
  var sheet2 = ss.getSheetByName("Marauder Abilities");
  var sheet3 = ss.getSheetByName("Warrior Abilities");
  var sheet4 = ss.getSheetByName("Dark Knight");
  var sheet5 = ss.getSheetByName("Gladiator");
  var sheet6 = ss.getSheetByName("Paladin");
  var sheet7 = ss.getSheetByName("Conjurer");
  var sheet8 = ss.getSheetByName("White Mage");
  var sheet9 = ss.getSheetByName("Arcanist");
  var sheet10 = ss.getSheetByName("Scholar");
  var sheet11 = ss.getSheetByName("Astrologian");
  var sheet12 = ss.getSheetByName("Pugilist");
  var sheet13 = ss.getSheetByName("Monk");
  var sheet14 = ss.getSheetByName("Lancer");
  var sheet15 = ss.getSheetByName("Dragoon");
  var sheet16 = ss.getSheetByName("Rogue");
  var sheet17 = ss.getSheetByName("Ninja");
  var sheet18 = ss.getSheetByName("Samurai");
  var sheet19 = ss.getSheetByName("Archer");
  var sheet20 = ss.getSheetByName("Bard");
  var sheet21 = ss.getSheetByName("Machinist");
  var sheet22 = ss.getSheetByName("Summoner");
  var sheet23 = ss.getSheetByName("Thaumaturge");
  var sheet24 = ss.getSheetByName("Black Mage");
  var sheet25 = ss.getSheetByName("Red Mage");
  var sheet26 = ss.getSheetByName("Garlean Pureblood");

  var cell1 = sheet1.getRange('B5');
  var cell2 = sheet1.getRange('C5');
  var cell3 = sheet1.getRange('D5');

  if (cell1.getValue() != "Marauder"||cell2.getValue() != "Marauder"||cell3.getValue() != "Marauder") {
    sheet2.hideSheet();
  }

  if (cell1.getValue() == "Marauder"||cell2.getValue() == "Marauder"||cell3.getValue() == "Marauder") {
    sheet2.showSheet();
  }

  if (cell1.getValue() != "Warrior"||cell2.getValue() != "Warrior"||cell3.getValue() != "Warrior") {
    sheet3.hideSheet();
  }

  if (cell1.getValue() == "Warrior"||cell2.getValue() == "Warrior"||cell3.getValue() == "Warrior") {
    sheet3.showSheet();
  }

  if (cell1.getValue() != "Dark Knight"||cell2.getValue() != "Dark Knight"||cell3.getValue() != "Dark Knight") {
    sheet4.hideSheet();
  }

  if (cell1.getValue() == "Dark Knight"||cell2.getValue() == "Dark Knight"||cell3.getValue() == "Dark Knight") {
    sheet4.showSheet();
  }

It goes on from there for all 26 sheets.

Is there an easier way to write this massive thing out?

Aucun commentaire:

Enregistrer un commentaire