【GAS】Google Apps Script 活用事例 数式の参照先を調べるスクリプト
上記は、仕事終わりに手抜きで作ったサンプルシートで、至極シンプルですが、実務で数式が複雑で分からんッ！！と感じたり、表が横に長過ぎてどこを参照しているか分かりづらいという事がよくあります。
=($G21+$H21)-($J21+$M21)
「........そもそも、G21って何だっけ？えっ....それから、JとMを足すの？」上記のような謎な数式を見ながら、これ一体何やっているの？.........って思ったので、そんな困ったを解決すべくスクリプトを書きました。
=($G21+$H21)-($J21+$M21)
書類選考なし : 1名 , 書類合格 : 14名 , 1次参加 : 13名, 1次辞退 : 2名
上記のように見出し項目と各セルの値を付き合わせる、頭の中でやっている作業を一瞬で表示させてくれる機能があれば、いいなと思いました。
スクリプトはこんな感じ
こちらは、1パターンのみのスクリプトです。後述しますが、一致したパターンによって処理を振り分けるスクリプトを後日書きました！！
//セルに入った数式を解析する。
function getFormulaFromActiveCell() {
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const sheet = spreadsheet.getSheetByName('採用イベント参加者');
const activeCell = sheet.getActiveCell();
const cellDetail = {
formula: activeCell.getFormula(),
value: activeCell.getValue(),
cell: activeCell.getA1Notation(),
row: activeCell.getRow(),
column: activeCell.getColumn()
}
console.log(cellDetail);
//activeCellの数式の取得結果、sheet、見出し行の位置
const result = analyzeFormula_(cellDetail.formula, sheet, 1);
console.log(`数式の解析結果： ${result}`);
//string, title, width, height
showMeNotice_(result, '数式の解析結果', 450, 75);
}
function analyzeFormula_(formula, sheet, headerRow){
//getFormula()で取得された文字列から、シート名だけを抽出する
//'=COUNTIFS(\'採用数値集計表\'!$G$3:$G,$D21,\'採用数値集計表\'!$H$3:$H,"エンジニアコース",\'採用数値集計表\'!$AN$3:$AN,"合格")';
const pattern = formula.match(/[A-Z]{1,2}[0-9]{1,3}/g); //アルファベットと数字だけを抽出する。
console.log(`matchの結果：${pattern}`);
if(pattern !== null){
//=($G21+$H21)-($J21+$M21)
const result = pattern.map(cell => {
const alphabet = cell.replace(/[0-9]{1,3}/,'');
const headerValue = sheet.getRange(`${alphabet}${headerRow}`).getValue();
const cellValue = sheet.getRange(cell).getValue() + '名 ';
const detail = headerValue + ' : ' + cellValue;
return detail
});//map
const string = formula + '<br>' +result.join();
console.log(string);
return string
}else{
return formula;
}
}
function showMeNotice_(string, title, width, height){
const ui = SpreadsheetApp.getUi();
const html = HtmlService.createHtmlOutput(string)
.setWidth(width)
.setHeight(height);
ui.showModelessDialog(html, title);
}数式結果から、シート名やRangeを取り出すのは結構大変
//=($G21+$H21)-($J21+$M21)
const pattern = formula.match(/[A-Z]{1,2}[0-9]{1,3}/g);こういう数式の場合は、大文字のA〜Zまでの文字で2桁かつ、0〜9までの数値で1〜3桁をgフラグで抽出します。これは割と簡単なのですが、他のシートから読み込んでいる場合は、難易度が、結構上がる印象です。
function myFunction() {
const string = '=COUNTIFS(\'採用数値集計表\'!$G$3:$G,$D21,\'採用数値集計表\'!$H$3:$H,"エンジニアコース",\'採用数値集計表\'!$AN$3:$AN,"合格")';
const pattern = string.match(/'.*,/g);
console.log(`matchの結果：${pattern}`);
if(pattern !== null){
const result = pattern.map(word => word.replace(/\'|!.|:.|[0-9]{1,2,3}|[A-Z]{1,2}|".*"|\$.*/g, '')//replace
);//map
console.log(result);
const sheetName = result.join();
console.log(sheetName);
return sheetName
}
}ログ
もはや執念.....。シート名を取得できたら、次は範囲を取得しないといけない。これが結構難しい......
シート名とレンジを抽出したい。
function test(){
//getFormulaで取得した値
const str = '=COUNTIFS(\'採用数値集計表\'!$G$3:$G,$D21,\'採用数値集計表\'!$H$3:$H,"エンジニアコース",\'採用数値集計表\'!$AN$3:$AN,"合格")';
const array = str.replace(/=.*\(/, '').match(/採用数値集計表|[A-Z]{1,2},/g);
console.log(`抽出した関数 : ${str}`);
console.log(`matchの結果：${array}`);//matchの結果：採用数値集計表,G,,採用数値集計表,H,,採用数値集計表,AN,
const result = array.map(element => element.replace(',', ''));
console.log(`result: ${result}`); //result: 採用数値集計表,G,採用数値集計表,H,採用数値集計表,AN
}2021/03/05 追記
とりあえず、COUNTIFのパターンであれば、シート名とアルファベットを抽出する事が出来ました。
これで、パターン1がnullではなかったら、パターン2がnullではなかったら....みたいな感じで、条件分岐を増やしていきます。
実際のスクリプト
ifとelse if内の処理が異なるため、上手く共通の処理にまとめて切り分ける事が出来なかったのですが、まとめられれば、もう少しスッキリするかもしれません。
/**
* 数値管理表の数式を表示し、どこを参照しているかハイライトする。
* この関数を実行する
*/
function getFormulaFromActiveCell() {
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const sheet = spreadsheet.getSheetByName('数値管理表');
const activeCell = sheet.getActiveCell();
const cellDetail = {
formula: activeCell.getFormula(),
value: activeCell.getValue(),
cell: activeCell.getA1Notation(),
row: activeCell.getRow(),
column: activeCell.getColumn()
}
console.log(cellDetail);
analyzeFormula_(cellDetail, sheet);
}
/**
*
*@param {object} アクティブセルの情報
*@param {number} シートオブジェクト
*@return {number} 数式の参照先
*
*/
function analyzeFormula_(cellDetail, sheet){
const pattern1 = cellDetail.formula.replace(/=.*\(/, '').match(/採用数値管理表|[A-Z]{1,2},/g);
const pattern2 = cellDetail.formula.match(/[A-Z]{1,2}[0-9]{1,3}/g);
console.log(`pattern1に一致 参照先シートあり：${pattern1}`);
console.log(`pattern2に一致 参照先シート無し：${pattern2}`);
/**
* パターン1は、COUNTIF
* パターン2は、引き算
*
* =COUNTIFS(\'採用数値管理表\'!$G$3:$G,$D21,.....
* ['採用数値管理表', 'G', '採用数値集計表', 'H', '採用数値集計表', 'AN']
* 上記のようにシート名とアルファベットのみを抽出して、参照先の見出し項目を取得する
*
* =($D9+$E9)-($G9+$J9)
* 上記の数式からD9,E9,G9,J9のように数式からアルファベットと数字だけを抽出する。
*/
let newString = `<p>${cellDetail.formula}</p>`;
//参照先シートの見出し項目を取得
//['採用数値管理表', 'G', '採用数値集計表', 'H', '採用数値集計表', 'AN']
if(pattern1 !== null){
const sheetName = pattern1[0];
const refernceSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
//配列の名からシート名の要素を削除して、アルファベットのみの配列にする
const filteredArray = pattern1.filter(word => !word.includes(sheetName));
console.log(`filterメソッドで処理後の配列： ${filteredArray}`);
//[ 'A21', '1DAY', '合格' ]のように参照先を抽出する
const referenceFormula = getReferenceCell_(cellDetail.formula);
for(let i = 0; i < filteredArray.length; i++){
if(filteredArray[i] !== ''){
//,の文字列を削除する
//2行目にある見出し項目を取得
const headerCell = filteredArray[i].replace(',', '') + 2;
const header = refernceSheet.getRange(headerCell).getValue();
let targetCell = referenceFormula[i];
//ダイアログで表示させる文言の生成
//流入元： イベント 選考タイプ：1DAYのように表示させる
if(targetCell.includes('1DAY')||targetCell.includes('合格')||targetCell.includes('辞退')){
targetCell;
}
else{
targetCell = sheet.getRange(referenceFormula[i]).getValue();
}
//HTMLを生成
newString += `<p>${header}： ${targetCell}</p>`;
console.log(headerCell);
}
}//for_i
console.log(newString);
showHtmlDialog_('数式の参照先を表示',newString, 450, 250);
}//if
else if(pattern2 !== null){
//数式からD9,E9,G9,J9のように数式からアルファベットと数字だけを抽出する。
for(let i = 0; i < pattern2.length; i++){
const targetCell = pattern2[i];
const headerCell = targetCell.replace(/[0-9]{1,3}/, '') + 2;
console.log(`着色するセル： ${targetCell}`);
console.log(`見出し行のセル： ${headerCell}`);
//薄い紫色で塗り潰す
const targetRange = sheet.getRange(targetCell);
targetRange.setBackground('#f3eeff');
//採用数値管理表の見出し項目を取得
const header = sheet.getRange(headerCell).getValue();
const number = targetRange.getValue();
newString += `<p>${header} ： ${number} 名 </p>`;
}//for
SpreadsheetApp.flush();
showHtmlDialog_('数式の参照先を表示',newString, 300, 250);
console.log(newString);
//25秒待つ
Utilities.sleep(25 * 1000);
//着色したセルを元に戻す
showAlert_(pattern2, sheet);
}//else if
return
}
/**
*
*@param {object} matchメソッドで一致したセルの情報の配列
*@param {number} シートオブジェクト
*@return {number} セル塗り潰しを消して無色に戻す
*
*/
function showAlert_(array, sheet){
const ui = SpreadsheetApp.getUi();
const response = ui.alert('先ほど着色したセルの色を元に戻します。', ui.ButtonSet.YES_NO_CANCEL);
if (response === ui.Button.YES) {
//色を元に戻す
console.log('はいが押されました');
array.map(cell => sheet.getRange(cell).setBackground(null));
}else {
ui.alert('いいえが押されました');
}
}
//COUNTIF用の参照先を求める
//=COUNTIFS('採用数値管理表'!$G$3:$G,$A21,'採用数値管理表'!$H$3:$H,"1DAY*",'採用数値管理表'!$AH$3:$AH,"合格")
//[ 'A21', '1DAY', '合格' ]のように参照先を抽出する
function getReferenceCell_(formula) {
const formatedWords = formula.replace(/\$|"|\*|\)/g, '');
const result = formatedWords.split(',');
console.log(result);
//indexが、奇数（2で割り切れない時）のみを配列に加える
const newArray = result.filter(element => {
const index = result.indexOf(element);
if(index % 2 !== 0){
console.log(element);
return element
}
});
console.log(newArray);
return newArray
}
/**
* 0.03456 → 3%
* 数値管理表の小数点の表記を指定したものに修正する。
* オマケ
*/
function cutDecimalPoint() {
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const sheet = spreadsheet.getSheetByName('数値管理表');
//選択している範囲の表示形式を、0.00%から、0%に直す
sheet.getActiveRange()
.setNumberFormat('0%');
}補足：こんなスクリプトも書きました。
簡単にいうと、数値算出の内訳を調べるスクリプトです。良かったら、見ていってください。
これ結構、使えるッ！！ってなりました。
僕のニーズとしては、複雑な条件式のCOUNTIFS関数が、どこを参照しているかをパッと視認できるものが作れないかなと思って作りました。
こんな過去記事を書いています。
この記事が気に入ったらサポートをしてみませんか？