# 用 Apps Script 設定 Google Sheets 保護欄位

保護 key

function protectColumnsWithKey() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();

  // 遍歷每個工作表
  for (var i = 0; i < sheets.length; i++) {
    var sheet = sheets[i];
    var range = sheet.getRange(1, 1, 1, sheet.getMaxColumns());
    var values = range.getValues();

    // 遍歷每個欄位的第一列
    for (var j = 0; j < values[0].length; j++) {
      if (values[0][j] === "key") {
        // 如果第一列的文字是 "key"，則設定該欄位為保護的範圍
        var protectRange = sheet.getRange(1, j + 1, sheet.getMaxRows(), 1);
        var protection = protectRange.protect().setDescription('Protected Column with Key');
        
        // 可編輯者的 email
        var allowedEditors = [
            '[e-mail]',
        ];
        protection.addEditors(allowedEditors);
      }
    }
  }
}

保護 en-US, zh-TW...

const language = ['en-US', 'zh-TW']
function protectColumnsWithKey() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();

  // 遍歷每個工作表
  for (var i = 0; i < sheets.length; i++) {
    var sheet = sheets[i];
    var range = sheet.getRange(1, 1, 1, sheet.getMaxColumns());
    var values = range.getValues();

    // 遍歷每個欄位的第一列
    for (var j = 0; j < values[0].length; j++) {
      if (language.includes(values[0][j])) {
        // 如果第一列的文字是 "key"，則設定該欄位為保護的範圍
        var protectRange = sheet.getRange(1, j + 1, sheet.getMaxRows(), 1);
        var protection = protectRange.protect().setDescription('Protected Column with Key');
        
        // 可編輯者的 email
        var allowedEditors = [
            '[e-mail]',
        ];
        protection.addEditors(allowedEditors);
      }
    }
  }
}