【GAS】Google Apps Script 活用事例 「触るな、 キケン」を実現。IMPORTRANGE関数で表示している範囲を保護して塗り潰す。
お願いだから、触らないでくれる？
本当に便利で、重宝しているのが、IMPORTRANGE関数やQUERY関数そしてARRAYFORMULA関数。しかし、関数で擬似的に表示されている値を編集したために、エラーになってしまうことが度々あります。1枚のシートがエラーになったがために、他のシートも連鎖的に.......悲劇でしかないですね。
共有するメンバーのレベルは様々です。触って欲しくない範囲にブロックしたり、色を塗ったりする事がボタン一つで出来るようになったら、最高だなと思ったのが出発点です。それまで範囲の保護とかを手動でやっていました。......こういう需要は、意外とあるんじゃないかなと思います。
なるほど、条件付き書式にISFORMULA関数
おぉ、そんなのあるんだ！！初めて知った！！
IMPORTRANGEの数式から範囲だけを正規表現で抽出
IMPORTRANGEに限らず、別のシートなどから範囲を読み込んでいる場合は、"シート名!A:Z" みたいな感じになっているかと思います。! から始まって " で終わる文字だけを抜き出して、不要な部分をreplaceで消します。
var getFormula = sheet.getRange('A1').getFormula();
var reg = /!.*?"/;
var targetRange = getFormula.match(reg)[0].replace('!','').replace('"','');今回は、Importrange関数を使う想定で書いているため、getFormulaメソッドを使っています。全範囲とかだったら、getFormulas 複数形のメソッドがあるので、そちらを使った方が汎用性が高いかなと思います。
完成形のスクリプト(A1に関数入っていないと失敗します....。)
/*数式から範囲を抽出する*/
function getFormula() {
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var sheet = spreadsheet.getSheetByName('IMPORT');
var getFormula = sheet.getRange('A1').getFormula();
var reg = /!.*?"/;
var targetRange = getFormula.match(reg)[0].replace('!','').replace('"','');
Logger.log(targetRange);
return targetRange;
}
function setColor() {
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var sheet = spreadsheet.getSheetByName('IMPORT');
var activeSheet = SpreadsheetApp.getActiveSheet();
/*別の関数の呼び出し*/
var targetRange = getFormula();
var range = sheet.getRange(targetRange);
var setColor = range.setBackground('#d9d9d9');
/*読み込んだ範囲を保護*/
var protection = range.protect();
protection.setDescription('自分のみ編集可');
/*条件つき書式を追加*/
var rule = SpreadsheetApp.newConditionalFormatRule()
.setRanges([range])
.whenFormulaSatisfied('=ISFORMULA(A1)')
.setBackground('#FF0000')
.build();
var rules = sheet.getConditionalFormatRules();
rules.push(rule);
sheet.setConditionalFormatRules(rules);
}
上記のスクリプトの場合、関数がA1セルに無い状態だと失敗してしまいます。スクリプトの至らない部分は運用でカバーしてください。IMPORTRANGE使う時は、必ずA1セルに入れる。そして、このスクリプトを実行するみたいな運用です。
B1とかC5とかいろんなセルに入っている場合、結構、技巧を尽くさないと難しい事が判明。
関数が入ったセルを判別し、アラートを出すスクリプト
function findFormula() {
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var sheet = spreadsheet.getSheetByName('IMPORT');
var allDetaRange = sheet.getDataRange();
/*条件付き書式を追加する。*/
var rule = SpreadsheetApp.newConditionalFormatRule()
.setRanges([allDetaRange])
.whenFormulaSatisfied('=ISFORMULA(A1)')
.setBackground('#FF0000')
.build();
var rules = sheet.getConditionalFormatRules();
rules.push(rule);
sheet.setConditionalFormatRules(rules);
/*2次元配列でセルに入った関数を見つける。*/
var getFormulas = sheet.getDataRange().getFormulas();
var detectedCell = [];
var protectedRange = '';
Logger.log(getFormulas);
/*セルの横断的な検索*/
for(var i = 0; i < getFormulas.length; i++){
for(var j = 0; j < getFormulas[0].length; j++){
/*正規表現に合致していたら、（例）"シート!A:Z" の中から、A:Zを抽出 !A:Z,でも抽出可能*/
var reg = /!.*?"/;
var reg2 = /!.*?,/;
if(getFormulas[i][j].match(reg) !== null || getFormulas[i][j].match(reg2) !== null){
Logger.log('i = %s 、j = %s ',i,j);
/*セルに入った関数を見つける*/
var row = i + 1;
var column = j + 1;
var targetFormula = sheet.getRange(row, column).getFormula();
/*正規表現で抽出後、余分な文字列を削除*/
var results = targetFormula.match(reg)[0].replace('!','').replace('"','').replace(',','');
Logger.log(sheet.getRange(row, column).getA1Notation());
Logger.log(sheet.getRange(row, column).getFormula());
Logger.log(targetFormula.match(reg));
Logger.log(results);
/*masgBoxで表示する*/
var formulaCell = ' ' + sheet.getRange(row, column).getA1Notation();
detectedCell.push(formulaCell);
protectedRange += '保護されている範囲は、' + results + ' です。\\n';
}//if
}//for_j
}//for_i
var message = '関数が含まれているセルは、' + detectedCell + ' です。\\n\\n' + protectedRange;
Browser.msgBox(message);
}//endBrowser.msgBoxでどこのセルに関数が入っているみたいにアラートが出るようにしました。for文のjまで回した事で、縦と横の検索が出来るようになっています。範囲の保護と着色しなかったのには理由があります。
正規表現で上手く抽出することに成功したものの、塗り潰しと範囲の保護をした結果、B3を起点に、A:J分の保護というのが出来なかったためです。で、結局範囲の保護がほぼ全範囲になってしまい、これだったら初めからシートの保護で全範囲を編集を禁止するのと、ほぼ変わらないことになってしまったため、これはこれで一旦終了する事にしました。
この記事が気に入ったらサポートをしてみませんか？