Googleスプレッドシートのシートと範囲を保護の機能を使えば、
- シート１を編集できるのはAさんだけ
- シート２の３行目を編集できるのはBさんだけ
・・・というように、範囲を編集できるユーザーを制限できます。
シートと範囲を保護するのはとても簡単ですが、複数の範囲にそれぞれ別の保護をかけたい場合、量が多いと手動で保護するのは大変です。
この記事では、シートや範囲の保護を、Google Apps Scriptを使って一括で行う方法を紹介します。
前提：Google Apps Scriptの使い方
前提として、Google Apps Scriptを使う方法を説明します。
知っている場合は読み飛ばしてください。
拡張機能メニューより、Apps Scriptを起動します。
あらかじめ入力されているコード（myFunction）を削除し、当記事のコードを貼り付けます。
貼り付け後、保存してください。
スクリプトが保存できたら、実行したい関数を選択して▷実行を押します。
シートごとに編集可能者を設定する
各シートに保護をかけ、シートごとに編集可能ユーザーを設定する例です。
下図のように、Ken、Lisa、Johnそれぞれに専用シートがあり、他の人は更新できないよう保護してみます。
「どのシートを誰が編集できるのか」を指定するシートを作成します。
シート名は保護リストとしました。
編集可能ユーザーに複数人指定したい場合は、user1@example.com,user2@example.comのように、カンマで区切ってメールアドレスを入力してください。
なお、個人のメールアドレスだけでなく、グループのアドレスも指定できます。
続いて、スクリプトを作成します。
下記のコードをスクリプトエディタに貼り付けてそのまま利用することができます。
（スクリプトエディタでprotectSheetsを選択して▷実行します）
/** @OnlyCurrentDoc */
function protectSheets() {
// スプレッドシートから保護対象の一覧を取得する
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const targets = spreadsheet.getSheetByName("保護リスト")
.getDataRange().getValues().slice(1); // 1行目を除くデータを取得
// 一覧にもとづいてシートを保護する
targets.forEach((target) => {
// 保護リストから、対象シートと編集可能ユーザーを取得
const sheetName = target[0];
const editors = target[1].split(","); // 複数アドレスが入力されていた場合はカンマで分割
// シートを保護
const targetSheet = spreadsheet.getSheetByName(sheetName);
const protection = targetSheet.protect();
protection.addEditors(editors);
});
}スクリプトが終了したら、スプレッドシートの画面に戻って結果を確認します。
データ > シートと範囲を保護 で保護の内容を確認できます。
期待どおり、シートが保護されていることが確認できました。
※ ここからは補足です。
シートを保護する際は、保護しない範囲を指定することができます。
先ほどと同じようにシートを保護しつつ、B3:C4の範囲は保護しない例を紹介します。
下記が、特定の範囲を保護対象外にする処理を加えたスクリプトです。
/** @OnlyCurrentDoc */
function protectSheets() {
// スプレッドシートから保護対象の一覧を取得する
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const targets = spreadsheet.getSheetByName("保護リスト")
.getDataRange().getValues().slice(1); // 1行目を除くデータを取得
// シートを保護する
targets.forEach((target) => {
// 保護リストから、対象シートと編集可能ユーザーを取得
const sheetName = target[0];
const editors = target[1].split(","); // 複数アドレスが入力されていた場合はカンマで分割
// シートを保護
const targetSheet = spreadsheet.getSheetByName(sheetName);
const protection = targetSheet.protect();
protection.addEditors(editors);
// 特定の範囲を保護しない
protection.setUnprotectedRanges([
targetSheet.getRange("B3:C4")
]);
});
}
実行後、データ > シートと範囲を保護 で保護の内容を確認すると、B3:C4を除いた保護がなされていることが確認できました。
なお、複数の範囲を保護対象外にしたい場合、下記のようにカンマで区切って複数指定します。
// 特定の範囲を保護しない（複数の範囲を指定する場合）
protection.setUnprotectedRanges([
targetSheet.getRange("B3:C4"),
targetSheet.getRange("B6:C7"),
targetSheet.getRange("B9")
]);ちなみに、シートの保護を一度全部解除したい場合は、下記のスクリプトを実行してください。
function removeAllSheetProtections() {
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const protections = spreadsheet.getProtections(SpreadsheetApp.ProtectionType.SHEET);
protections.forEach(protection => protection.remove());
}行ごとに編集可能者を設定する
行ごとに保護をかけ、それぞれに編集可能者を設定する例です。
「自分の行に入力してください（他の人の行を更新しないでください！）」
・・・というシチュエーションで利用できます。
A列に編集可能なユーザーをあらかじめ指定しておき、スクリプトを使って各行を保護します。
編集可能ユーザーに複数人指定したい場合は、user1@example.com,user2@example.comのように、カンマで区切ってメールアドレスを入力してください。
なお、個人のメールアドレスだけでなく、グループのアドレスも指定できます。
続いて、スクリプトを作成します。
下記のコードをスクリプトエディタに貼り付けてそのまま利用することができます。
※ シート名が「データ」ではない場合は、コードの4行目を書き換えてください。
（スクリプトエディタでprotectRangesを選択して▷実行します）
function protectRanges() {
// シートとその最終行番号を取得
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const sheet = spreadsheet.getSheetByName("データ"); // 保護するシートの名前にしてください
const lastRow = sheet.getLastRow();
// シートを保護する
for (let i = 2; i <= lastRow; i++) {
const editors = sheet.getRange(`A${i}`).getValue().split(","); // 複数アドレスが入力されていた場合はカンマで分割
const targetRow = sheet.getRange(`${i}:${i}`);
const protection = targetRow.protect();
protection.addEditors(editors);
}
}
期待どおり、行ごとに保護がかけられ、編集可能ユーザーも指定どおり設定されていました。
以上でサンプルの説明は終わりです。
ちなみに、シートの保護を一度全部解除したい場合は、下記のスクリプトを実行してください。
function removeAllRangeProtections() {
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const protections = spreadsheet.getProtections(SpreadsheetApp.ProtectionType.RANGE);
protections.forEach(protection => protection.remove());
}
コメント