Googleスプレッドシートを使って政府統計のデータをChart.jsで使えるJSONに変換する

シニアジョブで、サイトのコンテンツとして年代別就業状況の統計データを使うためにいろいろやったので流れをメモ。

データは政府の政府統計ポータルサイトe-Statのものを使いました。リンクは都道府県別のもの。
www.e-stat.go.jp

いろいろ探してエクセル形式でダウンロードしてGoogle スプレッドシートに読み込み、必要なものをコピペしていきます。

ちなみに私のスプレッドシートやエクセルのスキルは実務経験でいうと3週間ぐらいです。

コンテンツとしてデータがどんなものがいいかは、数字の羅列を見てもわかりません。
まずはグラフにするため、必要そうなデータをどんどん列に追加していきます。

行は年と都道府県にして、列にシニア世代を50代、60代、70歳以上という3つに分けていろいろ追加していきました。

f:id:uyamazak:20200818152939p:plain

唯一知っていると言っても過言ではない機能、ピボットテーブルを使って、データを整理してグラフにします。
知っているだけで使い方はわからないので、いい感じになるまで設定をいじりまくります。
いくつかできたら、どれがいいかなというのを周りに相談しながら決めました。
最終的には全就業者数における、各シニア世代の就業者数の割合のパーセントにしました。

f:id:uyamazak:20200818153519p:plain

グラフとデータが決まったのでChart.jsに移ります。
いい感じにグラフを表示してくれます。
www.chartjs.org

今回はVueのコンポーネントとして実装するため、ラッパーであるvue-chartjsをインストールしておきます。
vue-chartjs.org

Chart.jsで使うためには、スプレッドシートのデータがこんな感じになると嬉しいです。

  {
    "labels":[1980,1985,1990,1995,2000,2005,2010,2015],
    "datasets":[
      {"label":"50代","data":[16.69,18.53,19.08,19.95,22.89,23.11,20.34,20.07]},
      {"label":"60代","data":[7.33,7.61,8.87,10.18,10.17,11.53,14.5,15.51]},
      {"label":"70歳以上","data":[2.33,2.55,2.63,3.27,3.78,4.53,4.97,5.99]}
    ]
  }

なるべくスプレッドシートはデータだけに集中させたいので、backgroundColorなど色なんかはあとでVue側でlabelを見てくっつけることにします。

スプレッドシートのメニューの「ツール」→「スクリプトエディタ」を開いてGASを書きます。
Null合体演算子が使えない、アロー関数でインデントが狂う、StringにreplaceAllがないなど最近のJavaScriptとの微妙な違いと戦いながら雑に書いていき、こんな感じになりました。
都道府県別のも書きましたが複雑なので単純な年代別のものだけ。

function ageJson() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const rows = sheet.getSheetValues(1, 1, sheet.getLastRow(), sheet.getLastColumn());
  const labels = [];
  const dataset = [];
  const data50 = [];
  const data60 = [];
  const data70 = [];
  const dataAll = [];
  
  rows.forEach(
    function(cols, index) 
    {
      if (index === 0){
        return;
      }
      // 0が全国
      if (cols[1] !== 0) {
        return;
      }
      // 列変えたら数字変える
      year = cols[0];
      worker50 = cols[6];
      worker60 = cols[7];
      worker70 = cols[8];
      workerAll = cols[12];
      labels.push(year);
      data50.push(worker50);
      data60.push(worker60);
      data70.push(worker70);
      dataAll.push(workerAll);
    }
  );
  function toPercent(val, index) {
    return round(val / dataAll[index]*100, 2);
  }
  const result = {
    labels: labels,
    datasets: [
      {label: '50代', data: data50.map(toPercent)},
      {label: '60代', data: data60.map(toPercent)},
      {label: '70歳以上', data: data70.map(toPercent)}
    ]
  };
  
  Browser.msgBox(JSON.stringify(result));
}

// https://developer.mozilla.org/ja/docs/Web/JavaScript/Reference/Global_Objects/Math/round
function round(number, precision) {
  var shift = function (number, precision, reverseShift) {
    if (reverseShift) {
      precision = -precision;
    }  
    var numArray = ("" + number).split("e");
    return +(numArray[0] + "e" + (numArray[1] ? (+numArray[1] + precision) : precision));
  };
  return shift(Math.round(shift(number, precision, false)), precision, true);
}

他でも使えるテンプレートとしては下記のような感じ。

function myFunction() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const rows = sheet.getSheetValues(1, 1, sheet.getLastRow(), sheet.getLastColumn());
  const result = {}
  rows.forEach(
    function(cols, index) {
      // いろいろやってresultにいれてく
    }
  );
  Browser.msgBox(JSON.stringify(result));

出力結果を他のシートの書き出す方法なんかもあるんですが、今回は手軽にBrowser.msgBoxを使いました。

f:id:uyamazak:20200818155441p:plain