スプレッドシートのユーザー編集権限を変更する方法|GAS

こんにちは。ITライターのもり(@moripro3)です!GoogleAppsScriptの記事をお届けしています!

連載シリーズ「GASとスプレッドシートで作る勤怠管理ツール」では、勤怠管理ツールを題材としたGASの活用事例を紹介しています。

前回の記事では、これらの内容を紹介しました。

  • 勤怠管理ツールの全体像
  • GASで自動化する2つの機能
    • 【自動化その1】シート一括保護(月末締め)
    • 【自動化その2】翌月シートの一括作成

 

シリーズ第2回目のこの記事では、「【自動化その1】シート一括保護(月末締め)」の処理を作成します。

月末に多忙なバックオフィスのみなさん、わずらわしい手作業から解放されましょう!

『GASを使うとこんな自動化ができるんだ!』

というように、シリーズを通じて「GAS」のパワーを知ってもらい、バックオフィスで活躍するみなさまの業務効率化のお手伝いができれば幸いです。

スプレッドシートで作る勤怠管理ツールの構成

このシリーズで紹介する「勤怠管理ツール」は2種類のスプレッドシートで構成されています。

部下の勤怠をチェックする「管理者用」と、毎日の勤怠を入力する「社員用」です。

  1. タイムシート集計(管理者用)
  2. 勤怠表(社員用)

①と②のスプレッドシートは関数でリンクしています。

※連載シリーズの最後で実物のファイルを公開しますので、関数の仕組みはそちらをご覧ください

勤怠管理ツールの全体像

各社員は、毎月1日~末日の間、勤怠表に勤務時間を入力し、管理者は、翌月初に「締め」の作業をします。

各社員が入力値を変更できないように、管理者以外の編集権限を削除してシートを保護します。

これを手作業でやると、社員ひとりずつのスプレッドシートを開き、対象のシートを選択して、編集権限を削除し、シート保護する・・・を繰り返さなければなりません。この一連の処理を「ポチッ」と片づけられるよう、GASで一括処理します。

gas-kintai2-3

それではこの処理をGASで作成していきましょう!

複数のスプレッドシートの「指定名のシート」の権限設定をする

管理者用のタイムシートには、各社員の勤怠表とリンクするよう、各社員の勤怠表スプレッドシートのURLを入力しています。これらのURLのスプレッドシートに対して、一括処理をします。

一連の処理を1~4に細分化してコードを書いていきます。

  • 【処理1】締め月を指定する(保護対象のシート指定)
  • 【処理2】勤怠表の件数処理を繰り返す(ループ処理)
  • 【処理3】スプレッドシートの中の保護対象のシートを取得する
  • 【処理4】自分以外の編集権限を削除する

 

重要なメソッド(操作)を水色の枠で記述してるので、「メソッド名」と「役割」を意識しながら読んでみてくださいね。

この水色ボックスで重要な「メソッド」の役割を紹介しています

【処理1】締め月を指定する(保護対象のシート指定)

勤怠表の各月のシート名はYYYYMMの数字4桁形式です。(例:201909)

どの月を締めるか、ユーザーがスクリプト実行時に「締め月(保護対象シート)」を指定できるようにします。

GASのinputBoxメソッド

inputBoxメソッドを使用して、ユーザーのブラウザに、テキスト入力のダイアログボックスを表示します。

ユーザーの入力値(=保護対象のシート名)を変数YYYYMMに格納します。

var YYYYMM = Browser.inputBox('締め月の指定','締め月を半角数字6桁(YYYYMM)で入力してください', Browser.Buttons.OK_CANCEL);
  
//キャンセルされた場合は処理終了
if (YYYYMM == 'cancel'){
  return;
}
  
//入力値の簡易チェック
if (YYYYMM.length != 6){
  Browser.msgBox('締め月は半角数字6桁(YYYYMM)で入力してください');
  return;
}

inputBoxメソッドのダイアログボックスは、ユーザーに自由にパラメータを指定させたい場合に便利です!

