Protect whole row in a sheet if column F checkbox is set to true

[Hamid Gatch]

Hi every body

would you please some one help me to a code for my task.

I want to protect a row when user pressed checkbox in column F and unprotect if Unchecked

Thank you 

[Bennett, Scott]

Try this, it should work and you can edit it if needed.

function onEdit() {

  let ss = SpreadsheetApp.getActiveSpreadsheet();

  let sheet = ss.getActiveSheet();

  let cell = sheet.getActiveCell()

  let col = cell.getColumn();//gets column number

  let rownum = cell.getRow();//gets row number

 

  if(col==6 && cell.getValue() == true){//if changed to true protects row to end of data

    sheet.getRange(rownum,1,1,sheet.getLastColumn()).protect().setDescription(rownum+'Protected')

    }

  

  else if(col == 6 && cell.getValue() ==false){//if changed to false cycles through all the protected ranges on the sheet and unprotects if the row number is the same

    let protections = sheet.getProtections(SpreadsheetApp.ProtectionType.RANGE)

    for (let i = 0;i<protections.length;i++){

      let proDesc = protections[i].getDescription();

      if(parseInt(proDesc.substr(0,1))==rownum){

      protections[i].remove()

      }

    }

  }

}

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/3143ced8-76a5-492f-974a-de202c6fc92cn%40googlegroups.com.

--

Scott Bennett

[Hamid Gatch]

Dear Scott

Thank you very much

it working

[Bennett, Scott]

I found an issue, use this one instead.

function onEdit() {

  let ss = SpreadsheetApp.getActiveSpreadsheet();

  let sheet = ss.getActiveSheet();

  let cell = sheet.getActiveCell()

  let col = cell.getColumn();//gets column number

  let rownum = cell.getRow();//gets row number

 

  if(col==6 && cell.getValue() == true){//if changed to true protects row to end of data

    sheet.getRange(rownum,1,1,sheet.getLastColumn()).protect().setDescription(rownum)

    }

  

  else if(col == 6 && cell.getValue() ==false){//if changed to false cycles through all the protected ranges on the sheet and unprotects if the row number is the same

    let protections = sheet.getProtections(SpreadsheetApp.ProtectionType.RANGE)

    for (let i = 0;i<protections.length;i++){

      let proDesc = protections[i].getDescription();

      if(parseInt(proDesc)==rownum){

      protections[i].remove()

      }

    }

  }

}

On Thu, Jun 24, 2021 at 12:27 AM Hamid Gatch <hamid...@gmail.com> wrote:

--

You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/3143ced8-76a5-492f-974a-de202c6fc92cn%40googlegroups.com.

--

Scott Bennett

[Hamid Gatch]

yes you are right. the above working well. 

But how could it possible to run this code not on Onedit but also onchange column 6?

[Bennett, Scott]

I am not sure what you mean. 

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/b259d66a-28ed-45b1-995f-7f87002c2db9n%40googlegroups.com.

[Hamid Gatch]

the check box in column 6 is comming from another sheet by importrange function. so there is no editing om column 6 but it changes when user in another spreadsheet tick checkbox so in my current sheet ther is no editing on column 6, so this column changes from true to false only by importrang. so i nead another triger i think it shoul be onchange trigger rather than onedit,

[Bennett, Scott]

Onchange is only when you make changes to the structure of the spreadsheet.  

The only way I can think to fix this is to have code that actually grabs the values of the other spreadsheet then pastes it to yours.  

• An installable change trigger runs when a user modifies the structure of a spreadsheet itself—for example, by adding a new sheet or removing a column.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/814932d2-b271-4fcb-a18a-3d8391666a93n%40googlegroups.com.

--

Scott Bennett

[Hamid Gatch]

so how could grab data from other sheet, 

another problem with above code is that the protected area after changing column 6 to true is editable by editors i shared the sheet to them. 

[Bennett, Scott]

Well you could have the other sheet with an installable onEdit trigger that would transfer that value over to the sheet with checkbox 

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/f0c6b23a-8fe4-433a-a9d0-9477b008fbc1n%40googlegroups.com.

--

Scott Bennett

Data and Assessment Coordinator/Math Teacher

Bradley-Bourbonnais Community High School

P 815.937.3707 ext. 6010

