{"text_format":"1","workbook":"xlsx","n_sheets":7,"sheets":["README","BLOCKS 実行","BigQuery 接続情報","result1","result2","グラフ用データ","グラフ"],"not_rendered":{"charts":1,"images":2,"macros":1,"external_links":0,"defined_names":15,"comments":2,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"README","index":0,"n_rows":65,"n_cols":4,"rows_kept":65,"cut":false,"hidden":false,"merged":[[57,1,57,3],[58,1,58,3],[63,1,63,3],[64,1,64,3],[54,2,54,3],[60,1,60,3],[61,1,61,3],[55,2,55,3],[44,1,44,3],[53,2,53,3],[46,1,46,3],[47,1,47,3],[48,1,48,3],[50,1,50,3],[51,1,51,3],[33,1,33,3],[35,1,35,3],[36,1,36,3],[40,1,40,3],[43,1,43,3],[25,1,25,3],[1,1,1,3],[2,1,2,3],[4,1,4,3],[6,1,6,3],[7,1,7,3],[8,1,8,3],[11,1,11,3],[12,1,12,3],[14,1,14,3],[15,1,15,3]]}
[]
[null,"1. はじめに"]
[null,"このスプレッドシートは、MAGELLAN BLOCKS ウェブサイトの活用例「Excel から MAGELLAN BLOCKS のフローを実行するサンプル」で使用している Microsoft Excel のスプレッドシートです。"]
[]
[null,"このスプレッドシートは、次のことを実現するサンプルとして提供しています。"]
[]
[null,"・スプレッドシートから MAGELLAN BLOCKS のフローを実行"]
[null,"・BigQuery からデータを取得"]
[null,"・BigQuery から取得したデータをグラフで可視化"]
[]
[]
[null,"2. 前提"]
[null,"このスプレッドシートの利用にあたっては、所定の MAGELLAN BLOCKS のフローを準備しておきます。"]
[]
[null,"2.1 MAGELLAN BLOCKS のフローを準備"]
[null,"次のフローを準備します。"]
[]
[]
[]
[null,"ブロック名","ブロックの種類"]
[null,"気象データ分析","フローの開始（基本）"]
[null,"雨の日のデータを取得","クエリーの実行（BigQuery）"]
[null,"月ごとの集計","クエリーの実行（BigQuery）"]
[null,"フローの終了","フローの終了（基本）"]
[]
[null,"「雨の日のデータを取得」ブロックと「月ごとの集計」ブロックには、次の内容を設定してください。"]
[]
[null,"プロパティ","雨の日のデータを取得ブロック","月ごとの集計ブロック"]
[null,"クエリー","SELECT\r\n  month,\r\n  day,\r\n  max_temperature,\r\n  total_precipitation,\r\nFROM\r\n  [publicdata:samples.gsod]\r\nWHERE\r\n  station_number = ${station_number}\r\n  AND year = ${year}\r\n  AND month IN (${month}) \r\n  AND total_precipitation > 0.0\r\nORDER BY\r\n  month,\r\n  day","SELECT\r\n  month,\r\n  AVG(max_temperature) AS max_temperature,\r\n  SUM(total_precipitation) AS total_precipitation,\r\n  COUNT(*) AS precipitation_days\r\nFROM\r\n  [${project}:${dataset}.${table1}]\r\nGROUP BY\r\n  month\r\nORDER BY\r\n  month"]
[null,"結果格納先のデータセット","${dataset}","${dataset}"]
[null,"結果格納先のテーブル","${table1}","${table2}"]
[null,"空でないテーブルが存在したとき","上書き","上書き"]
[]
[null,"この他に、GCP サービスアカウントのプロパティも設定してください。"]
[]
[null,"2.2 フローに ID を付与"]
[null,"作成したフローに次の ID を付与します。ID の付与は、フローリスト機能で行います。"]
[]
[null,"ID: weather_data_analysis"]
[]
[null,"ID は自由に設定して構いません。ただし、上記以外の ID を設定した場合は、「BLOCKS 実行」シートの D5 セルの内容を設定した ID で書き換えてください。"]
[]
[]
[null,"3. このスプレッドシートの使い方"]
[null,"このスプレッドシートは、次の手順で使用します。"]
[]
[null,"1. 「BLOCKS 実行」シートで MAGELLAN BLOCKS のフローを実行"]
[null,"2. 「BigQuery 接続情報」シートで Excel 用 BigQuery コネクタを使い BigQuery のデータを取得"]
[null,"3. 「グラフ」シートで BigQuery のデータを可視化"]
[]
[null,"3.1 「BLOCKS 実行」シート"]
[null,"「ボード URL」、「API トークン」および「プロジェクト ID」を記入し、「フローを実行する」ボタンをクリックし、BLOCKS のフローを実行します。"]
[]
[null,"ボード URL: ","実行するフローがあるボード URL のホスト名部分（**********blocksboard********.magellanic-clouds.net）を指定します。ボード URL は、ウェブブラウザーでボード編集画面を開き、ウェブブラウザーのアドレスバーで確認します。"]
[null,"API トークン: ","実行するフローがあるボードで生成した API トークンを指定します。"]
[null,"プロジェクトID:","Google Cloud Platform のプロジェクト ID を指定します。"]
[]
[null,"3.2 「BigQuery 接続情報」シート"]
[null,"フローの実行が成功終了した後で、Excel 用 BigQuery コネクタを使い BigQuery からデータを取得します。"]
[]
[null,"「BigQuery 接続情報」シートで、シート内の解説に従い、コネクタ Key を記入し、[データ] メニューの [すべて更新] をクリックします。"]
[null,"（BigQuery のデータが「result1」シートと「result2」シートに反映されます。また、グラフ表示用の「グラフ用データ」シートも更新されます。）"]
[]
[null,"3.3 「グラフ」シート"]
[null,"「グラフ」シートに切り替えると、取得したデータを元にグラフが表示されます。"]

{"sheet":"BLOCKS 実行","index":1,"n_rows":17,"n_cols":4,"rows_kept":17,"cut":false,"hidden":false,"merged":[[2,1,2,2]]}
[]
[null,"フローの実行に必要な BLOCKS 情報"]
[null,"項目名",null,"値"]
[null,"ボード URL"]
[null,"フロー ID",null,"weather_data_analysis"]
[null,"API トークン"]
[]
[]
[null,"フローに渡す BigQuery に関する情報"]
[null,"項目名","フローからの参照方法","値"]
[null,"プロジェクト ID","${project}"]
[null,"結果格納先のデータセット","${dataset}","excel"]
[null,"結果格納先のテーブル_1","${table1}","result1"]
[null,"結果格納先のテーブル_2","${table2}","result2"]
[null,"都市","${station_number}","722430"]
[null,"年","${year}","2009"]
[null,"月","${month}","1,2,3,4,5,6,7,8,9,10,11,12"]

{"sheet":"BigQuery 接続情報","index":2,"n_rows":20,"n_cols":3,"rows_kept":20,"cut":false,"hidden":false,"merged":[[21,1,21,2],[17,1,17,2],[19,1,19,2],[8,1,8,2],[10,1,10,2],[16,1,16,2],[12,1,12,2],[9,1,9,2],[11,1,11,2],[15,1,15,2],[14,1,14,2]]}
[]
[null,"BigQuery 接続情報"]
[null,"項目名","値"]
[null,"result1 用クエリ",{"$f":"\"SELECT * FROM [\" & project & \":\" & dataset_id_1 & \".\" & table_id_1_1 & \"]\"","v":"SELECT * FROM [:excel.result1]"}]
[null,"result2 用クエリ",{"$f":"\"SELECT * FROM [\" & project & \":\" & dataset_id_1 & \".\" & table_id_1_2 & \"]\"","v":"SELECT * FROM [:excel.result2]"}]
[null,"プロジェクト ID",{"$f":"project","v":0}]
[null,"コネクタ Key"]
[]
[null,"BigQuery からのデータ取得は、Google が提供する「Excel 用 BigQuery コネクタ」を使用しています。"]
[]
[null,"このシートでは、「Excel 用 BigQuery コネクタ」に必要な情報を管理しています。"]
[]
[null,"コネクタ Key には、Excel 用 BigQuery コネクタのページの先頭に表示される Key を入力してください。もし、ページ先頭に Key が表示されない場合は、ページ下部にある [Create Key] ボタンをクリックして Key を入手してください。"]
[]
[null,"なお、コネクタ Key を貼り付けるときは、書式なしテキストで貼り付けてください。"]
[]
[null,"Excel 用 BigQuery コネクタのページ:"]
[null,"https://bigquery-connector.appspot.com/"]
[]
[null,"コネクタ Key 設定後、メニューから [データ] をクリックし、接続の [すべて更新] をクリックします。"]

{"sheet":"result1","index":3,"n_rows":118,"n_cols":5,"rows_kept":118,"cut":false,"hidden":false,"merged":[]}
[]
[null,{"$f":"table_id_1_1 & \" テーブルのデータ\"","v":"result1 テーブルのデータ"}]
[null,"月","日","1日(降雨)の最高気温","1日(降雨)の降水量"]
[null,"month","day","max_temperature","total_precipitation"]
[null,1,5,41,9.9999997764825804E-3]
[null,1,6,41,5.9999998658895402E-2]
[null,1,7,35.099998474121001,0.34000000357627802]
[null,1,28,37.400001525878899,0.10000000149011599]
[null,2,1,51.799999237060497,2.9999999329447701E-2]
[null,2,9,53.099998474121001,7.0000000298023196E-2]
[null,2,10,64.900001525878906,5.0000000745057997E-2]
[null,2,11,52,0.479999989271163]
[null,2,13,45,9.9999997764825804E-3]
[null,2,14,54,0.51999998092651301]
[null,2,15,55.400001525878899,0.239999994635581]
[null,2,17,51.099998474121001,9.9999997764825804E-3]
[null,2,18,68,3.9999999105930301E-2]
[null,2,19,50,9.9999997764825804E-3]
[null,2,21,53.099998474121001,9.0000003576278603E-2]
[null,3,11,59,1.9999999552965102E-2]
[null,3,12,46.400001525878899,0.81000000238418501]
[null,3,13,42.799999237060497,0.64999997615814198]
[null,3,14,42.799999237060497,0.479999989271163]
[null,3,15,42.099998474121001,0.46000000834464999]
[null,3,24,66.900001525878906,9.9999997764825804E-3]
[null,3,25,57.200000762939403,0.15999999642372101]
[null,3,26,57.200000762939403,0.70999997854232699]
[null,3,27,57.900001525878899,0.37000000476837103]
[null,3,28,41,0.38999998569488498]
[null,3,30,42.099998474121001,9.9999997764825804E-3]
[null,3,31,48,1.9999999552965102E-2]
[null,4,2,48,5.0000000745057997E-2]
[null,4,12,59,1.00999999046325]
[null,4,17,57,1.8099999427795399]
[null,4,18,62.599998474121001,3.0699999332427899]
[null,4,25,66,0.230000004172325]
[null,4,26,68,2.9999999329447701E-2]
[null,4,28,64,3.6800000667571999]
[null,4,29,66.900001525878906,0.479999989271163]
[null,5,1,72,1.9999999552965102E-2]
[null,5,11,73,9.9999997764825804E-3]
[null,5,12,71.099998474121094,2.9999999329447701E-2]
[null,5,17,66.199996948242102,0.15999999642372101]
[null,5,22,63,7.0000000298023196E-2]
[null,5,24,68,0.11999999731779]
[null,6,25,79,0.270000010728836]
[null,7,7,73,0.46000000834464999]
[null,7,8,80.099998474121094,1.0199999809265099]
[null,7,18,73.400001525878906,0.20999999344348899]
[null,7,19,71.099998474121094,5.9999998658895402E-2]
[null,7,20,71.099998474121094,9.9999997764825804E-3]
[null,7,23,73.400001525878906,0.43999999761581399]
[null,7,24,75,0.17000000178813901]
[null,7,27,78.099998474121094,0.109999999403953]
[null,7,30,81,1.9999999552965102E-2]
[null,7,31,77,0.34000000357627802]
[null,8,9,75.900001525878906,0.18999999761581399]
[null,8,12,75.900001525878906,0.61000001430511397]
[null,8,13,75,3.9999999105930301E-2]
[null,8,18,75.199996948242102,1.9999999552965102E-2]
[null,8,21,73,0.69999998807907104]
[null,8,22,73,2.9999999329447701E-2]
[null,8,28,73.900001525878906,1.9999999552965102E-2]
[null,8,29,73,0.5]
[null,9,4,69.800003051757798,0.79000002145767201]
[null,9,9,72,0.25]
[null,9,10,75,1.9999999552965102E-2]
[null,9,11,75,0.20999999344348899]
[null,9,12,73.400001525878906,9.9999997764825804E-3]
[null,9,13,73.400001525878906,9.9999997764825804E-3]
[null,9,14,71.599998474121094,0.81999999284744196]
[null,9,22,68,7.0000000298023196E-2]
[null,9,23,66.199996948242102,1.53999996185302]
[null,9,25,64.900001525878906,7.0000000298023196E-2]
[null,9,28,72,0.89999997615814198]
[null,9,29,71.599998474121094,9.9999997764825804E-3]
[null,10,2,64,0.62000000476837103]
[null,10,4,69.099998474121094,0.40999999642372098]
[null,10,5,70,0.66000002622604304]
[null,10,6,73.900001525878906,9.9999997764825804E-3]
[null,10,8,78.099998474121094,7.9999998211860601E-2]
[null,10,10,57,0.93999999761581399]
[null,10,12,61,5.9999998658895402E-2]
[null,10,13,64,1.2300000190734801]
[null,10,14,73.900001525878906,0.41999998688697798]
[null,10,15,78.099998474121094,5.9999998658895402E-2]
[null,10,21,63,0.10000000149011599]
[null,10,22,66,2.1500000953674299]
[null,10,23,50,1.1000000238418499]
[null,10,26,54,2.42000007629394]
[null,10,27,55,9.9999997764825804E-3]
[null,10,29,75.199996948242102,5.0000000745057997E-2]
[null,10,30,54,2.9400000572204501]
[null,11,9,64.400001525878906,7.0000000298023196E-2]
[null,11,15,59,9.9999997764825804E-3]
[null,11,16,55,0.239999994635581]
[null,11,20,48.900001525878899,7.9999998211860601E-2]
[null,11,21,51.799999237060497,0.62999999523162797]
[null,11,22,48,0.140000000596046]
[null,11,24,46,5.9999998658895402E-2]
[null,11,30,50,0.37999999523162797]
[null,12,1,48.200000762939403,5.0000000745057997E-2]
[null,12,2,46,1.37999999523162]
[null,12,4,33.099998474121001,0.21999999880790699]
[null,12,6,27.100000381469702,0.10000000149011599]
[null,12,7,55.400001525878899,0.34999999403953502]
[null,12,8,55,0.17000000178813901]
[null,12,12,44.099998474121001,1.1799999475479099]
[null,12,14,48.900001525878899,3.9999999105930301E-2]
[null,12,15,50,0.44999998807907099]
[null,12,17,44.599998474121001,0.10000000149011599]
[null,12,18,41,0.44999998807907099]
[null,12,22,48.200000762939403,1.9999999552965102E-2]
[null,12,23,64.400001525878906,1.9999999552965102E-2]
[null,12,24,41,0.15999999642372101]
[null,12,29,33.099998474121001,0.129999995231628]
[null,12,30,33.099998474121001,0.37999999523162797]
[null,12,31,43,0.46999999880790699]

{"sheet":"result2","index":4,"n_rows":16,"n_cols":5,"rows_kept":16,"cut":false,"hidden":false,"merged":[]}
[]
[null,{"$f":"table_id_1_2 & \" テーブルのデータ\"","v":"result2 テーブルのデータ"}]
[null,"月","月間(降雨)の最高気温の平均","月間(降雨)の降水量の合計","月間(降雨)の日数"]
[null,"month","max_temperature","total_precipitation","precipitation_days"]
[null,1,38.625,0.51000000350177199,4]
[null,2,54.399999791925602,1.54999996721744,11]
[null,3,50.283333460489899,4.0899999402463401,12]
[null,4,61.4375,10.3599999267607,8]
[null,5,68.883332570393804,0.40999999269843102,6]
[null,6,79,0.270000010728836,1]
[null,7,75.319999694824205,2.8399999830871798,10]
[null,8,74.362500190734806,2.1099999975413,8]
[null,9,71.0750001271565,4.6999999452382299,12]
[null,10,65.076470318962507,13.2600002810359,17]
[null,11,52.887500286102203,1.6099999826401401,8]
[null,12,44.482352873858197,5.6699999012053004,17]

{"sheet":"グラフ用データ","index":5,"n_rows":16,"n_cols":6,"rows_kept":16,"cut":false,"hidden":false,"merged":[]}
[]
[null,"集計結果をグラフ用に加工（単位を変換）"]
[null,"月","月間(降雨)の最高気温の平均(華氏°F)","月間(降雨)の降水量の合計(inch)","月間(降雨)の最高気温の平均(摂氏°C)","月間(降雨)の降水量の合計(mm)"]
[null,"month","max_temperature(°F)","total_precipitation(inch)","max_temperature(℃)","total_precipitation(mm)"]
[null,1,{"$f":"VLOOKUP(B5,result2!$B$5:$E$18,2,FALSE)","v":38.625},{"$f":"VLOOKUP(B5,result2!$B$5:$E$18,3,FALSE)","v":0.51000000350177199},{"$f":"CONVERT(C5,\"F\",\"C\")","v":3.6805555555555554},{"$f":"CONVERT(D5,\"in\",\"mm\")","v":12.954000088945008}]
[null,2,{"$f":"VLOOKUP(B6,result2!$B$5:$E$18,2,FALSE)","v":54.399999791925602},{"$f":"VLOOKUP(B6,result2!$B$5:$E$18,3,FALSE)","v":1.54999996721744},12.444444328847556,39.369999167322973]
[null,3,{"$f":"VLOOKUP(B7,result2!$B$5:$E$18,2,FALSE)","v":50.283333460489899},{"$f":"VLOOKUP(B7,result2!$B$5:$E$18,3,FALSE)","v":4.0899999402463401},10.157407478049944,103.88599848225704]
[null,4,{"$f":"VLOOKUP(B8,result2!$B$5:$E$18,2,FALSE)","v":61.4375},{"$f":"VLOOKUP(B8,result2!$B$5:$E$18,3,FALSE)","v":10.3599999267607},16.354166666666668,263.14399813972176]
[null,5,{"$f":"VLOOKUP(B9,result2!$B$5:$E$18,2,FALSE)","v":68.883332570393804},{"$f":"VLOOKUP(B9,result2!$B$5:$E$18,3,FALSE)","v":0.40999999269843102},20.490740316885447,10.413999814540148]
[null,6,{"$f":"VLOOKUP(B10,result2!$B$5:$E$18,2,FALSE)","v":79},{"$f":"VLOOKUP(B10,result2!$B$5:$E$18,3,FALSE)","v":0.270000010728836},26.111111111111111,6.8580002725124345]
[null,7,{"$f":"VLOOKUP(B11,result2!$B$5:$E$18,2,FALSE)","v":75.319999694824205},{"$f":"VLOOKUP(B11,result2!$B$5:$E$18,3,FALSE)","v":2.8399999830871798},24.066666497124558,72.13599957041437]
[null,8,{"$f":"VLOOKUP(B12,result2!$B$5:$E$18,2,FALSE)","v":74.362500190734806},{"$f":"VLOOKUP(B12,result2!$B$5:$E$18,3,FALSE)","v":2.1099999975413},23.534722328186003,53.593999937549022]
[null,9,{"$f":"VLOOKUP(B13,result2!$B$5:$E$18,2,FALSE)","v":71.0750001271565},{"$f":"VLOOKUP(B13,result2!$B$5:$E$18,3,FALSE)","v":4.6999999452382299},21.708333403975832,119.37999860905104]
[null,10,{"$f":"VLOOKUP(B14,result2!$B$5:$E$18,2,FALSE)","v":65.076470318962507},{"$f":"VLOOKUP(B14,result2!$B$5:$E$18,3,FALSE)","v":13.2600002810359},18.375816843868058,336.80400713831187]
[null,11,{"$f":"VLOOKUP(B15,result2!$B$5:$E$18,2,FALSE)","v":52.887500286102203},{"$f":"VLOOKUP(B15,result2!$B$5:$E$18,3,FALSE)","v":1.6099999826401401},11.604166825612335,40.893999559059559]
[null,12,{"$f":"VLOOKUP(B16,result2!$B$5:$E$18,2,FALSE)","v":44.482352873858197},{"$f":"VLOOKUP(B16,result2!$B$5:$E$18,3,FALSE)","v":5.6699999012053004},6.934640485476776,144.01799749061462]

{"sheet":"グラフ","index":6,"n_rows":0,"n_cols":0,"rows_kept":0,"cut":false,"hidden":false,"merged":[]}