ただし、入力値の整合性チェックが必要です。ここでは、簡易チェックとして、桁数の確認のみしています(※この部分は記事の本題ではないので簡易処理としています)

【処理2】勤怠表の件数、処理を繰り返す(ループ処理)

管理者用の集計表は、各社員の勤怠表のスプレッドシートとリンクしています。集計表のセルに、対象のスプレッドシートのURLが入力されているので、このURLを利用します。

管理者用スプレッドシートの、B列3行目~10行目に入力されているURLの分、処理を繰り返します。

勤怠表とリンクする

for文を使用した繰り返し処理です。セルが空白の場合は、continue文で処理をスキップします。

var sheet = SpreadsheetApp.getActiveSheet();
  
//B列3行目~10行目まで繰り返す(空白の場合、スキップ)
for (var row = 3; row <= 10; row++) {
    
  //各社員の勤怠表スプレッドシートのURLを取得
  var targetURL = sheet.getRange(row, 2).getValue();
    
  if (targetURL == '') { //セルが空白の場合はスキップ
    continue;
  }

  // 後続の処理を記述

}

 

【処理3】スプレッドシートの中の保護対象のシートを取得する

以下2つの情報を使用して「保護対象のシート」を取得します。

  1. ユーザーがinputBoxのダイアログで入力した値(締め月:YYYYMM)
  2. 管理者用のスプレッドシートに入力されているURL

 

スプレッドシートのURLはこのような形式です。赤色のx部分がIDなので、URL全体からこの部分のみを抽出します。

  • URL = https://docs.google.com/spreadsheets/d/xxxxxxxxxxxxxxx(←ID)

ID部分のみを取得したら、SpreadsheetAppのopenByIdメソッドの引数にIDを指定して対象のスプレッドシート(勤怠表)を開きます。

getSheetByNameメソッドの引数に変数YYYYMMを指定して、保護対象のシートを取得します。

var strgoogle = 'https://docs.google.com/spreadsheets/d/'; //URLの固定部
var targetId = targetURL.replace(strgoogle, ''); //スプレッドシートのID部を取得
    
//ID指定でスプレッドシートを開き、指定のシートを取得
var targetSS = SpreadsheetApp.openById(targetId);
var targetSheet = targetSS.getSheetByName(YYYYMM); //★これが保護対象のシート★

変数targetSheetに保護対象のシートオブジェクトを格納しました。このtargetSheetに対して、シート権限設定の処理をしていきます。

【処理4】自分以外のユーザーの編集権限を削除する

この処理が、今回のメインテーマです。対象シートの「自分以外のユーザーの編集権限」を削除します。

処理を4ステップに細分化して紹介します。

【処理4-1】シートの保護設定のオブジェクトを作成する

まずは「シートの保護設定のオブジェクト」を作成します。

シートに対して直接処理をするのではなく、「保護設定のオブジェクト」というものを作成して、そのオブジェクトに対して処理をします(これは、そういう仕組みなんだな~と思っていただければ大丈夫です)

