Google Sheets
/
Google Sheetsの使い方
概要
- excelのオンラインバージョンのアプリ
- bigqueryと繋げられたり便利
- 呼称のエイリアスが多くあり、
スプレッドシート,スプシ,GSSなどとも呼ばれる
Google Apps Script/GAS
概要
Microsoft Excelのマクロに該当する機能- 様々な具体例はGoogle Developersに記されている。
Google Apps Scriptを開く
- SpreadSheetの
[拡張機能]->[Apps Script]からスクリプトエディタを開くことができる。
ssidについて
- URLに含まれるハッシュ値のようなものがssidでありスプレッドシート毎の固有値
- ssidを指定してgasで操作可能
基本的な操作
日付の表示を %Y-%m-%d に
[表示形式]->[数字]->[表示形式の詳細設定]->[その他の日付や時刻の設定]
csv fileのインポート
[ファイル]->[インポート]->[アップロード]
Queryを使う
- SpreadSheet上のデータに対して、SQLのようなクエリを使うことができる。
A B
1 name val
2 a 10
3 b 20
4 c 30
5 a 15
6 b 40
以上のようなデータがあった際に、Bのカラムの平均や合計
=QUERY(B2:B6, "SELECT AVG(B), SUM(B)", -1)
Aに対してGROUP BY操作のようなこともできる
-
=QUERY(A2:B6, "SELECT A, AVG(B), SUM(B) GROUP BY A", -1)
セレクトボックを作る
[Data]->[Data Validation]->クライテリアを入力して、"Show dropdown list in cell"を選ぶ
column, rowの幅や高さを変える
- 幅や高さを変えたいcolumn, rowを選択
- 右クリックし
Resize columns...またはResize rows...を選択 - 設定したい幅か高さのpixel数を入力する
セルの中身を折り返して標示する
- テキスト位置のメニューの
|→|のようなアイコンを選択 |⏎|のようなアイコンにすると折返しになる|→|; デフォルト。溢れた状態にするoverflow|⏎|; 折返し。text wrapping|-|; 溢れた文を消す。clip
基本的な可視化
積み上げグラフ
- indexがrow
- 積み上げる種別がcolumnになっている必要がある
- つまり、pandasであればhueを指定すればいいが、google sheetsでは明示的にpivotする必要がある
Google Sheetsからテキストエディタにデータをコピーする場合
- Google Sheetsにてコピーしたい範囲を選択
- テキストエディタにコピーする
- コピーしたデータはセルの区切りが
\t(タブ)になっているので、必要なパーサーでパースする- pandasであれあば、
pd.read_csv(io.StringIO("""..."""), sep="\t")となる
- pandasであれあば、
大量のシートを含むデータをGoogle Sheetsにアップロードする場合
- ユースケース
- 機械学習による大量の推論結果をGoogle Sheets経由でシェアする場合
- 手順
- pandasから一時的にExcelに出力
- Excelファイルを
スプレッドシートの置換でインポート- 大量のデータをインポートすると、
Google ドキュメント内でエラーが発生しました~と出るが、リロードを行うと、正常に反映される
- 大量のデータをインポートすると、
アドオン関連
- アドオンの追加
[拡張機能]->[アドオンを取得]
OWOX BI BigQuery
- 概要
- 無料版のgoogleアカウントだとbigquery連携(データコネクタ)が使えないが、このアドオンを利用するとSQLを実行することができる
- 参考