google-apps-script - 运行带有锁定单元格的脚本

Question

我编写了以下脚本，作为具有完全编辑权限的工作表的创建者，我可以正确运行。该工作表还与另一个具有编辑权限的人共享，但某些单元格已为该用户锁定。结果，当他们打开工作表时，脚本无法运行。错误似乎首先出现在这一行：

var values = range.getValues().map(function(d){ return d[0] })

我试图重写代码，但我最终所做的只是破坏它。如果我必须为用户解锁单元格，那么我如何确保他们不能更改在那里输入的信息？信息通过公式从锁定和隐藏的工作表中调用。

function onOpen(){

  var ss = SpreadsheetApp.getActive();
  var sheetsCount = ss.getNumSheets();
  var sheets = ss.getSheets();

  for (var i = 0; i < sheetsCount; i++){

  var sheet = sheets[i]; 

  var range = sheet.getRange(6, 3, 35);
  var values = range.getValues().map(function(d){ return d[0] }); 

  //clear previous border
  var selection = sheet.getRange(6,2,35,5)
  selection.setBorder(false,false,false,false,false,false);  

  //set border
  var index = values.indexOf("");

      var border = sheet.getRange(5, 2, index+1, 5);
  border.setBorder(true, true, true, true, true, true);}
}

标签： google-apps-scriptgoogle-sheets

Answer 1

so if I have an installable trigger the script will run because the person has editing rights but via my credentials so bypassing the locked cells. Would the following adaptation achieve this?

function createSpreadsheetOpenTrigger() {
  var ss = SpreadsheetApp.getActive();
  ScriptApp.newTrigger('borders')
      .forSpreadsheet(ss)
      .onOpen()
      .create();
}

function borders(){

  var ss = SpreadsheetApp.getActive();
  var sheetsCount = ss.getNumSheets();
  var sheets = ss.getSheets();

  for (var i = 0; i < sheetsCount; i++){

  var sheet = sheets[i]; 

  var range = sheet.getRange(6, 3, 35);
  var values = range.getValues().map(function(d){ return d[0] }); 

  //clear previous border
  var selection = sheet.getRange(6,2,35,5)
  selection.setBorder(false,false,false,false,false,false);  

  //set border
  var index = values.indexOf("");

      var border = sheet.getRange(5, 2, index+1, 5);
  border.setBorder(true, true, true, true, true, true);}
}