I’m quite new in the Apps Script world. I believe my question would come in 2 different/separate scripts:
- As a new tab is created based on the same template, and the dates are being filled in, could the columns automatically be grouped (by script), with only the “Monday” of that week being visible? (Not sure if this is feasible, also I reckon not with protected ranges?
- Could only the column for today be shown via a checkbox? (as could be found scripted on this post). Miserably failed at trying for this so far
Notes:
- Columns A and B have to remain visible (frozen)
- Rows 1 to 5 as well (frozen)
- Current tab is called “September”, but more tabs (October etc) would need to have the same script being run
I tried the second point, but all my columns were just gone. I’m sure the problem lies with me lol
2
You have dates in columns; there is also a checkbox. If the status of the checkbox is:
checkedthen display the column for today’s datenot checkedthen display the column for Monday of this week.
You have unsuccessfully tried to apply the code from How to show columns based on today’s date, and hide the rest? (@Tanaike). The code in Google Sheets Apps Script to Find this weeks date for a day, as well as the follow weeks date for the same day was also helpful (@Kristkun).
Consider this script and the screenshots below (“today’s date” = 13/9/2024)
The key is in the syntax for show Columns – specifically the number of columns to show.
showColumns(columnIndex, numColumns)
In the referenced script, the data for one day consists of three columns (“Time In”, “Temp” and “Time Out” and the date is contained in the first column, so the script must display three columns:
sheet.showColumns(i + 3, 3)
In this case, the data for one day is a single column and this column also contains the date, so the script must display one column only.
sheet.showColumns(i + 3, 1)
function onEdit(e) {
const { range } = e
const sheet = range.getSheet()
// checkbox is in B1
if (range.getA1Notation() != "B1" || sheet.getSheetName() != "Sheet1") return
// test if checkbox is checked (= "Today")
if (!range.isChecked()) {
// "Today" Checkbox is not checked, so display Monday
// Logger.log("DEBUG: not checked, so display last Monday")
// show all columns (unhide any columns)
sheet.showColumns(3, sheet.getMaxColumns() - 2)
// get the script timezone
var timeZone = Session.getScriptTimeZone()
// Logger.log("DEBUG: timezone = "+timeZone)
var curr = new Date()
var mondayDate = curr.getDate() - curr.getDay()+1
// Logger.log("DEBUG: curr = "+curr+", mondayDate = "+mondayDate+", curr get date = "+curr.getDate()+", day = "+(curr.getDay()+1)+", set date mondayDate = "+curr.setDate(mondayDate))
var monday = Utilities.formatDate(new Date(curr.setDate(mondayDate)), timeZone, "E d MMM")
// Logger.log("DEBUG: monday = "+monday)
// hide all columns
sheet.hideColumns(3, sheet.getMaxColumns() - 2)
// show Monday only
sheet.getRange(5, 3, 1, sheet.getLastColumn() - 2).getDisplayValues()[0]
.forEach((e, i) => {
if (e == monday) {
sheet.showColumns(i + 3, 1)
}
})
return
}
// "Today" Checkbox IS checked, so today's date
// Logger.log("DEBUG: today = checked so display today")
// get the script timezone
var timeZone = Session.getScriptTimeZone()
// Logger.log("DEBUG: timezone = "+timeZone)
// get the date
var now = Utilities.formatDate(new Date(), timeZone, "E d MMM")
// Logger.log("now = "+now)
// hide all the columns
sheet.hideColumns(3, sheet.getMaxColumns() - 2)
// Logger.log("DEBUG: the data range = "+sheet.getRange(5, 3, 1, sheet.getLastColumn() - 2).getA1Notation())
// show today's date
sheet.getRange(5, 3, 1, sheet.getLastColumn() - 2).getDisplayValues()[0]
.forEach((e, i) => {
if (e == now) {
sheet.showColumns(i + 3, 1);
}
})
}
Sample – Raw data
Sample – checked
Sample – unchecked
4