Memo/Google/SpreadSheet

スプレッドシート/spread sheet

スプレッドシート/spread sheet †

関数リスト

記事
- これで完璧。Googleスプレッドシートで使える21個の便利な関数 | ノマドジャーナル
- Googleスプレッドシートでコーディング（等）を楽にやる - LiBz Tech Blog

↑

フィルタ済みのシートをダウンロード †

問題:

フィルタやカスタムフィルタを適用し、ダウンロードしてもシート上のすべての値が出力される

解決:

FILTER - Google ドキュメントエディタヘルプを使う
別シート(sheet2)を用意する
元シート(sheet1)を対象として、sheet2:A1に以下を追加。例として、正規表現でE:E列に「python2.x, pytyon3.7」以下のみフィルタする。「Runtime」はheader行
```
=FILTER(sheet1!A:M,REGEXMATCH(sheet1!E:E, "Runtime|python2.|python3.[0-7]$"))
```
AND条件で複数指定したい
```
=FILTER(sheet1!A:M,条件1,条件2)
```
sheet2を対象として、ダウンロードする
"含まない"を表現するには「NOT(REGEXMATCH(...))」のように指定する。AND(), OR()もある

↑

例: sheet.20210101!A2:A2の件数をカウントしたい。「$A$2=20210101」と書いて動的に参照したい。

# counta関数では、エラー時(シートが存在しない等)にエラー文字もカウントして、1を返してしまう
=COUNTA(INDIRECT("sheet." & $A$2 &"!A2:A"))

# countif関数では、対象が数値なら">0"、文字なら"*"を指定する。数値に"*"を指定すると、0を返す。
=COUNTIF(INDIRECT("sheet." & $A$2 &"!A2:A"),"*")

↑

デバッグ †

Logging | Apps Script | Google Developers
- Logger.log('message');
- 表示 > ログに出力される。

「arguments.callee.name」で現在の関数名が取れる

例: sheetを取得。存在しなければログに残す

function get_sheet(sheet_name){
  var active_sheet = SpreadsheetApp.getActive();
  var sheet = SpreadsheetApp.getActive().getSheetByName(sheet_name)
  if(!sheet){
    Logger.log("[ERROR] [%s] %s is not found", arguments.callee.name, sheet_name)
    return
  }
  active_sheet.setActiveSheet(sheet, true);
  return active_sheet
}

↑

IFERROR: エラー時の処理の分岐 †

