Question

一个人能否帮助我把这一保护范围之广的文字变成一个有多种不同例外情况的保护表? 在使用这一文字时,除范围外,还制作15份复印件,但复印件没有保护。感谢!

function combineScripts() {
const ss = SpreadsheetApp.getActiveSpreadsheet();

const sheetToCopy = ss.getSheetByName( Monthly Budget );
for (let i = 1; i < 15; i++) {
const newSheet = copySheetWithProtections\_(sheetToCopy, `Monthly Copy ${i}`);
}

const sourceSheet = ss.getSheetByName( Monthly Budget );
const copiedSheet = sourceSheet.copyTo(ss).setName( Monthly Copy );
const protections = sourceSheet.getProtections(SpreadsheetApp.ProtectionType.RANGE);
protections.forEach(p => {
const rangeNotation = p.getRange().getA1Notation();
const copiedProtection = copiedSheet.getRange(rangeNotation).protect();
copiedProtection.setDescription(p.getDescription());
copiedProtection.setWarningOnly(p.isWarningOnly());
if (!p.isWarningOnly()) {
copiedProtection.removeEditors(copiedProtection.getEditors());
copiedProtection.addEditors(p.getEditors());
}
});
}

function copySheetWithProtections\_(sourceSheet, newName) {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const newSheet = sourceSheet.copyTo(ss);
newSheet.setName(newName);

const protections = sourceSheet.getProtections(SpreadsheetApp.ProtectionType.RANGE);
protections.forEach(p => {
const rangeNotation = p.getRange().getA1Notation();
const copiedProtection = newSheet.getRange(rangeNotation).protect();
copiedProtection.setDescription(p.getDescription());
copiedProtection.setWarningOnly(p.isWarningOnly());
if (!p.isWarningOnly()) {
copiedProtection.removeEditors(copiedProtection.getEditors());
copiedProtection.addEditors(p.getEditors());
}
});

return newSheet;
}

Answer 1

如果a 受保护的表格有多种例外: ,是指受保护的表格的无保护范围,如何修改?

Modification points:

关于您目前的<代码>问题在使用该说明时,除各种例外情况外,还制作了15份复印件,但没有保护。 SHEET. 在你的文字中,只有“保护Type.RANGE”被复制。我认为,这是你目前问题的原因。
在您的文稿中,使用i < 15;,p =>{,>copySheetWith Protections_(,。在本案中发生错误。请对此加以仔细研究。

当这些问题反映在你的文字中时,如何修改?

Modified script:

function combineScripts() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheetToCopy = ss.getSheetByName( Monthly Budget );
  for (let i = 1; i < 15; i++) {
    copySheetWithProtections_(sheetToCopy, `Monthly Copy ${i}`);
  }
  copySheetWithProtections_(sheetToCopy,  Monthly Copy );
}

function copySheetWithProtections_(sourceSheet, newName) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const newSheet = sourceSheet.copyTo(ss);
  newSheet.setName(newName);
  const protections = sourceSheet.getProtections(SpreadsheetApp.ProtectionType.RANGE);
  protections.forEach(p => {
    const rangeNotation = p.getRange().getA1Notation();
    const copiedProtection = newSheet.getRange(rangeNotation).protect();
    copiedProtection.setDescription(p.getDescription());
    copiedProtection.setWarningOnly(p.isWarningOnly());
    if (!p.isWarningOnly()) {
      copiedProtection.removeEditors(copiedProtection.getEditors());
      copiedProtection.addEditors(p.getEditors());
    }
  });
  const protections2 = sourceSheet.getProtections(SpreadsheetApp.ProtectionType.SHEET);
  protections2.forEach(p => {
    const copiedProtection = newSheet.protect();
    copiedProtection.setDescription(p.getDescription());
    copiedProtection.setWarningOnly(p.isWarningOnly());
    copiedProtection.setUnprotectedRanges(p.getUnprotectedRanges().map(r => newSheet.getRange(r.getA1Notation())));
    if (!p.isWarningOnly()) {
      copiedProtection.removeEditors(copiedProtection.getEditors());
      copiedProtection.addEditors(p.getEditors());
    }
  });
}

By this modification, both protection types of SpreadsheetApp.ProtectionType.RANGE and SpreadsheetApp.ProtectionType.SHEET are copied to the copied sheets.

Modification points:

Modified script:

References:

友情链接