-3

I have a Google sheet which will have historical data updated by the employees. I want to freeze (non-editable) the rows having the data of Jan to July (till the previous month) and leave the current month's rows for editing. There is a column with Month for each row. Any row which has month as Jan to July (till previous month), those rows should be protected for editing. I have tried using Data Validation, but it can be removed it by anyone who has access to the sheet.

Here is a sample sheet: https://docs.google.com/spreadsheets/d/102Rs93sZxeY2rq1WJEIL50P_6RLRzOCmDlIH6ZoBTx8/edit#gid=0

Marios
  • 26,333
  • 8
  • 32
  • 52
Sunny Jayadev
  • 43
  • 10
  • 1
    Does this answer your question? [Protect ranges with google apps script](https://stackoverflow.com/questions/38993561/protect-ranges-with-google-apps-script) – joshmeranda Aug 25 '20 at 14:04
  • Share a small copy of your sheet so we can see the structure, the format of the values etc. – Marios Aug 25 '20 at 14:10
  • Collect the data with a webapp and only collect data for the current month and don't provide edit access to the Spreadsheet at all. – Cooper Aug 25 '20 at 15:09
  • @Marios, here is the link for Dummy data sheet. All the rows with Month column till July should be protected for editing and current month should be allowed for editing till the month gets over. Once September starts, automatically, all the rows till August should be protected and current month rows should be editable. [https://docs.google.com/spreadsheets/d/102Rs93sZxeY2rq1WJEIL50P_6RLRzOCmDlIH6ZoBTx8/edit?usp=sharing] – Sunny Jayadev Aug 25 '20 at 15:17
  • @Cooper They should be able to edit the current month's data in the same sheet – Sunny Jayadev Aug 25 '20 at 15:19
  • They can perform all of the required editing from within a webapp. And the webapp can retreive all of the required data from the spreads via google.script.run – Cooper Aug 25 '20 at 20:25

1 Answers1

1

Solution:

This is what you are looking for:

function protectData() {
  
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Sheet1');
  const today_date = new Date();
  const today_month = today_date.toLocaleString('default', { month: 'short' });
  const today_full_date = `${today_month}-${today_date.getFullYear()}`
  const months = sh.getRange('C2:C'+sh.getLastRow()).getDisplayValues().flat(1);
  const protection = sh.protect();
   
  const unprotected = [sh.getRange(sh.getLastRow()+1,1,sh.getMaxRows()-sh.getLastRow(),sh.getMaxColumns()),
                      sh.getRange(1,sh.getLastColumn()+1,sh.getMaxRows(),sh.getMaxColumns()-sh.getLastColumn())];
  
  months.forEach((month,index)=>{          
                 if(month===today_full_date)
                       {                    
                          unprotected.push(sh.getRange(index+2,1,1,sh.getLastColumn()));                                           
                       }           
                 })
    protection.setUnprotectedRanges(unprotected);                
}

Explanation:

Here are the steps:

  1. get the month-year of today in the format of Jan-2020,
  2. get all the date values of column C,
  3. protect the full sheet,
  4. iterate through the date values of column C: if month-year of today matches a date value, unprotect the row.

Result:

Only the rows that contain Aug-2020 can be edited (unprotected):

result

Marios
  • 26,333
  • 8
  • 32
  • 52
  • awesome, its working. Since, this code is protecting the entire sheet, others are unable to add the data in new rows. The other blank rows should be unprotected and should be allowed to enter the data. – Sunny Jayadev Aug 25 '20 at 18:14