如何格式化UnprotectedRanges? [英] How to format UnprotectedRanges?

问题描述

我想在电子表格中的每个单元格周围放置一个红色边框，该电子表格没有为该单元格或单元格范围分配任何保护.我知道如何设置边框，但是如何访问不受保护的范围的格式?

I would like to put a red border around each cell in a spreadsheet which does not have any protection assigned to the cell or range of cells. I know how to set a border but how do I access the formatting for the unprotectedranges?

function wtf() {
var ui = SpreadsheetApp.getUi();
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];
var protection = sheet.protect();
var unprotected = protection.getUnprotectedRanges();  
  for (var i = 0; i < unprotected.length; i++) {
  ui.alert('this cell is unprotected');
  } 
}

推荐答案

问题:

有两种类型的保护 ¹ :

• 范围-仅保护某些范围.
• 表格-例外保护整个工作表.

如果使用工作表保护，则还可以使用UI将工作表保护中的某些范围(所谓的未保护范围)排除(除外).然后，可以使用protection.getUnprotectedRanges() ^{来检索这些不受保护的范围2} .工作表保护更适合您要执行的操作，此 answer 充分说明了这一点.然而，在片材中未被保护的范围，其中一些范围使用范围保护"来保护.无法轻易检索.

If you use sheet protection, you can also exclude(except) certain ranges from the sheet protection(so called the unprotected ranges) using the UI. These unprotected ranges can then be retrieved using protection.getUnprotectedRanges()². Sheet protection is better for what you want to do and this answer sufficiently explains it. However, the ranges that are unprotected in a sheet, where some ranges are protected using "range protection" cannot be retrieved easily.

• 为整个工作表上色，并只在受保护的范围内进行clearFormat/restore格式.

function colorUnprotectedRangesRed() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheets()[0]; //first sheet
  const prots = sh.getProtections(SpreadsheetApp.ProtectionType.RANGE); //get onlyRangeProtections
  const rngList = prots.map(function(pro) {
    return pro.getRange().getA1Notation();
  });
  sh.getRange('1:' + sh.getMaxRows()).setBorder(
    true,
    true,
    true,
    true,
    true,
    true,
    'red',
    SpreadsheetApp.BorderStyle.SOLID
  );
  //SpreadsheetApp.flush(); //flush the changes first before clearing format, if you have issues
  sh.getRangeList(rngList).clearFormat();
}

这篇关于如何格式化UnprotectedRanges?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！