How to make to update which cell is highlighted every day at midnight

Question

I'm attempting to make a Google Sheet Apps Script that updates daily at midnight. I want it to change which cell is highlighted every day. Each of the 9 sheets in my project represents a fictional month that has 40 days each. I want the 5th month (Van) to be the first user and the 12th day should be highlighted (D13). After cycling through the 40 days of Voan it should continue to the next month (starting at 1). When it reaches the last month it should restart the year in the first month (Peylior).

Here are the google sheets:
https://docs.google.com/spreadsheets/d/1d6aVBQo3plrW0Glx4LQf987j1PGiwq5BnklhvtyEh4c/edit? usp=sharing

Would this code work to do that:

function addTrigger() {
 ScriptApp.newTrigger("updateCell").timeBased().atHour(0).everyDays(1).create();
}

function updateCell() {
  //set color
  var color = (255,0,0)
  //open google sheets
  var ss = SpreadsheetApp
  //open the spreadsheet
  var sheet = ss.getActiveSpreadsheet()
  //create mnths array
  var mnths = [
    "Peylior",
    "Memnor",
    "Rollur",
    "Uduir",
    "Voan",
    "Azzaz",
    "Waap",
    "Dustrem",
    "Predco",
    ]
  //get "System"
  var sys = sheet.getSheetByName("System")
  //find the month
  var mnthind = mnths.indexOf(sys.getRange(1,2).getValue())
  //get day
  var day = sys.getRange(2,2).getValue()
  //add 1 to day
  day += 1
  //check if month is over
  if (day > 40) {
    //reset days
    day = 1
    //change month to next in array unless a year has passed
    if (mnthind=12) {sys.getRange(1,2).setValue(mnths[1])}
    sys.getRange(1,2).setValue(mnths[mnthind+1])
  }
  //set the background of current day to color
  sheet.getSheetByName(mnths[mnthind]).getRange(day+1,5).setBackground(color)
  //update all
  sys.getRange(1,2).setValue(mnths[mnthind]) 
  sys.getRange(2,2).setValue(day)
}

Answer 1

There are a few issues with the code.

First:

A new trigger will be added to the project every time you run addTrigger(), so be sure to check your project's triggers and removing duplicate triggers, you may not need.

Second:

You'll probably want to un-highlight the preceding day also, so you may want to code that in.

Third:

Your if is assigning not comparing. Use double equal signs (==) for comparisons.

Fourth:

Your calendar has 9 months not 12, so that was adjusted.

Example:

function updateCell() {

  var sheet = SpreadsheetApp.getActiveSpreadsheet()
  //create mnths array
  var mnths = [
    "Peylior",
    "Memnor",
    "Rollur",
    "Uduir",
    "Voan",
    "Azzaz",
    "Waap",
    "Dustrem",
    "Predco"
  ]
    //get "System"
    var sys = sheet.getSheetByName("System")
    //find the month
    var mnthind = mnths.indexOf(sys.getRange(1,2).getValue())
    //get day
    var day = sys.getRange(2,2).getValue()
    
    //remove the background of past day to color
    sheet.getSheetByName(mnths[mnthind]).getRange(day+1,4,1,2).setBackground("#d9d9d9")
    
    //add 1 to day
    day += 1
    //check if month is over
    if (day > 40) {
      //reset days
      day = 1
      //change month to next in array unless a year has passed
      if (mnthind == 8) {mnthind = 0}
      else{ mnthind++}
      sys.getRange(1,2).setValue(mnths[mnthind])
    }
  
  //set the background of current day to color
  sheet.getSheetByName(mnths[mnthind]).getRange(day+1,4,1,2).setBackground("yellow")
  //update all
  sys.getRange(1,2).setValue(mnths[mnthind]) 
  sys.getRange(2,2).setValue(day)
}

Answer 2

I don’t think you want a google apps script to do this, just use conditional formatting

Answer 3

Try this:

function updateCell() {
  const color='#ff0000';
  const def='#111111';//default color
  const mnths=["Peylior","Memnor","Rollur","Uduir","Voan","Azzaz","Waap","Dustrem","Predco"];
  const ss=SpreadsheetApp.getActive();
  const sys=ss.getSheetByName("System");
  var mnthind=mnths.indexOf(sys.getRange(1,2).getValue());//get current data before incrementing day
  var sheet=ss.getSheetByName(mnths[mnthind]);//current month before change
  var day=sys.getRange(2,2).getValue();//current day before change
  day+=1;
  if (day>40) {
    day=1;
    sheet.getRange(2,4,sheet.getLastRow()-1,1).setBackground(def);
    if (mnthind==8) {
      mnthindx=0;
      sys.getRange(1,2).setValue(mnths[mnthind]);
    }else{
      mnthind+=1;
      sys.getRange(1,2).setValue(mnths[mnthind])
    }
  }
  sheet=ss.getSheetByName(mnths[mnthind]).getRange(2,4,sheet.getLastRow()-1,1).setBackground(def);//set column to default color
  ss.getSheetByName(mnths[mnthind]).getRange(day+1,4).setBackground(color);//current day to selected color
  sys.getRange(1,2).setValue(mnths[mnthind]); //save current day and month in sys
  sys.getRange(2,2).setValue(day);
}

Not tested.