Google sheet
最近做比较多 data migration 的东西。
当我们开发一个新的系统去替代一个旧系统时，通常就需要做大量的 migration 动作.
有好几个做法
我之前比较常用的的工具是 sql 和 c#
sql 就很简单啦, 2 的 database 直接写代码操作.
遇到比较复杂的情况, 通常需要用到储蓄过程. 不是很喜欢写那些，语法太落伍了.
这时候就会配上 c# 来写.
虽然这 2 个可以打天下，但是感觉有点笨重，data migration 最好能用一些比较轻巧的工具.
因为有时候 data migration 时你会发现，之前的 data 未必是正确的, 可能是之前的系统保护的不是很好，总会有些鬼鬼的资料会在你的预料之外。
然后你的 migration 程序就卡着了. 这就导致很多时候我们需要先写一些正则去过滤一遍，确保我们预想到的格式是 ok 的.
如果发现问题还得和客户询问.
所以最好有一个比较轻巧的工具.
本来以为 excel 会是首选，谁知道它的 filter 非常弱, 除非自己写 vb code
后来就开始用 google sheet 了. 够轻, online， 功能也还不错用.
这里就开始记入一些比较长用到的功能:
Array
array 用花括弧
比如 { A1: B } A1 到 B, B 没有表示 row number 就是所有的意思.
比如 { 1, 2, 3; 4, 5, 6 } 就是 3 个 column 2 条 row.
注意, 逗号和分号, 一个是 column 一个是 row
{ C7:C12, D7:D12 } 还可以这样表达, 这个让我们很方便抽取某个 table 的 某些 column 和 data 出来，或者调换位置.
有一点要留意的是，这个 array 必须同样 column count，不然会怪怪的。
QUERY
QUERY(A1:B, "SELECT A, B", 1)
第一参数: table 范围.
第二参数: 语句, 有点像 sql 语法.
第三参数: header row count, 需要告诉它 table 里面多少 row 是 header. 通常是 1, 如果 select 的 table 没有 header 就不需要写咯.
"SELECT A" vs "SELECT Col1"
和 sql 不同，它 select 不是 column name, 而是位置.
Col1, Col2 是特别语法, 只有在第一个参数是 Array 时能用
比如 QUERY({ A1: B }) 这个是 array, QUERY(A1,B) 这个不是 array 就用不到 Col1 了, 只可以用 A1, B1 这样.
QUERY rename column
SELECT A, B, C LABEL A 'new column name', B 'new column name'
QUERY + Upper, lower, title case
SELECT UPPER(A), LOWER(B), PROPER(C) LABEL UPPER(A) 'new column name'
PROPER 就是 title case, 记得如果前面加了 UPPER, 那么 LABEL 的时候也要加哦. 不然会 match 不到的.
QUERY + 常用
WHERE NOT Col1 CONTAINS '#N/A'. 过滤掉 #N/A
WHERE B STARTS WITH 'BS'. starts with
WHERE B ENDS WITH 'BS'. ends with
QUERY WHERE <> '' and IS NOT NULL
<> '' 和 != '' 是一样的用在 string
is not null 用在 number
不要弄反哦，会坏掉的
COLUMNS and COLUMN
COLUMNS(C7:E12) 返回有多少个 column 在这个 list 里面
COLUMN(C7) 返回这个 cell 在第几个 column
INDIRECT
INDIRECT("D7") D7 是一个 string, 通过这个方法我们可以拿到 D7 这个 cell 的 value
UNIQUE
UNIQUE(A1:A10)
就是 distinct 咯
CONCATENATE
CONCATENATE("a","+","b")
用来 merge string 这个
当遇到 ARRAYFORMULA 时会不能用. 可以用 & 来替代 A1&"+"B1
INDEX
INDEX(range, row, col)
从一个 table 里面拿指定的 cell value 出来. 在那种返回 array 的情况蛮好用的
比如 SPLIT
JOIN
JOIN(",", A1: A3)
就是 array.join(',') 啦
VLOOPUP
VLOOKUP(H15,C8:E12,2,0)
用来找 data 的,
第一参数是 search key word
第二参数是 table
search key word 会去 table 第一个 column match every row
第三参数是 column index
match 到 row 之后返回指定的 column
最后一个参数不清楚, 通常是放 false
ARRAYFORMULA + VLOOPUP 可以实现 left join 效果
使用 ARRAYFORMULA 后, 第一个参数和第三个参数就可以写 range 了, 比如第三个参数可以写多个 column { 1,2,3 } 这样
ARRAYFORMULA
A1 - B1
A2 - B2
通常我们要写一整排一样的 formula 的时候, 我们会写第一个，然后 copy furmula 到下面所有 cell 去, 结果类似上面这样
ARRAYFORMULA(A1:A - B1:B) 可以换成这个写法，效果是一样的. 简单的看就是在原本的 formula 上面套一层方法
然后把里面的 cell 换成 range 就可以了
配合 SUM(ARRAYFORMULA...) 这类操作会很好用
AND, OR, CONCATENATE 不能用哦
https://webapps.stackexchange.com/questions/54645/concatenate-a-string-with-an-arrayformula
and 和 or 的替代方式是乘和加
比如 and, 1 * 0 = 0 所以只要其中一个是 0 就是 false
or 的话就是 1 + 0 = 1 只要一个是 1 那么就是 true
google sheet 的 IF 0 代表 false, 其它号码都代表 true 包括 -1
MATCH
MATCH(P4,K4:K6,0)
第一个参数是 search key word
第二个是查找的 rang, 1 column multiple row
第三个不知道，放 0 就好
返回的结果就是 match 到在第几个 row index
可以通过 ISNUMBER(x) 转换成 boolean
FILTER
FILTER(A2:B26, A2:A26 > 5, D2:D26 < 10)
第一个参数是 table
第二个是 range + 一个判断 (通常是指 table 里面某个 column)
第三...更多的条件
返回的结果就是参数 1 整个 table
LEFT JOIN
有 2 个做法
ARRAYFORMULA + VLOOPUP
https://stackoverflow.com/questions/14796620/google-spreadsheet-query-join-equivalent-function
ARRAYFORMULA + MATCH + FILTER
https://www.youtube.com/watch?v=JQSlbQeEz1k&t=404s
都可以用
LEFT, RIGHT, MID, LEN
string 方法
LEFT("Derrick", 2) 拿前面 2 个
RIGHT("Derrick", 2) 拿后面 2 个
MID("Derrick", 2, 3) 第 2 个字开始，拿 3 个字出来. 注意: 第一个字的 index 是 1 不是 0 哦
LEN("Derrick") 拿 length
FIND("rick", "Derrick") js 的 'Derrick'.indexOf('rick'), 不过它是 start with 1 而不是 0 哦 (case sensitive)
SEARCH 和 FIND 一样功能只是它 ignoring case.
POW, SQRT
数学方法
POW(10, 2) 就是 10 kuasa 2
SQRT(9) 就是 punca kuasa, 答案就是 3 因为 3 * 3 = 9
COUNTIF
COUNTIF(A:A, A1)
COUNTIF(A:A, ">4")
获取 count, 第一个参数是 range, 第二个是一个 string 的话那么就是等于，如果是表达式那么就是表达式咯
IMPORTRANGE
IMPORTRANGE("15SmfIERpdYO8pyBKCsWGrbS-gOlV9M3QuvuMglsVmD8","oil seal!A1:E")
第一个参数是 spreadsheet 路径
第二个就是 sheet name 和 range (sheet name 不要小点 ')
SUBSTITUTE
SUBSTITUTE("search for it","search for","Google")
可以当简单的 string replace 来用
Javascript custom function
google sheet 一个亮点就是可以用 js 写方法.
这个可方便了
Tools -> Script editor
会打开多一个 page, 然后自己写方法就可以了.
它处理 array 的方式是 array array , 比如你丢一个 A1: B2, js 获取到的是 [['a', 'b'], ['c', 'd']] 是长这样.
然后就可以 for loop 之类的了.
此外要在 script 里面操作 sheet 也是可以的, 通过 javascript api 可以 set value 啦，等等.
方法返回 array 的话, ['a', 'b', 'c'] 出来的结果是直的, 如果想横的话可以 [['a'],[''b]], 如果要出 table 也是 array array
如果想加全局变量给 function 方便用. 可以在 editor page -> File -> Project properties 里面设置
然后通过 js api 去拿到
PropertiesService.getScriptProperties().getProperty('mykey');
https://stackoverflow.com/questions/24721226/how-to-define-global-variable-in-google-apps-script
还有很多 api 可以用，之后再介绍.
注意 : custom function 不可以调用 google 的 built in function 哦
虽然很不可思议，但是这是真的. google 只提供了一些 Utilities
