複数条件ならSUMIFS
Google SpredSheetでAnalyticsの集計をするときのメモ
アドインで「Google Analytics」を追加。
アドインの機能でデータをシートに展開し、
その展開されたデータをSpredSheetの関数とか使って集計するのだ
ただ、Usersの集計をすると
Google Analytics直で1週間ベースで計算したのと
この方法で集計したのでは値が変わってくる。
どうやらGoogle Analytics直でUsersは、指定した期間内でのUsersになるようで、
1日単位でUsersを出したものを7日分合計したのとは違うみたい。
ネタ元
- https://www.mk-design.co.jp/blog/2018/05/marketing-google-analytic-spreadsheet-report-automation-02
- https://tonari-it.com/gas-analytics-chatwork/
- http://bnbrd.net/automation-by-google-analytics-and-spreadsheet/
- https://tonari-it.com/google-analytics-spreadsheet/
- https://qiita.com/Yamotty/items/41a513fe9592ee0cd11a
- https://digitalidentity.co.jp/blog/analytics/google-analytics/gareporting-with-add-on.html
- https://webtan.impress.co.jp/e/2015/12/15/21593
GASでシートに行を挿入する
これだと9行目の下に挿入
function InsertRowAfter() { var objSpreadsheet = SpreadsheetApp.getActiveSpreadsheet(); var objSheet = objSpreadsheet.getActiveSheet(); objSheet.insertRowAfter(9); }
こちらは9行目の上に挿入
objSheet.insertRowBefore(9);
Google Apps Scriptによる配列処理のためのループ
いろいろある。
- 一般的なfor loop
- for (var i = 0; i < 10; i++) {array[i]}
- for in
- for (var i in array) {array[i]}
- while
- while (i < 10) {array[i++]}
- forEach
- array.forEach(function(e){e})
- map, filter
- array.map(function(e){e})
- array.filter(function(e){e})
- Comprehension: GASはJavaScript 1.7を使っていることから、下記のような配列の内包表記を使用することができます。
- [e for each (e in array)]
速度にも違いがあるそう。
GASで列内で特定の値に一致する行番号を取得する
列の内容を配列に取り込んで、
その配列内を検索して場所を調べるという方法になります。
function input_value(){ var key = "え"; var col = "A"; var sh = SpreadsheetApp.getActiveSheet(); var row = get_row(key, col, sh); sh.getRange(row, 2).setValue("ここ") } function get_row(key, col, sh){ var array = get_array(sh, col); var row = array.indexOf(key) + 1; return row; } function get_array(sh, col) { var last_row = sh.getLastRow(); var range = sh.getRange(col + "1:" + col + last_row) var values = range.getValues(); var array = []; for(var i = 0; i < values.length; i++){ array.push(values[i][0]); } return array; }
GASで任意の文字列を含んでいるかどうかを調べる
function myFunction() { var str = ‘柴犬’ //変数strに代入した文字列に「犬」が含まれている場合 if ( str.match(/犬/)) { Browser.msgBox(‘犬です’); } else { Browser.msgBox(‘犬ではありません’); } }
という感じ。
GASでSleep
Utilities.sleep(1000);
GASで繰り返し正規表現でマッチさせる
var myRe = /ab*/g; var str = "abbcdefabh"; var myArray; while ((myArray = myRe.exec(str)) !== null) { var msg = "Found " + myArray[0] + ". "; msg += "Next match starts at " + myRe.lastIndex; console.log(msg); }
GASサンプル Y!Fから上場日一覧取得
スクレイピングは禁止されてるので実際には使わないでね。
function myFunction() { var book = SpreadsheetApp.getActiveSpreadsheet(); var sheetData = book.getSheetByName("シート1"); var colID = 1; var colURL = 2; var colContributeCount_code = 1; var colContributeCount_date = 2; var rowStartData = 2 var rowEndData = sheetData.getDataRange().getLastRow() var start_i = 0; var page = 70; while(1){ try { var i = start_i; var url = 'https://info.finance.yahoo.co.jp/ranking/?kd=41&tm=d&vl=a&mk=1&p=' + page.toString(10); var response = UrlFetchApp.fetch(url); var html = response.getContentText('UTF-8'); var myReCode = /<a href="https:\/\/stocks.finance.yahoo.co.jp\/stocks\/detail\/\?code\=(\d{4})\./g; var myReDate = /<td class="txtcenter bgyellow01">(\d{4}\/\d{1,2}\/\d{1,2}?)<\/td>/g; var myArray; var arr_code = []; var arr_date = []; var i_max = 0; while ((myArray = myReDate.exec(html)) !== null) { arr_date[i] = myArray[1]; i++; } i = start_i; while ((myArray = myReCode.exec(html)) !== null) { arr_code[i] = myArray[1]; i++; } // 1ページ目で大体終わりなので抜けるようにしとく // データ更新するときなどはここコメントアウトすると良いでしょう break; page++; start_i = i; Utilities.sleep(1000); }catch (ex) { break; } } i_max = i; // コードでA列を検索。なかったら追加 i = 0; while (i < i_max) { var col = get_array(sheetData,"A"); var i_code = parseInt(arr_code[i],10); var row = get_row(i_code,"A",sheetData); if( row == 0 ){ sheetData.insertRowBefore(1); sheetData.getRange(1, colContributeCount_code).setValue(arr_code[i]); sheetData.getRange(1, colContributeCount_date).setValue(arr_date[i]); }else{ //既存のデータが見つかったらその時点で終了とする break; } i++; } Browser.msgBox("確認", "終了!", Browser.Buttons.OK); } function get_row(key, col, sh){ var array = get_array(sh, col); var row = array.indexOf(key) + 1; return row; } function get_array(sh, col) { var last_row = sh.getLastRow(); var range = sh.getRange(col + "1:" + col + last_row) var values = range.getValues(); var array = []; for(var i = 0; i < values.length; i++){ array.push(values[i][0]); } return array; }