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)
}
There are a few issues with the code.
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.
You'll probably want to un-highlight the preceding day also, so you may want to code that in.
Your if is assigning not comparing. Use double equal signs (==) for comparisons.
Your calendar has 9 months not 12, so that was adjusted.
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)
}