スプレッドシート内で数式が含まれている複数のセルに対して一括で保護をかけたい

Question

前提・実現したいこと

数式が組まれている複数のセルに対して一括で保護をかけたい

発生している問題・エラーメッセージ

対象のセルが多く6分間を超過してしまい処理が途中でキャンセルされる

エラーメッセージ

該当のソースコード

// アクティブなシート内の数式セルを保護
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('数式セルの為保護');
     }
   }
 }
}

試したこと

他の方を参考に6分間超過する際の対処法を組み込みましたが上手くできず

補足情報（FW/ツールのバージョンなど）

ここにより詳細な情報を記載してください。

Answer 1

GASでセルに対する処理をするときには、「できる限りまとめて読み書きの処理をする」必要があります。
今回ですと、getFormulasで数式の読み込みはまとめて行なっていますが、protectの処理を1セルごとに行なっているため、処理時間がオーバーしてしまいます。

そこで、「列方向に連続している数式の範囲をまとめてprotectする」ことを考えます。たいていの場合、スプレッドシートでは列方向に同種のデータを並べますからね。

function myFunction() {
  const sheet = SpreadsheetApp.getActiveSheet();

  const data_range = sheet.getDataRange();
  const start_row = data_range.getRow();
  const start_col = data_range.getColumn();
  console.log(data_range.getA1Notation())
  const formulas = data_range.getFormulas();
  const rects = [];
  
  // 列方向に連続した数式があればまとめて範囲にする
  for (let x = 0; x < formulas[0].length; x++) {
    // 数式か否かの真偽値のインデックス付きリストを作り、真偽値の切り替わる部分だけ残す
    let packs = formulas.map((f, i) => ({value:!!f[x], index:i})).filter((t, i, arr) => (i === 0 || arr[i - 1].value != t.value));
    if (packs.slice(-1)[0].value) {
      // 末尾の要素がtrueならfalseの要素を追加
      packs.push({value:false, index:formulas.length})
    }
    if (packs[0].value === false) {
      // 先頭の要素がfalseなら削除する
      packs = packs.slice(1);
    }
    
    for (let i = 0; i < packs.length; i += 2) {
      rects.push({row:packs[i].index + start_row,
                  col:x + start_col,
                  num_rows:packs[i + 1].index - packs[i].index,
                  num_cols:1})
    }
    // console.log(`x: ${x}`)
    // console.log(rects)
  }

  // rectに対する行方向の統合(めんどくさいのでパス)

  for (let rect of rects) {
    const target_range = sheet.getRange(rect.row, rect.col, rect.num_rows, rect.num_cols);
    const protection = target_range.protect().setDescription("数式セルの保護");
    protection.removeEditors(protection.getEditors());
  }
}

これで各列ごとに、数式が連続している部分はひとつの範囲として保護されるので、時間オーバーにはなりにくいと思います。

Comments

[asutaro]

ご丁寧に回答頂きありがとうございます。
実は連続した数式ではないのですが、その場合の解決策などありますでしょうか・・・
数式という括りが難しいようであれば「空白以外のセルを保護」という策も考えております。

[Daregada]

この回答のコードは、「列ごとに数式が連続している範囲があればそれをひとつにまとめた範囲にする」もので、連続していなくてもそのまま使えます。

たとえば、B1:B50の範囲にいくつか数式でないセルが含まれていても、(B1が数式でなければ範囲に入れずに)「B2:B10を保護」、(B11が数式でなければ範囲に入れずに)「B12:B15を保護」、(B15:B20が数式でなければ範囲にいれずに)「B21:B50を保護」という動作をします。

[asutaro]

ありがとうございます！
保護無事掛かっており解決致しました。

Answer 2

なんか使う機会ありそうだったんで、私も備忘録的にコード書いたのを回答しますね。

方向性としてはDaregadaさんの回答されてる通りで、単体セルを一つ一つprotectする処理が遅い原因と思われるので、手動でやる時と同じように数式が入ってるセルが連続してるならセル範囲の記述に変換して、最後にprotectという手順が良いかと。

取得した配列をコネコネするのは、とんでもないデータ量じゃなければ遅延の原因にはなりにくいので、こんな感じのコードで試してみてはどうでしょうか。

function protectFormulaCells2(){
  const sheet = SpreadsheetApp.getActiveSheet();
  const range = sheet.getDataRange();　//A列、1行目がなにかしら入力されている想定
  const formulas = range.getFormulas();

  //数式が入ったセル位置情報を二次元配列化
  const formulasRangeList = formulas.flatMap((r,i) => r.map((f,j) => f? [i+1,j+1,1,1] : "")).filter(v=>v);

  //最初に列方向（縦に結合）

  let result =[];

  for(col=1; col<=formulas[0].length; col++){
    const targetColArr = formulasRangeList.filter(range => range[1]==col);
    if(!targetColArr.length) continue; //列に数式のあるセルが無い場合は次へ

    const array =targetColArr.reduce((acc,cv)=>{
      if(acc[0]+acc[2]==cv[0]){
        acc[2]+=1;
        return acc
      }else{
        result.push(acc);
        return cv
      };
    });
    result.push(array);
  };

  console.log(result);　//列方向結合の結果

  //さらに行方向（横に結合）
  result = result.reduce((acc,cv)=>{
    //開始行と行数(高さ)が同じで、隣同士のセル範囲をまとめていく
    const index = acc.findIndex(arr => arr[0]==cv[0] && arr[1]+arr[3]==cv[1] && arr[2]==cv[2]);
    if(index > -1){
      acc[index][3]+=1;
      return acc;
    }else{
      acc.push(cv);
      return acc;
    }
  },[]);

  console.log(result);　//さらに行方向に結合した結果


  result.forEach(range =>{
    sheet.getRange(...range).protect().setDescription('数式セルの為保護');
  });

}

Answer 3

事前に保護された範囲を取得し、保護されていない範囲をprotect()したらどうでしょうか？
保護された範囲は以下のコードのように取得できるようです。保護されている範囲がformulaだった場合にA1Notationなんかを取得して配列に保存し、indexOfやincludesを使い、ゴニョゴニョしてみたらどうでしょうか？

function myFunction() {
    const ss = SpreadsheetApp.getActive();
    const protections = ss.getProtections(SpreadsheetApp.ProtectionType.RANGE);
    for (var i = 0; i < protections.length; i++) {
        const protection = protections[i];
        const protection_range = protection.getRange();
        console.log(protection_range.getA1Notation());
        console.log(protection_range.getFormula());
    }
}