GASでスプレッドシートの数式セルを保護するスクリプト

2021年5月4日 11:09

ノンプロ研でGAS勉強中です。

先日まで受講していたGAS初級講座の卒業制作で作ったスクリプトです。

エクセルにはジャンプという素敵機能があるのですが、スプレッドシートには無いのがちょいちょい不便です（無いですよね？代替方法あったら教えてほしいです）。

ジャンプを代替するわけではないのですが、誰かにスプレッドシートを共有するときに数式セルをいじられないように保護するスクリプトを作ってみました。

// アクティブなシート内の数式セルを保護
function protectActiveSheetFormulas() {
 const sheet = SpreadsheetApp.getActiveSheet();
 console.log(sheet.getName());
 protectFormulaCells(sheet);
}

// 全てのシートの数式セルを保護
function protecAllSheetsFormulas() {
 const ss = SpreadsheetApp.getActiveSpreadsheet();
 const sheets = ss.getSheets();
 for (const sheet of sheets) {
   console.log(sheet.getName());
   protectFormulaCells(sheet);
 }
}

// 数式が含まれるセルを取得して保護していく関数
function protectFormulaCells(sheet) {

 //全データ範囲を取得
 const allRange = sheet.getDataRange();

 //全データ範囲の数式を取得
 const formulas = allRange.getFormulas();

 //データ配列数＝行数を取得
 const numFormulas = formulas.length;
 // console.log(numFormulas);

 //データ範囲の列数を取得
 const lastColumn = sheet.getLastColumn();
 // console.log(lastColumn);

 // 数式を含むセルの範囲を一つずつ取得し保護
 for (let i = 0; i < numFormulas; i++) {
   for (let j = 0; j < lastColumn; j++) {
     const cell = formulas[i][j];
     if (cell.length > 0) {
       // console.log(`${i + 1}:${j + 1}`);
       console.log(sheet.getRange(i + 1, j + 1).getA1Notation());
       sheet.getRange(i + 1, j + 1).protect().setDescription('数式セルの為保護');
     }
   }
 }
}


// アクティブなシート内の保護範囲を解除
function removeProtections() {
 // https://excel-ubara.com/apps_script1/GAS034.html

 const sheet = SpreadsheetApp.getActiveSheet();
 const protections = sheet.getProtections(SpreadsheetApp.ProtectionType.RANGE);
 for (let i = 0; i < protections.length; i++) {
   let protection = protections[i];
   if (protection.canEdit()) {
     protection.remove();
   }
 }
}

構成としては、4（3?）部になっていて。

1. 特定の（アクティブな）シートを対象とする関数
2. スプレッドシート内の全てのシートを対象とする関数
3. 数式の入ったセルを一つづつ取得して保護していく関数
4. 全ての保護を解除する関数（コピペ）

です。

1と2を分けた理由は、このスクリプトの実行が時間がかかる（セルを一つづつ保護する部分）ため、数式の数が多い複雑なスプレッドシートの場合、GASの時間制限に引っかかる可能性があったためです。

無事目的は達成できましたが、もう少しスマートに機能を実現できるようになりたいです。

アクティブなシート内の保護範囲を解除する関数は、下記URLより参考にさせていただきました。

GASはノンプロ研で勉強させてもらっています。

この記事が気に入ったらサポートをしてみませんか？