[Hamid Gatch]

ok... 

thank you

and  another problem with above code is that the protected area after changing column 6 to true is editable by editors i shared the sheet to them

 i want only only owner can  edit protected area

[Bennett, Scott]

function onEdit() {

  let ss = SpreadsheetApp.getActiveSpreadsheet();

  let sheet = ss.getActiveSheet();

  let cell = sheet.getActiveCell()

  let col = cell.getColumn();//gets column numnber

  let rownum = cell.getRow();//gets row number

 

  if(col==6 && cell.getValue() == true){//if changed to true protects row to end of data

    let protRange = sheet.getRange(rownum,1,1,sheet.getLastColumn()).protect().setDescription(rownum);

    protRange.removeEditors(protRange.getEditors());

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/eabc06f9-48da-4ac6-9532-0249562550edn%40googlegroups.com.

--

Scott Bennett

[Hamid Gatch]

Dear Scott

hi,

first of all thank you very much for helping me to improve my codes in google sheet script

I wrote the below code with the help of sample code on the web in order to save some sheets on my spreadsheet as pdf file on my google drive folders. This spreadsheet shared also by me to some other users.

The below code adds a menu with a sub menu and when I click on the "PDF Store" sub menu a copy of the pdf file just in the "MAHSOL" or "MAVAD" sheet created and stored in the "ANBAR-PDF-1400" folder.

it works fine for me but when i share the same sheet for other users it doesn't work and the below message displayed:

Exception: Cannot retrieve the next object: iterator has reached the end.

Would you please let me know what correction should be done in code in order to do solve the problem

regards,

Hamid

below you can see my code:

// By default, PDFs are saved in your Drive Root folder
// To save in the same folder as the spreadsheet, change the value to 'false' without the single quote pair
// You must have EDIT permission to the same folder
var saveToRootFolder = false


function onOpen() {
  var submenu = [{name:" PDF Store ", functionName:"exportCurrentSheetAsPDF"}];
  SpreadsheetApp.getActiveSpreadsheet().addMenu('Store', submenu);

}

/*

function onOpen() {
  SpreadsheetApp.getUi()
    .createAddonMenu()
    //.addItem('Export all sheets', 'exportAsPDF')
    //.addItem('Export all sheets as separate files', 'exportAllSheetsAsSeparatePDFs')
    .addItem('Export Sheet', 'exportCurrentSheetAsPDF')
    //.addItem('Export selected area', 'exportPartAsPDF')
    //.addItem('Export predefined area', 'exportNamedRangesAsPDF')
    .addToUi()
}
*/

function _exportBlob(blob, fileName, spreadsheet) {
  blob = blob.setName(fileName)
  const folderName = `ANBAR-PDF-1400`;
 
  var folder = saveToRootFolder ? DriveApp : DriveApp.getFoldersByName(folderName).next()
  var pdfFile = folder.createFile(blob)
 
  // Display a modal dialog box with custom HtmlService content.
  const htmlOutput = HtmlService
    .createHtmlOutput('<p>Click to open <a href="' + pdfFile.getUrl() + '" target="_blank">' + fileName + '</a></p>')
    .setWidth(300)
    .setHeight(80)
  SpreadsheetApp.getUi().showModalDialog(htmlOutput, 'Export Successful')
}

function exportAsPDF() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet()
  var blob = _getAsBlob(spreadsheet.getUrl())
  _exportBlob(blob, spreadsheet.getName(), spreadsheet)
}

function _getAsBlob(url, sheet, range) {
  var rangeParam = ''
  var sheetParam = ''
  if (range) {
    rangeParam =
      '&r1=' + (range.getRow() - 1)
      + '&r2=' + range.getLastRow()
      + '&c1=' + (range.getColumn() - 1)
      + '&c2=' + range.getLastColumn()
  }
  if (sheet) {
    sheetParam = '&gid=' + sheet.getSheetId()
  }
  // A credit to https://gist.github.com/Spencer-Easton/78f9867a691e549c9c70
  // these parameters are reverse-engineered (not officially documented by Google)
  // they may break overtime.
  var exportUrl = url.replace(/\/edit.*$/, '')
      + '/export?exportFormat=pdf&format=pdf'
      + '&size=A4'
      + '&portrait=true'
      + '&fitw=true'      
      + '&top_margin=0.5'              
      + '&bottom_margin=0.5'          
      + '&left_margin=0.7'            
      + '&right_margin=0.7'          
      + '&sheetnames=false&printtitle=false'
      + '&pagenum=CENTER' // change it to CENTER to print page numbers
      + '&gridlines=true'
      + '&fzr=TRUE'      
      + sheetParam
      + rangeParam
     
  Logger.log('exportUrl=' + exportUrl)
  var response
  var i = 0
  for (; i < 5; i += 1) {
    response = UrlFetchApp.fetch(exportUrl, {
      muteHttpExceptions: true,
      headers: {
        Authorization: 'Bearer ' +  ScriptApp.getOAuthToken(),
      },
    })
    if (response.getResponseCode() === 429) {
      // printing too fast, retrying
      Utilities.sleep(3000)
    } else {
      break
    }
  }
 
  if (i === 5) {
    throw new Error('Printing failed. Too many sheets to print.')
  }
 
  return response.getBlob()
}


function exportCurrentSheetAsPDF() {

  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet()
  var currentSheet = SpreadsheetApp.getActiveSheet()
 
  if(currentSheet.getSheetName() == "MAHSOL")
  {  
    var finv = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("MAHSOL");
    var blob = _getAsBlob(spreadsheet.getUrl(), currentSheet)
    _exportBlob(blob, ' GS '+finv.getRange(6,11).getValue()+'-'+finv.getRange(5,3).getValue(), spreadsheet)
  }

  if(currentSheet.getSheetName() == "MAVAD")
  {  
    var finv = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("MAVAD");
    var blob = _getAsBlob(spreadsheet.getUrl(), currentSheet)
    _exportBlob(blob, ' GM '+finv.getRange(6,11).getValue()+'-'+finv.getRange(5,3).getValue(), spreadsheet)
  }
 
}

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAL7%3DANGSwB5U5grEo6Z-76zZBwnYKJ6jT6qRTZ538ztt%3DE8O%2Bw%40mail.gmail.com.

[Bennett, Scott]

Not really sure on this one.  I do not do a lot of things in drive.  Perhaps, someone else in the group could help.  

When it comes to other people running the code you wrote, just make sure they are signed out of all other google accounts.  

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CA%2BFa9gOv9BBPWU-51WdUZaPoODHaPaSuuJfcGLzHLTAuMP4dnw%40mail.gmail.com.

[Hamid Gatch]

Dear Scott

Thank you for your reply

I found the solution

I should share the folders to users.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAL7%3DANFsw%3Do_SG%3DzviDFk7dXha1%2BaOwtSOe%3DoHRRcfBZUag1DA%40mail.gmail.com.

[CBMServices Web]

The issue with sharing spreadsheets and expecting the script to work for others is a problem of authorization.

Every user who has access (edit) of the spreadsheet, must authorize the script to run before it can function for them.

If you only give view access of the spreadsheet, then they can not get access to the script functions to use or authorize.

If you have many users who will be using the spreadsheet function, you are best to look at add-ons to deploy it. If only a few, then you will need to explain to them how to authorize the script. Note that giving them edit access also means they can modify the script as well..

So decide wisely how you want to handle this.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAL7%3DANFsw%3Do_SG%3DzviDFk7dXha1%2BaOwtSOe%3DoHRRcfBZUag1DA%40mail.gmail.com.

[Игорь Зарудный]

const ss = SpreadsheetApp.getActiveSpreadsheet()

const mainPage = ss.getSheetByName('Main') // change name of the list

const valueFromMainPage = mainPage.getDataRange().getValues()

function myFunction() {

  for (i = 1; i < valueFromMainPage.length; i++) {

    if (valueFromMainPage[i][18] === true) { // change namber of cell

        let protection = mainPage.getRange(i + 1, 1,1,valueFromMainPage[i].length).protect()

        protection.removeEditors(['test.z...@gmail.com']) //change email

    }

  }

}

[azra t1]

hello guys can you help me with this task pls so in my sheet there is me the owner and other editors .so for a specific range of cells i want the editors to fill the cells but once the cell is filled they can't change it and they don't have permission to edit the celles that they already  were filled .so once any cell in that range is filled they can't change it again only me the owner can and thanks