GAS＞スプレッドシートで編集可能なセルを設定

SHARE

生徒一人ひとりに個別スプレッドシートを配布して共有しています。このファイルに生徒が入力したデータを吸い上げて、必要な形に加工して先生方と共有するということをしています。使っているのは進路希望調査ですが、時期によって編集できるセルに制限をかけて利用しています。それを GAS で変更する際の自分用メモです。

Googleドライブには生徒一人ひとりと共有しているフォルダがあり、そこにクラス別にして個別スプレッドシートを置いてあります。このファイルを１つずつ開いて、以下のような流れで編集できるセルを設定しなおします。調べたノウハウを後で使えるように冗長的にまとめておきます。

目次 非表示

スプレッドシート編集者から生徒をクリア

スプレッドシートの共有・保護を再設定したいときに、古い設定が残っていると面倒です。そこで処理を始める前に、いったん共有していた生徒を外します。この際、ファイルのオーナーは残るし、共有ドライブ自体にアクセスできる先生方は引き続きアクセス可能です。

let ss = SpreadsheetApp.open(file);
let nowEditors = ss.getEditors();
for (member of nowEditors) {
  ss.removeEditor(member);
}
Java
  • 1行目・・・スプレッドシートを開く → ss へ（開き方は色々）
  • 2行目・・・ss.getEditors() で編集権限をもつアカウントを取得 → nowEditors へ
  • 3行目・・・for 文ここから
    　　　　　　nowEditors（配列だから of）から１つ取り出して member に入れる
  • 4行目・・・ss.removeEditor(member) で member の編集権限を削除
  • 5行目・・・for 文ここまで、配列の個数だけ繰り返し

これで共有ドライブ自体にアクセスできる先生方以外はアクセスできなくなります。

特定シートに設定された保護をクリア

続いて特定シート（hoge）に設定された保護をクリアします。新しい保護を設定したときに古いものが残らないように全て削除します。

let ss = SpreadsheetApp.open(file);
let ts = ss.getSheetByName("hoge");
let protectRanges = ts.getProtections(SpreadsheetApp.ProtectionType.RANGE);
for (range of protectRanges) {
  range.remove();
}
Java
  • 1行目・・・スプレッドシートを開く → ss へ（開き方は色々）
  • 2行目・・・ss.getSheetByName(“hoge”) で hoge という名前のシートを開く → ts
  • 3行目・・・ts.getProtections() で保護されたRANGEを取得する → protectRanges
  • 4行目・・・for 文ここから
    　　　　　　protectRanges（配列だから of） から１つ取り出して range に入れる
  • 5行目・・・range.remove() で range に設定された保護情報を削除
  • 6行目・・・for 文ここまで、配列の個数だけ繰り返し

これで特定シート（hoge）に設定された保護がクリアされ、全く保護されていない状態になりました。

スプレッドシート編集者に該当する生徒を追加

最初に生徒からアクセス権を除いたので、このファイルに該当する生徒１名を編集者に追加します。

let ss = SpreadsheetApp.open(file);
ss.addEditor("acc@mail.add.ress");
Java
  • 1行目・・・スプレッドシートを開く → ss へ（開き方は色々）
  • 2行目・・・ss.addEditor(“メールアドレス”) で生徒に編集権限を追加

この流れでいいのか分かりませんが、別の生徒がアクセスできるようになってしまっては困るので、念の為やっています。これで該当する生徒がアクセスできるようになりました。

特定シートに新たに保護をセットする

最後に特定シート（hoge）に新しい保護情報をセットします。まっさらな状態になっているのでトラブルは起こらないはずです。

let ss = SpreadsheetApp.open(file);
let ts = ss.getSheetByName("hoge");
let protection = ts.protect();
let nowEditors = protection.getEditors();
protection.removeEditors(nowEditors);
protection.setUnprotectedRanges([
  ts.getRange("D3"),
  ts.getRange("C73:D73"),
]);
Java
  • 1行目・・・スプレッドシートを開く → ss へ（開き方は色々）
  • 2行目・・・ss.getSheetByName(“hoge”) で hoge という名前のシートを開く → ts
  • 3行目・・・開いたシート全体を ts.protect() で保護、保護情報を取得 → protection
  • 4行目・・・protection.getEditors() で編集権限をもつ人のリストを取得 → nowEditors
  • 5行目・・・protection.removeEditors(nowEditors) で編集権限を全て削除（オーナーは残る）
    　　　　　　これでシートは完全に保護される。以下、編集可能な範囲を指定
  • 6行目・・・protection.setUnprotectedRanges() で特定のセルを除く範囲を指定
  • 7行目・・・ts.getRange(“D3”) で D3 セルは編集可能に
  • 8行目・・・ts.getRange(“C73:D73”) で C73:D73 範囲のセルを編集可能に
  • 9行目・・・protection.setUnprotectedRanges() に配列を渡して設定完了

7行目〜8行目で特定のセルを制限から外します。これを必要な数だけ取得し、配列に取り込みます。この辺、何かかっこいいやり方がありそうですが、素人は実現できたから良しとします。

後で使いまわせるよう目的ごとにまとめてみました。新たな情報が手に入ったら修正していきます。どなたかの参考になれば幸いです。

  • 2024.10.08…初版公開

コメントを残す

メールアドレスが公開されることはありません。 ※ が付いている欄は必須項目です

前の記事

Hello world!