Google Apps Script

スプレッドシートをJSON形式に出力する方法

はじめに

今回はGoogle Apps Scriptを使ってGoogleのスプレッドシートの情報をJsonに変換する方法を検証してみました

Google Apps Script とは

  • 通常GASと呼ばれる
  • Googleのクラウド上で起動されるスプレッドシート、文書、プレゼンテーション、フォームなどの追加機能を作れるプログラミング言語
  • JavaScriptプラットフォームで起動される

参考：Apps Script | Google Developers

スプレッドシート＞JSON出力

  • スプレッドシートの情報をJSONに変換し、新規シートへ出力
  • スプレッドシート
  • 出力Json形式
1
2
3
4
5
6
7
8
9
10
11
12
13
14
[
  {
    "名前値":{
      "部門CD値":"部門値"
      ,"チームCD値":"チーム値"
    }
    ,"番号":"番号値"
    ,"ID":"ID値"
    ,"ヨミ":"ヨミ値"
    ,"漢字":"漢字値"
    ,"メールアドレス":"メールアドレス_値"
  }
  ,{…},{…},…
]
  • GASイメージ
1
2
3
4
doGet(){
  convSheet(); // スプレッドシートをdata（配列）へ変換
  createNewSheet(); //dataをJson形式に変換し、新規シート出力
}
  • doGet()
1
2
3
4
5
6
7
8
9
function doGet() {
  //スプレットシート(account)の情報を取得
  var sheet = SpreadsheetApp.getActive().getSheetByName('account');
  //dataへ変換
  var dataArray = convSheet(sheet);
  
  //dataをJsonに変換し、新規シート出力
  creatNewSheet(dataArray);
}
  • convSheet() // スプレッドシートをdata（配列）へ変換
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
//data（配列）へ変換関数
function convSheet(sheet) {
  //内容開始行番号
  var rowIndex=2;
  //列開始行番号
  var colStartIndex = 1;
  //行番号
  var rowNum = 1;
  //最初行領域
  var firstRange = sheet.getRange(1, 1, 1, sheet.getLastColumn());
  //Logger.log('firstRange:'+JSON.stringify(firstRange));
  var firstRowValues = firstRange.getValues();
  //Logger.log('firstRowValues:'+JSON.stringify(firstRowValues));
  //項目情報取得
  var titleColumns = firstRowValues[0];
 
  var indexName = titleColumns.indexOf('氏名');
  var indexDepCd = titleColumns.indexOf('部門CD');
  var indexDepNm = titleColumns.indexOf('部門');
  var indexTeamCd = titleColumns.indexOf('チームCD');
  var indexTeamNm = titleColumns.indexOf('チーム');
  //Logger.log('indexName: '+indexName+'  '+'indexDepCd: '+indexDepCd+'  '+'indexDepNm: '+indexDepNm+'  '
  //           +'indexTeamCd: '+indexTeamCd+'  '+'indexTeamNm: '+indexTeamNm);
  var exceptIndexArray =[indexName,indexDepCd,indexDepNm,indexTeamCd,indexTeamNm];
  //Logger.log('exceptIndexArray: '+exceptIndexArray);
  
  //最後の行番号
  var lastRow = sheet.getLastRow();
  //Logger.log('lastRow:'+JSON.stringify(lastRow));
  //内容情報取得配列
  var rowValues = [];
  //内容取得
  for(rowIndex; rowIndex<=lastRow; rowIndex++) {
    var range = sheet.getRange(rowIndex, colStartIndex, rowNum, sheet.getLastColumn());
    var values = range.getValues();
    //内容情報にPUSH
    rowValues.push(values[0]);
  }
  //Logger.log('rowValues:'+JSON.stringify(rowValues));
  
  //Json形式配列
  var dataArray = [];
  for(var i=0; i<rowValues.length; i++) {
    //内容の行情報
    var line = rowValues[i];
    //JsonObject
    var json = new Object();
    //初期Json
    var firstJson = new Object();
    
    //初期Json作成
    //Dep
    firstJson[line[indexDepCd]]=line[indexDepNm];
    //Team
    firstJson[line[indexTeamCd]]=line[indexTeamNm];
    //dataArray[0]作成
    json[line[indexName]] = firstJson;
    
    //残りJson作成
    for(var j=0; j<titleColumns.length; j++) {      
      //最初Json除外
      if(0>exceptIndexArray.indexOf(j)){
        json[titleColumns[j]] = line[j];
      }
    }
    //data格納
    dataArray.push(json);
  }
  //「dataArray」ログ確認
  //Logger.log(dataArray);
  return dataArray;
}
  • convSheet() 実行結果 （実際は改行無し）
