I’m using Google Apps Script and I am trying to write a program that consolidates my monthly notes. I have hidden columns i-t which are labeled “January 2024” – “December 2024” chronologically. I have a dropdown configured in column H that shows “January 2024” – “December 2024” to select from.
The idea is to hide the 12 months of notes (which looks terrible on the sheet), and let me unhide a singular column of my choosing. “January 2024” from the dropdown, should unhide column i named “January 2024”.
For some reason it’s not doing anything. Not sure what to do.
Here’s the code:
function onEdit(e) {
// Ensure the function only runs during actual edits
if (!e) return; // If no event object (e), stop the execution to avoid errors
var sheet = e.source.getActiveSheet();
// Ensure the edit was made in column H (8th column) and after the header row
if (e.range.getColumn() == 8 && e.range.getRow() > 1) {
var selectedMonth = e.range.getValue(); // Get the value of the selected month
// Define the mapping of months to columns
var monthMapping = {
"January 2024": 9, // Column I
"February 2024": 10, // Column J
"March 2024": 11, // Column K
"April 2024": 12, // Column L
"May 2024": 13, // Column M
"June 2024": 14, // Column N
"July 2024": 15, // Column O
"August 2024": 16, // Column P
"September 2024": 17,// Column Q
"October 2024": 18, // Column R
"November 2024": 19, // Column S
"December 2024": 20 // Column T
};
// Notes columns from I to T (9 to 20)
var notesColumnStart = 9;
var notesEndColumn = 20;
// Unhide all columns from I to T
sheet.showColumns(notesColumnStart, notesEndColumn - notesColumnStart + 1);
// Check if the selected month exists in the mapping
if (selectedMonth in monthMapping) {
var selectedColumn = monthMapping[selectedMonth]; // Get the corresponding column number
// Check if the selected column is already visible
var isColumnVisible = !sheet.isColumnHiddenByUser(selectedColumn);
if (isColumnVisible) {
// If the column is visible, re-hide it (toggle behavior)
sheet.hideColumns(selectedColumn);
Logger.log("Hiding column: " + selectedColumn);
} else {
// If the column is hidden, show it and hide all others
Logger.log("Showing column: " + selectedColumn);
for (var col = notesColumnStart; col <= notesEndColumn; col++) {
if (col !== selectedColumn) {
sheet.hideColumns(col); // Hide the column if it is not the selected month
}
}
sheet.showColumns(selectedColumn); // Make sure the selected column is shown
}
} else {
// If the selected month isn't in the mapping, log the issue
Logger.log("Month not found in the mapping: " + selectedMonth);
}
}
}