Sheetクラスのprotectメソッドを使用します(Google公式リファレンスはこちら

Sheetオブジェクト.protect()

シートの保護設定のオブジェクトを作成します。シートが既に保護されている場合は、既存の保護設定を表すオブジェクトを返します。

「protect」という名前のメソッドなので、このメソッドでシートを保護する・・・と思ってしまいそうですが、protectメソッドは、「保護設定のオブジェクトを作成する」だけのメソッドであり、保護設定をするメソッドではありません。

protectメソッドで作成したオブジェクトに対して、権限追加・権限削除などの他メソッドを実行することによって、権限が変更されます。

protectメソッドを実行しても、見た目ではシートに鍵マークが付きますが、シートに対する権限は変わりません。protectメソッドは「保護設定のオブジェクト」を作成するだけだからです。

コードはこの1行です。目的のシートが変数targetSheetに格納されているので、変数targetSheet(Sheetオブジェクト)に対して、protectメソッドを実行します。

var protection = targetSheet.protect();

protectメソッドの戻り値を、変数protectionに格納しています。

変数protectionが「シートの保護設定を表すオブジェクト」です。このオブジェクトに対して、追加・削除などのメソッドを実行することで、シートの権限が変更されます。

 

【処理4-2】自分のアカウントに編集権限を付与する

protectメソッドの戻り値であるProtectionクラスには、権限追加・権限削除など多数のメソッドが用意されています(Google公式リファレンスはこちら

シートの編集権限を持たない人がスクリプトを実行するとエラーになるので、事前に、自身の編集権限を付与する処理です(通常は編集権限を持つ人が操作すると思うので、この処理はおまけです)

addEditorメソッドで自分のアカウントに編集権限を付与します。

Protectionオブジェクト.addEditor(ユーザー)

保護された範囲またはシートの編集者のリストにユーザーを追加します。

コードがこちらです。

var me = Session.getEffectiveUser(); //自分のアカウントを取得
protection.addEditor(me);

 

【処理4-3】現在の編集権限者を削除する

今回の目的である「自分以外のユーザーの編集権限を削除する」処理です。

まずは、getEditorsメソッドで「現在の編集者一覧」を取得します。

Protectionオブジェクト.getEditors()

保護された範囲またはシートの編集者のリストを取得します(ユーザーが編集権限を持っていない場合は取得不可)

 

getEditorsメソッドの返り値は、下記のような「メールアドレスの配列」です。

(例)[user1@dummy.co.jp, user2@dummy.co.jp, user3@dummy.co.jp]

つぎに、removeEditorsメソッドで、指定のユーザーを削除します。

 

Protectionオブジェクト.removeEditors(メールアドレスの配列)

保護された範囲またはシートの編集者のリストから、配列に列挙されたメールアドレスのユーザーを削除します。

ただし、下記ユーザーについては削除できません。

  • スプレッドシートのオーナー
  • スクリプトの実行ユーザー

また、ドメインに編集権限が付与されている場合、そのドメインに属するユーザーは引き続き編集可能です。

 

以上の2つのメソッドを使用して、共同編集者を削除するコードがこちらです。現在の編集者を一覧を配列で取得し、それらを削除します。

var editors = protection.getEditors(); //現在の編集者一覧の取得
protection.removeEditors(editors); //自分以外の編集者を削除

 

【処理4-4】ドメインの編集権限を削除する

G Suite導入企業では「ドメイン」というグループ単位で権限付与が可能です。前述のremoveEditorsメソッドでは「ドメインの削除」ができないため、ドメインの編集権限を削除する処理を加えます。

まずは、canDomainEditメソッドで、ドメインに編集権限が付与されているかを判定します。

 

Protectionオブジェクト.canDomainEdit()

「スプレッドシートのオーナーが属するドメイン」に編集権限が付与されているか判定するメソッドです。

返り値はBoolean型です。

  • true = ドメインに編集権限あり
  • false = ドメインに編集権限なし

ドメインに編集権限が付与されている場合は、setDomainEditメソッドで、ドメインの編集権限を削除します。

Protectionオブジェクト.setDomainEdit(true/false)

「スプレッドシートのオーナーが属するドメイン」の編集権限を設定するメソッドです。引数にBoolean型の引数を指定します。

  • ドメインに編集権限を付与する場合 = true
  • ドメインの編集権限を削除する場合 = false

コードがこちらです。if文で条件判定をして、ドメインに編集権限が付与されている場合のみ、ドメインの編集権限を削除します。

//ドメインに編集権限が付いてる場合は削除
if (protection.canDomainEdit()) {
  protection.setDomainEdit(false);
}

 

編集権限削除の処理【まとめ】

「自分以外のユーザーの編集権限を削除する」処理はちょっと複雑ですね。特にG Suite導入企業では「ドメイン」の考慮が必要です。

ポイントはこの2メソッドです。

  • ドメインを削除する → setDomainEdit(false)
  • ユーザーを削除する → removeEditors(ユーザーの配列)

GASのシート編集権限の設定

 

ユーザー「user1@abc.co」を削除しても、ドメイン「abc.co」に編集権限が付与されている場合、 「abc.co」ドメインに属する「user1@abc.co」は、引き続きシート編集が可能である

・ドメイン「abc.co」を削除しても、ユーザー「user1@abc.co」に編集権限が付与されている場合、 「user1@abc.co」は、引き続きシート編集が可能である

よって、ドメインに編集権限が付与されている場合は、ユーザー・ドメインの両方の削除処理が必要です。

【スクリプトまとめ】自分以外のユーザーの編集権限を削除してシート保護

この記事で作成した処理のまとめです。ぜひご活用ください!

function ProtectSheet() {
  
  var YYYYMM = Browser.inputBox('締め月の指定','締め月を半角数字6桁(YYYYMM)で入力してください', Browser.Buttons.OK_CANCEL);
  
  //キャンセルされた場合は処理終了
  if (YYYYMM == 'cancel'){
    return;
  }
  
  //入力値の簡易チェック
  if (YYYYMM.length != 6){
    Browser.msgBox('締め月は半角数字6桁(YYYYMM)で入力してください');
    return;
  }
  
  var sheet = SpreadsheetApp.getActiveSheet();
  
  //B列3行目~10行目まで繰り返す
  for (var row = 3; row <= 10; row++) {
    
    //各社員の勤怠表スプレッドシートのURLを取得
    var targetURL = sheet.getRange(row, 2).getValue();
    
    if (targetURL == '') { //セルが空白の場合はスキップ
      continue;
    }
    
    var strgoogle = 'https://docs.google.com/spreadsheets/d/'; //URLの固定部
    var targetId = targetURL.replace(strgoogle, ''); //スプレッドシートのID部を取得
    
    //ID指定でスプレッドシートを開き、指定のシートを取得
    var targetSS = SpreadsheetApp.openById(targetId);
    var targetSheet = targetSS.getSheetByName(YYYYMM); //★これが保護対象のシート★
    
    //シートの保護設定のオブジェクトを作成
    var protection = targetSheet.protect();
    
    //自分のアカウントに編集権限を付与する ※スクリプト実行時点で編集権限がない場合に必要
    var me = Session.getEffectiveUser();
    protection.addEditor(me);
    
    //共同編集者の削除
    var editors = protection.getEditors(); //現在の編集者一覧の取得
    protection.removeEditors(editors); //自分以外の編集者を削除
    
    //ドメインに編集権限が付いてる場合は削除
    if (protection.canDomainEdit()) {
      protection.setDomainEdit(false);
    }
    
  }
}

 

処理結果です。編集可能者が自分だけになっているのが確認できますね。

GASでシートの権限を削除する

 

まとめ:GASとスプレッドシートで作る勤怠管理ツール

GASで「自分以外のユーザーの編集権限を削除してシートを保護する方法」を紹介しました。

G Suite導入企業で、ドメイン単位で権限設定をしている場合は、ドメインの権限削除も考慮する必要があります。

次回は、「【自動化その2】翌月シートの一括作成」の処理を紹介します。

【連載目次:GASとスプレッドシートで作る勤怠管理ツール】

  1. GASでスプレッドシートのシフト表を自動化するには?
  2. スプレッドシートのユーザー編集権限を変更する方法
  3. GASでスプレッドシート活用術。今月日付から翌月日付を作る
  4. GASでスプレッドシートをコピー、シート内の一番左に表示する方法
  5. GASで複数のスプレッドシートに対して同じ処理を繰り返す(※サンプルファイル公開あり)
請求書の受け取りはsweeepで自動化

【AI請求書処理】従来の請求書OCRでは対応できない非定型帳票や

自動会計仕訳も、sweeepなら対応可能!最短で即日導入、

面倒な設定不要。手軽に導入して請求業務を効率化。