javascriptgoogle-apps-scriptgoogle-sheetstriggersgs-conditional-formatting

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


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)
}

Solution

  • 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)
    }