1
2
3
4
5
6
7
8
9
10
11
//Logger.log(dataArray);
[
   {ヨミ=ﾅﾏｴ ｲﾁ, 番号=1.0, 名前一={11=営業Aチーム, 1=営業}, 英字=Namae Ichi, ID=ID1, メールアドレス=ichi@test.co.jp},
   {ヨミ=ﾅﾏｴ ﾆ, 番号=2.0, 英字=Namae Ni, 名前二={11=営業Aチーム, 1=営業}, ID=ID2, メールアドレス=ni@test.co.jp},
   {ヨミ=ﾅﾏｴ ｻﾝ, 番号=3.0, 英字=Namae San, ID=ID3, 名前三={2=総務, 21=総務Aチーム}, メールアドレス=san@test.co.jp},
   {ヨミ=ﾅﾏｴ ｼ, 番号=4.0, 英字=Namae Shi, ID=ID4, メールアドレス=shi@test.co.jp, 名前四={2=総務, 21=総務Aチーム}},
   {名前五={3=IT, 31=ITAチーム}, ヨミ=ﾅﾏｴ ｺﾞ, 番号=5.0, 英字=Namae Go, ID=ID5, メールアドレス=go@test.co.jp},
   {ヨミ=ﾅﾏｴ ﾛｸ, 番号=6.0, 英字=Namae Roku, 名前六={3=IT, 31=ITAチーム}, ID=ID6, メールアドレス=roku@test.co.jp},
   {ヨミ=ﾅﾏｴ ﾅﾅ, 番号=7.0, 名前七={3=IT, 32=ITBチーム}, 英字=Namae Nana, ID=ID7, メールアドレス=nana@test.co.jp},
   {ヨミ=ﾅﾏｴ ﾊﾁ, 番号=8.0, 英字=Namae Hachi, ID=ID8, メールアドレス=hachi@test.co.jp, 名前八={33=ITCチーム, 3=IT}}
]
  • creatNewSheet() //data(配列)をJson形式に変換し、新規シート出力
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
//新規シート作成関数
function creatNewSheet(dataArray){
  //data(配列)よりJsonへ変換
  var json = JSON.stringify(dataArray);
 
  //出力
  //msgBox出力
  //Browser.msgBox(json);
  
  //新規シート名（日付）
  var date = new Date();
  var newSheetName = date.getFullYear() + "/" + (date.getMonth() + 1) + "/" + date.getDate();
  //新規シート初期化
  var newSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(newSheetName);
  
  //存在有無確認
  //存在しない場合
  if(!newSheet){
    //シート生成
    newSheet = SpreadsheetApp.getActiveSpreadsheet();
    newSheet.insertSheet(newSheetName);
    //データ入力
    newSheet.getRange("A1").setValue(json);
  //存在する場合
  }else{
    //データの書換え
    newSheet.getRange("A1").setValue(json);
  }
}

※ JSON.stringify()により、JavaScriptの値がJSON文字列に変換される

  • 出力完了

日付情報でシートを追加し、Json形式で出力された

JSON形式で出力結果（実際は改行無し）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
[
  {
    "名前一":
      {
        "1":"営業"
        ,"11":"営業Aチーム"
      }
    ,"番号":1
    ,"ID":"ID1"
    ,"ヨミ":"ﾅﾏｴ ｲﾁ"
    ,"英字":"Namae Ichi"
    ,"メールアドレス":"ichi@test.co.jp"
  }
  ,{
    "名前二":
      {
        "1":"営業"
        ,"11":"営業Aチーム"
      }
    ,"番号":2
    ,"ID":"ID2"
    ,"ヨミ":"ﾅﾏｴ ﾆ"
    ,"英字":"Namae Ni"
    ,"メールアドレス":"ni@test.co.jp"
  }
  ,{
    "名前三":
      {
        "2":"総務"
        ,"21":"総務Aチーム"
      }
    ,"番号":3
    ,"ID":"ID3"
    ,"ヨミ":"ﾅﾏｴ ｻﾝ"
    ,"英字":"Namae San"
    ,"メールアドレス":"san@test.co.jp"
  }
  ,{
    "名前四":
      {
        "2":"総務"
        ,"21":"総務Aチーム"
      }
    ,"番号":4
    ,"ID":"ID4"
    ,"ヨミ":"ﾅﾏｴ ｼ"
    ,"英字":"Namae Shi"
    ,"メールアドレス":"shi@test.co.jp"
  }
  ,{
    "名前五":
      {
        "3":"IT"
        ,"31":"ITAチーム"
      }
    ,"番号":5
    ,"ID":"ID5"
    ,"ヨミ":"ﾅﾏｴ ｺﾞ"
    ,"英字":"Namae Go"
    ,"メールアドレス":"go@test.co.jp"
  }
  ,{
    "名前六":
      {
        "3":"IT"
        ,"31":"ITAチーム"
      }
    ,"番号":6
    ,"ID":"ID6"
    ,"ヨミ":"ﾅﾏｴ ﾛｸ"
    ,"英字":"Namae Roku"
    ,"メールアドレス":"roku@test.co.jp"
  }
  ,{
    "名前七":
      {
        "3":"IT"
        ,"32":"ITBチーム"
      }
    ,"番号":7
    ,"ID":"ID7"
    ,"ヨミ":"ﾅﾏｴ ﾅﾅ"
    ,"英字":"Namae Nana"
    ,"メールアドレス":"nana@test.co.jp"
  }
  ,{
    "名前八":
      {
        "3":"IT"
        ,"33":"ITCチーム"
      }
    ,"番号":8
    ,"ID":"ID8"
    ,"ヨミ":"ﾅﾏｴ ﾊﾁ"
    ,"英字":"Namae Hachi"
    ,"メールアドレス":"hachi@test.co.jp"
  }
]

感想

  • JavaScriptの使用経験があり、違和感なく作成ができた。
  • 環境設定必要なく、作成できた。