Google SpredSheetでAnalyticsの集計をするときのメモ

アドインで「Google Analytics」を追加。
アドインの機能でデータをシートに展開し、
その展開されたデータをSpredSheetの関数とか使って集計するのだ

ただ、Usersの集計をすると
Google Analytics直で1週間ベースで計算したのと
この方法で集計したのでは値が変わってくる。

どうやらGoogle Analytics直でUsersは、指定した期間内でのUsersになるようで、
1日単位でUsersを出したものを7日分合計したのとは違うみたい。

ネタ元

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サンプル　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;
}