[IFERROR - ドキュメントエディタヘルプ](https://support.google.com/docs/answer/3093304?hl=ja)
- 関数が先にあり、後から対象のシートを追加した場合に再評価してくれない

「sheet」が無い場合、「0」がセルに入る
```
=iferror(sum(sheet!A:A),0)
```

↑

日付型式の変換 †

iso-8601形式から、Google SpreadSheetで対応している日付型式(YYYY-MM-DD hh:mm:ss)へ。タイムゾーンは切り捨て

echo "2019-01-02T03:04:05+00:00  2019-01-02T03:04:05Z   2019-01-02T03:04:05.123Z" | perl -ane 's/(\d{4}-\d{2}-\d{2})T(\d{2}:\d{2}:\d{2})(\.*\d*)(Z|\+\d{2}:\d{2})/\1 \2/g;print;'

2019-01-02 03:04:05  2019-01-02 03:04:05   2019-01-02 03:04:05

↑

BigQueryでスプレッドシートのデータを集計する †

記事
- スプレッドシート->BigQuery->データポータルでSQLでのデータ加工処理が必要なレポート作成を試してみる | Developers.IO

↑

SUMIFS: 複数条件を指定して合計 †

SUMIFS - ドキュメントエディタヘルプ

↑

IMPORTRANGE: 別スプレッドシートを読み込み †

IMPORTRANGE - ドキュメントエディタヘルプ
- query(), vlookup()等と組みあわせが可能。
- query()と組み合わせた場合、列の指定が「Col1, Col2」に変わる

記事
- GoogleスプレッドシートのQuery関数で結果が空になる謎仕様と回避策 - Qiita

↑

QUERY: SQLでデータを選択 †

select, where, order by, group by等でデータを選択できる。

QUERY - ドキュメントエディタヘルプ
Query Language Reference (Version 0.7) | Charts | Google Developers
- 使えるクエリ言語

IMPORTRANGE()と組み合わせる場合、列の指定が「Col1, Col2」に変わる
「select *」の場合は省略できる。

セルや関数を参照する場合: '"&value&"'
TRUE/FALSEは、クオート不要。クオート付けると一致しない。

C列が指定日付未満を表示

=QUERY('sheet1'!A:H,"where C < date '2020-05-01'",1)

C列が今日未満を表示

=QUERY('sheet1'!A:H,"where C < date '"&text(today(), "YYYY-MM-DD")&"'",1)

whereにカレントシートの値で検索

=QUERY('sheet1'!A:C,"select A,B,C where C='"&C2&"'",1)

↑

SUBTOTAL: 垂直方向の集計/フィルタ適用済みを対象 †

SUBTOTAL
- 9 - SUM: フィルタ適用された行のみを合計。名前付きフィルタも対象になる。

フィルタ適用された行のみ合計
```
=SUBTOTAL(9, A2:A101)
```

↑

カスタムフィルタ †

元データに影響を与えずに、複数のフィルタを作成できる
- 例: 複数人で作業する場合新しくフィルタを作ると他の人に影響が出ないため便利

データ > フィルタ表示 > 新しいフィルタ表示を作成

↑

カスタム数式 †

Google スプレッドシートの関数リスト - ドキュメントエディタヘルプ

カスタム数式で、計算結果がTrue, Falseになる関数が使える
例：1行目はヘッダ。2行目以降にデータ

REGEXMATCH: 正規表現
- 部分一致検索: A列に"foo"か"bar" 文字列を含む場合のみ表示
```
=REGEXMATCH($A1,"foo|bar")
```

ピボットテーブルのフィルタで指定する場合: データ元のシートの項目で絞り込める
```
=REGEXMATCH(sheet!A:A,"foo|bar")
```

OR: 完全一致のみ表示
```
=OR($A2="foo", $A2="bar")
```

↑

組織図をtableから生成 †

記事
- 【小ネタ】Google スプレッドシートで組織図を自動生成する｜ DevelopersIO

↑

sumif(): 条件付き合計 †

SUMIF - ドキュメントエディタヘルプ

H列に"yes/no/空白", E列に数値があり、"H列 != yes" のE列合計を計算したい場合、「<>」を使う。
```
=sumif(sheet1!H2:H,"<>yes",sheet1!E:E)
```

↑

QRコードを表示 †

スマホで情報を読み取らせたい時に便利

記事
- あのゲームのユーザーIDをスプレッドシートにQRコード表示して友達申請を楽にする｜ DevelopersIO

↑

セルに特定の文字列を入力した場合に行の背景色を変える †

範囲に適用: A1:F1000
書式ルール
- セルの書式設定: カスタム数式
- =countif($A1, "*文字列*")
- 空白以外にマッチさせたい場合: =countif($A1, "<>")
書式設定のスタイル: 好みの背景色

↑

iso-8601形式の日付を変換する †

「YYYY-MM-DDThh:mi:ssZ」のような形式が文字列として扱われてしまう。

記事
- ISO-8601 String to Date in Google Sheets cell - Stack Overflow

日付として扱われる形式へ変換

=DATEVALUE(MID($A2,1,10)) + TIMEVALUE(MID($A2,12,8))

↑

VLOOKUPで複数条件を指定 †

検索キーは、範囲の1列目固定。検索キーは、行を一意に識別できるPrimaryKeyを複数列を結合するなどして作る
- または、vlookup()の代わりに、query()関数を使う。where句で複数条件を指定できる。

itemシートにある情報を、sheet1から参照する場合
itemシート
PK Name Category Price

=B2&C2 A book 350

=B3&C3 B book 500

=B4&C4 A music 100

=B5&C5 B music 150

PK	Name	Category	Price
=B2&C2	A	book	350
=B3&C3	B	book	500
=B4&C4	A	music	100
=B5&C5	B	music	150

sheet1
Name Category Price

A book =vlookup(A2&B2,'item'!$A$1:$D$5,4,false)

A music =vlookup(A3&B3,'item'!$A$1:$D$5,4,false)

Name	Category	Price
A	book	=vlookup(A2&B2,'item'!$A$1:$D$5,4,false)
A	music	=vlookup(A3&B3,'item'!$A$1:$D$5,4,false)

↑

VLOOKUP: 行方向に検索して、一致した列の値を返す †

VLOOKUP - ドキュメントエディタヘルプ
```
VLOOKUP(検索キー, 範囲, 番号, [並べ替え済み])
```
- 範囲: 検索キーが1列目に含まれるように指定する。全範囲の中からは検索しない。
- 番号: 出力したい範囲の列番号(1～N)
- 並べ替え済み: 完全一致させたいならFALSE

↑

データをCSVでダウンロード †

ファイル > 形式を指定してダウンロード > カンマ区切りの値(csv)

公開したスプレッドシートが対象

ファイル > ウェブに公開 > 公開開始
URLからkey=の部分をメモ
以下のリンクのkey部分を置き換えて、ブラウザで開くと直接ダウンロードできる
```
http://spreadsheets.google.com/pub?key=[KEY ID]&single=true&gid=0&output=csv
```