I’m working with a Google spreadsheet that contains employee data organized by months. I need to lock certain rows from being edited while keeping others open for modifications.
Basically, I want to protect all rows that contain data from January through July (previous months) and only allow editing for the current month’s entries. Each row has a month column that indicates which month the data belongs to.
I already tried using data validation, but that doesn’t work because users can just delete the validation rules. I need a more permanent solution using Google Apps Script that will automatically protect rows based on the month value in a specific column.
Can someone help me write a script that checks the month column and applies protection to rows containing historical months while leaving current month rows editable?
To achieve your goal, you need to create a script that evaluates each row based on the month specified in your target column. Begin by obtaining a reference to your sheet and leveraging getDataRange() to access all your data. Loop through the rows, extracting the month value and comparing it to the current month’s index obtained with new Date().getMonth() + 1. For the rows containing months from January to July, use sheet.getRange(rowIndex, 1, 1, sheet.getLastColumn()).protect() to apply protection. Remember to manage existing protections to avoid duplication by invoking getProtections() and remove() before creating new ones.
I had a similar requirement in my project and found that using a combination of month comparison and protection management works well. Create a function that first clears all existing protections using sheet.getProtections().forEach(protection => protection.remove()), then iterate through your data starting from row 2. For each row, extract the month value and convert it to a number for comparison. If the month is less than or equal to 7, protect that specific row using sheet.getRange(i, 1, 1, sheet.getLastColumn()).protect().setDescription('Historical data protection'). Set this function to run on a schedule or trigger it manually when needed. Make sure to handle edge cases where month values might be text instead of numbers by using proper parsing methods.
to protect those rows, use getRange().protect(). loop through your month data, compare it with the current month, and apply protection where needed. set it up as a trigger for automatic updates when the sheet changes.