はじめに
今回は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の使用経験があり、違和感なく作成ができた。
- 環境設定必要なく、作成できた。