【GAS】Google Apps Script 活用事例　シートを増やさず、GASでSUMIF関数を再現する方法

noteのコンテンツで、何が読まれているのかを把握するために、アクセス数をシートに書き出しています。その結果をEvernoteに定期的に飛ばしたいなぁと思って、SUMIF関数的な動きをスクリプトを書きました。まぁ、大したPV数ではないのですが.....。

書き出したままで、集計をしていない状態でした。既にシートが複数枚あるので、あまり増やしたくないと考えていました。

実行すると、こんな感じでEvernoteに送られます。

SlackAppでも、検索可能

一番最初に書いたコードはこちら。

for文が2重。文字列を入れる変数、カウントする変数などなど.....。冗長な感じがするなーと感じていました。

function generateArray_(values, column){
 return values.map(record => record[column]).filter(value => value);
}

//シートを増やさず、GASでSUMIF関数を再現する方法
function generateMessage_() {
 const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
 const sheet       = spreadsheet.getSheetByName('コンテンツ分析');
 const values      = sheet.getDataRange().getValues();
 values.shift();
 
 //ブログの記事名を取得し、重複を削除する
 const originalArray = generateArray_(values, 1);
 const uniqueArray   = Array.from(new Set(originalArray));
 uniqueArray.splice(5);

 console.log('重複なしの配列の要素数',uniqueArray.length);//22
 console.log('元々の配列', originalArray.length);//36

 let amount  = 0;   //合計のPV数
 let count   = 0;   //ヒット回数をカウント
 let message = '';  //Slack等で送るためのメッセージ

 for(let i = 0; i < uniqueArray.length; i++){
   let entry = uniqueArray[i];

   for(let j = 0; j < values.length; j++){
     const title = values[j][1];
     const pv    = values[j][2];

     if(entry === title){
       count  += 1;
       amount += pv;

       console.log(pv, values[j][1], count + '件');
     }//if
   }//for_j

   //タイトルから不要な文言を削除する
   entry = entry
   .replace('【GAS】Google Apps Script 活用事例　','')
   .replace(/【.*】/, '')
   .replace(/\s/, '')
   .replace(/（.*）/, '')
   .slice(0, 25);

   //メッセージを生成
   message += `${entry}　PV: ${amount}\n`;

   //初期化
   title  = '';
   amount = 0;
   count  = 0;

 }//for_i
 console.log(message);
 return message
}//end

上記のコードは、SUMIF関数を実際に組んで、どんなロジックかを確認しながら書きました。

UNIQUE関数

=UNIQUE('コンテンツ分析'!B2:B)

SUMIF関数

=SUMIF('コンテンツ分析'!$B$2:$C,A1,'コンテンツ分析'!$C$2:$C)

やはり、同じ事がやりたい人っているのね

reduceが使えるのではないか？と目星をつけていて、reduceを使ったスクリプトでリライトする事にしました。

SUMIF() in JavaScript?

エトーさん、はけたさん、ありがとうございます。

これ、mapとかreduceの練習に良さそうですね。 https://t.co/yFrya1GEjx pic.twitter.com/N18zESDjy3

— はけた＠経理のExcel仕事術 発売中 (@excelspeedup) March 13, 2021

|| はショートサーキットで、その前の値が false のときに次の値 (今回の場合は 0) をつかうのじゃよ。reduce の 初期値に { A: 0, B: 0 } 持たせれば || 0 なくても動きそうだけどね、しらんけどｗ

— etau (@etau0422) March 13, 2021

.....はけたさんのコードがめちゃくちゃ難しいと感じるのは気のせいでしょうか？一部、スプレッド構文で書かれているのだけは分かる。?の部分は、おそらく三項演算子かな。

【JavaScript入門】条件(三項)演算子の使い方と活用例まとめ！ | 侍エンジニアブログ【JavaScript入門】条件(三項)演算子の使い方と活用例まとめ！ - 侍エンジニアブログ

NaNになる場合を再現してみる

function myFunction(){
 const a = undefined;
 const b = 1;
 console.log( a + b );//NaN
}



function myFunction2(){
 const a = false;
 const b = 1;
 console.log( a + b );//1
}

undefined以外は、1になりました。うーむ、よく分からないですね。

参考にして、書けたスクリプトがこちら

/**
* メール経由で、Evernoteに送信する。　SlackAppで、　/find note で検索できる 
*
*/

function mailNotify(){
 const greet    = 'note　2021/02/22　からの合計PV数をお知らせします。\n\n';  
 const message  = generateMessage_();
 const to       = '***************@m.evernote.com';

 const date     = Utilities.formatDate(new Date(), 'JST', 'yyyy/MM/dd');
 const subject  = `note アクセス数　${date}`;
 const sheetUrl = 'https://docs.google.com/spreadsheets/d/*****************/edit';
 
 const body    = greet + message + '\n' + sheetUrl;
 GmailApp.sendEmail(to, subject, body);

}



/**
* 2次元配列を1次元配列にする 
*
* @param {object}  2次元配列
* @param {number}  列　数字で指定
* @return {object} 1次元配列
*
*/

function generateArray_(values, column){
 return values.map(record => record[column]).filter(value => value);
}




/**
* シートから重複を削除して、SUMIF関数的な動きで集計　Evernoteに送信するメッセージを作成
*
*/

function generateMessage() {
 const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
 const sheet       = spreadsheet.getSheetByName('コンテンツ分析');
 const values      = sheet.getDataRange().getValues();
 values.shift();
 
 //ブログの記事名を取得し、重複を削除する
 const entriesArray = generateArray_(values, 1);
 const pvArray      = generateArray_(values, 2);

 //console.log(entriesArray);
 //console.log(pvArray);


 //テキストから余計な文字列を抽出する
 const formatTitle = (text) => (text
 .replace('【GAS】Google Apps Script 活用事例　','')
 .replace(/【.*】/, '')
 .replace(/\s/, '')
 .replace(/（.*）/, '')
 .slice(0, 25));

 //オブジェクトに変換し、重複を省く　ショートサーキット
 const sumObject = pvArray.reduce((acc, current, i) => {
   
   const title = formatTitle(entriesArray[i]);
   acc[title]  = (acc[title] || 0) + current;

   console.log(`title: ${title}, pv: ${current}`);

   return acc
 }, {})//reduce

 console.log(sumObject);
 
 //2次元配列に変換
 const newValues = Object.entries(sumObject);

 //[['ブログタイトル', 'PV数']]　indexの1番目　PV数でSORT
 const compareFunc = (a, b) => (b[1] - a[1]);
 newValues.sort(compareFunc);

 console.log(`オブジェクトから変換後の配列：　${newValues}`);
 

 //2次元配列を文字列に変換する
 const message = newValues.map(array => `${array[0]} PV ${array[1]}\n`).join('');
 console.log(message);

 return message
}

2次元配列のソート

javascript　~ 二次元配列のソート~ - Qiita

ソートの挙動を確かめるためのミニテスト

function myFunction3456(){
 const values = [ [ '入力制限をかけて、綺麗なデータを集めよう', 910 ],
 [ 'スプレッドシートのキーワードからGoogle検索の', 174 ],
 [ '差し込み文書の作成を自動化する', 446 ],
 [ ' VLOOKUP関数的な処理をGASで書く方法', 318 ],
 [ 'noteのアクセスログを定期的に取得するスクリプト', 121 ] ];

 const compareFunc = (a, b) => (b[1] - a[1]);
 values.sort(compareFunc);
 console.log(values);

}

引数のa,bには、1次元配列が入り、それぞれのPV数を比較しながら、並び替える事が出来ます。

こんな記事も書いています。