Protect specified range in google Sheet with GAS

Question

function Lock(){ var sh = SpreadsheetApp.openById(ssID).getSheetByName('6'); var protection = sh.protect().setDescription('BVDATA'); var me = Session.getEffectiveUser(); protection.addEditor(me).setUnprotectedRanges(sh.getRange("F1:F").createTextFinder("^(?!OK).*$").matchEntireCell(true).useRegularExpression(true).findAll()); protection.removeEditors(protection.getEditors()); if (protection.canDomainEdit()){ protection.setDomainEdit(false); } }

@Tanaike guided me to this. Now i have a problem, that code will automatically protect 1 cell F if it value is OK. Is there a way for me it will protect F:G(like F5:G5 will be protected if F5 value OK) if F has a value of OK?. I tried modification :

protection.addEditor(me).setUnprotectedRanges(sh.getRange("F1:G").CreateTextFinder("^(?!OK).*$").MatchEntireCell(true).useRegularExpression(true).findAll());

but it only protects cell G when the value is OK.

Tanaike · Accepted Answer · 2021-04-17 04:55:04Z

1

I believe your goal as follows.

You want to protect the cells of the columns "F" and "G", only when the cell value is OK.

In order to achieve this, how about the following modification?

From:

protection.addEditor(me).setUnprotectedRanges(sh.getRange("F1:F").createTextFinder("^(?!OK).*$").matchEntireCell(true).useRegularExpression(true).findAll());

To:

protection.addEditor(me).setUnprotectedRanges(sh.getRange("F1:F").createTextFinder("^(?!OK).*$").matchEntireCell(true).useRegularExpression(true).findAll().map(r => r.offset(0, 0, 1, 2)));

In this modification, the searched range is expanded using offset.

Reference:

offset(rowOffset, columnOffset, numRows, numColumns)

answered Apr 17, 2021 at 4:55

Tanaike

203k12 gold badges126 silver badges222 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

Tanaike Over a year ago

@Leti Thank you for replying. I'm glad your issue was resolved.

Tanaike Over a year ago

@Leti About your new question of your comment, I will check it. When I could correctly understand about your new question, I would like to think of the solution.

Tanaike Over a year ago

@Leti About your new question, I have to apologize for my poor English skill. Unfortunately, from your comment, I cannot understand your new question. But I would like to try to understand it. When I could correctly understand it, I would like to think of the solution. I deeply apologize I cannot resolve your issue soon.

Tanaike Over a year ago

@Leti Thank you for replying. From your replying, I cannot still understand about your new question. I think that this is due to my poor English skill. I deeply apologize for this again. For example, by including the sample situation, can you post it as new question? By this, I would like to try to understand it. And also, in that case, the users including me can also think of the solution. When you can cooperate to resolve your new question, I'm glad. Can you cooperate to do it?

Tanaike Over a year ago

@Leti Thank you for replying. I have to apologize for my poor English skill. Unfortunately, I cannot understand about hihi. Your quotes :. Can I ask you about the detail of it?

|

Collectives™ on Stack Overflow

Protect specified range in google Sheet with GAS

1 Answer 1

From:

To:

Reference:

11 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

From:

To:

Reference:

11 Comments

Linked

Related