yano3nora
7/1/2017 - 4:43 AM
[gas: Spreadsheet test] Attention on old code. #gas #js
[gas: Google Spreadsheets] Google Spreadsheets note. #gas #js
一括結合解除
なんか普通にだとできにゃい。多分「結合を解除」を選択できるようにしてから範囲選択 ... ってのが必要。GAS はいらない。
- どこでもいいので結合セルを選択
- その状態で cmd + a などで範囲選択
- あとは普通に「結合を解除」が選択できるので解除する
関数の追加
ハッシュ関数
/**
* =SHA256(A1&B1&C1) みたいに使う
*/
function SHA256(input) {
var rawHash = Utilities.computeDigest(
Utilities.DigestAlgorithm.SHA_256,
input,
Utilities.Charset.UTF_8,
);
var txtHash = '';
for (i = 0; i < rawHash.length; i++) {
var hashVal = rawHash[i];
if (hashVal < 0) {
hashVal += 256;
}
if (hashVal.toString(16).length == 1) {
txtHash += '0';
}
txtHash += hashVal.toString(16);
}
return txtHash;
}
範囲ソート
https://www.wikihow.com/Sort-by-Multiple-Columns-in-Google-Spreadsheets
- 範囲選択
- データ→範囲を並べ替え
- 優先列から順に ASC / DESC を選択
関数
https://support.google.com/docs/table/25273?hl=ja&ref_topic=3105625
http://blog.otakumode.com/2015/09/25/startup-paji-spreadsheet/
- regexreplace
- substitute
- sumproduct
- filter
- import
ArrayFormula
https://support.google.com/docs/answer/3093275?hl=ja
配列数式から返された値を複数行または複数列に表示したり、非配列関数で配列を使用したりすることができます。
- スプレッドシートの『ARRAYFORMULA』関数を使って表示速度を高速化させよう！
- 【スプレッドシート】arrayformula関数の使い方・IF関数
- 【GAS】ARRAYFORMULA関数を使って九九計算表、可変長型計算表を作ってみよう
AND, OR 的なことをやりたい
# こんな感じのことしたいけど and は配列処理で結果をまとめて
# bool 評価するので全部が true じゃないと true にならない
#
=arrayformula(if(and(A:A<>"", B:B<>""), "ok", "ng"))
# () で評価しつつ * で結果をかけ合わせて 1 or 0 にして and を表現する
# or は + でつなぐ
#
=arrayformula(if((A:A<>"") * (B:B<>""), "ok", "ng"))
連番を自動でいれるやつ
=ARRAYFORMULA(row(A2:A)-1)
空白だったら一番近い ↑ セルで埋めるやつ
Array Formula to Fill Blank Cells With the Values Above in Google Sheets
いみわからん。
# B 列の 3 行目から ↓ みたいになっているとき
# C 列で B 列の内容を上セルでデータ補完 (穴埋め) する
#
# A | B | C
# --- | ----- | ---
# | |
# | |
# | test1 | test1
# | | test1
# | test2 | test2
# | test3 | test3
# | | test3
# | test4 | test4
# | | test4
=ArrayFormula(iferror(if(row(C3:C)-countblank(C3:C)<=MATCH(2,1/(C:C<>""),1),lookup(row(B3:B),row(B3:B)/if(C3:C<>"",TRUE,FALSE),B3:B),),""))
# 左が空白のときだけ表示させて、階層っぽくみせる、とか
#
# A | B | C
# --- | ----- | ---
# | |
# | |
# | test1 |
# | | test1
# | | test1
# | test2 |
# | test3 |
# | | test3
# | test4 |
# | | test4
↓ この B 列が空なら、を階層が増える毎に追加する
=ArrayFormula(iferror(if((B:B="")*(row(C3:C)-countblank(C3:C)<=MATCH(2,1/(C:C<>""),1)),lookup(row(B3:B),row(B3:B)/if(C3:C<>"",TRUE,FALSE),B3:B),),""))
Query
https://support.google.com/docs/answer/3093343
Google Visualization API のクエリ言語を使用して、データ全体に対するクエリを実行します。
データを集約したりカウントしたりするのは Query を使うのが楽。
- QUERY関数を使って複数シートの情報を統合する
- Google スプレッドシートからデータをSQLライクに取得してグラフを描く
- Googleスプレッドシート QUERY関数の条件(WHERE)に日付を使いたい
予約語との衝突
BYやORなど SQL で予約語になっている列名を使う時は衝突に注意- Reserved Words を参照
# BY が予約後なのでバッククォートでかこってやる
=query(sheet!A2:BZ, "select A, AB, `BY`, BZ")
クエリ結果が空のときの制御
query({}) 内部でクエリ結果配列を union する際に、結果が空のものがあると ARRAY_LITERAL の配列リテラルで、1 つ以上の行の値が見つかりませんでした。 エラーになる。
iferror() で囲ってエラーケース (結果が空) のとき select で指定した数の空要素を含んだ配列リテラルをわたしてやる。
# 2 つの別シートの A 列には同一の ID があって
# それを 1 つのデータにまとめる的な簡易的な例
#
=query({
iferror(query('別シート1'!A2:C, "select A, B, C where B != ''"),{"","",""});
iferror(query('別シート2'!A2:C, "select A, B, C where B != ''"),{"","",""})
})
別シートの内容を 1 つにまとめる
# importrange を使って別 sheets の sheet を参照してる、自シートでもおｋ
# select 部分は省略可能、この数式では列 B, A, C で表示されるよう入れ替えてる
# where 句で空白行を無視してる
=query({
query(importrange("https://...", "しーと１!a2:c"),"select Col2, Col1, Col3 where Col1 is not null");
query(importrange("https://...", "しーと２!a2:c"), "select Col2, Col1, Col3 where Col1 is not null")
})
横持ちテーブルを正規化 (縦持ち) に変換
いわゆるマルチカラムアトリビュート (tag1, tag2 みたいなカラム) を正規化するときの。
カスタム数式
address(row(), column()) # AB6 とかがとれる
// このカラムが hoge なら
=INDIRECT("G"&row())="完了"
// 一つ上とおんなじ
=(INDIRECT("A"&row()-1)=indirect("A"&row()))
// この列の ↑ の 2 行目が "土曜日" だったら
=indirect(address(2, column()))="土曜日"
// がんとチャートっぽいので使った
=and(datevalue(indirect((address(row(), 4))))<=datevalue(indirect(address(1, column()))), datevalue(indirect((address(row(), 5))))>=datevalue(indirect(address(1, column()))))
// 奇数偶数でうんたら
=MOD(ROW(),2)=1
// 範囲と範囲を比較して違ったら
=A1<>'HogeSheet'!A1