仕事メモとか

仕事中に調べた情報とか知ったことをメモしています。
unixコマンド, vim, oracle, putty, postgresql, bash, EXCEL, python, SQL全般 など。
最近は tableau, movabletype とかも触ったりしています。
雑な読書感想とかはこちら

カテゴリ: google スプレッドシート


googleのスプレッドシートで翻訳を使ってみた。GOOGLETRANSLATE関数

はい、ちょっと話題になっていたので、スプレッドシート上で翻訳関数を使ってみました。

例)
=GOOGLETRANSLATE(B2,"en","ja")

翻訳したい文があるセル、その言語、そして翻訳語の言語を設定します。
日本語は「ja」です。jpって書いたらエラーになりました。


いくつか英文の名言を翻訳してみましたが、格言っぽくはならないというか、情緒がなくなった感じもしますが、
意味は結構分かるし、少なくとも日本語っぽい文章になります。


以下、翻訳例。

ワシントン
Observe good faith and justice toward all nations. Cultivate peace and
harmony with all.
すべての国に向けて誠意と正義を守ってください。すべてと平和と調和を育成。

ベーコン
A wise man will make more opportunities than he finds.
賢い人は、彼が見つけたよりも多くのチャンスを作ります。

オルコット
There is always light behind the clouds.
常に雲の後ろに光があります。

ゲーテ
He who moves not forward, goes backward.
前方に移動していない彼は、後方に行きます。


スプレッドシートで作った関数を大量発行したら、途中からエラーになった

はい、以下の記事の続きです

http://workmemo.techblog.jp/archives/37357577.html

喜び勇んで作った関数を大量に(1万件)くらい回してみたら、
実行に結構時間が掛かるうえ、ERRORになってる場所が結構ある。
ただし個別にデコードするとちゃんと成功する。

調べてみたらgoogleスプレッドシートって
スクリプトを大量に呼ぶと途中であきらめてERRORにする癖があるようで。

参考:
http://jutememo.blogspot.com/2014/01/google.html


ちゃんと測ってませんが、直観的に1000件くらいを超えると怪しい。

#ERROR! となり、以下のエラーが吐き出されます。
Service invoked too many times in a short time: exec qps. Try
Utilities.sleep(1000) between calls.(行 0)が発生しました。


今回は自動化することが目的ではなく、単発でデータを出すことが目的だったので、
ISERRORでERRORになっていない部分を抜き出してデータ化。
ERRORになってる部分だけを再計算するようにしました。

この再計算もスプレッドシートの自動計算のクセなのか、
フォーカスのin-outではだめで、一度セルを綺麗にして再度コピペする、みたいにしないとダメでした。
再計算ボタン探したけど見当たらなかったのですが、たぶんどこかにあるんじゃないかと。


スプレッドシートは便利だけど、地味な落とし穴がたまに空いてます。


アクセスログにあるエンコーディングされた文字列を良い感じに表示する

アクセスログ見たら、大体エンコーディングされた%ナントカみたいなのが大量に並んでいます。
んで、これを良い感じに見えるようにしようと思ったんですが、googleスプレッドシートで出来るんじゃ? と思ってやってみました。

但し、デコード関数が無い点に注意


●アクセスログからデータ取得
zgrep {取りたいURLだけを特定する文字} {アクセスログファイル} | cut -f{URLのx番目}

●スプレッドシートに貼り付け

●URLをデコード
エンコード関数はあるものの、デコード関数は無いので、
「ツール」「スクリプトエディタ」から関数を作成

参考:
https://teratail.com/questions/91518

こちらの例で言うと、JavaScriptのdecodeURIを直接読んでます。素晴らしい

●作った関数を呼ぶ


これで簡単にURLデコードして日本語部分を良い感じに抜けるようになりました。
ついでにmid関数とか使って不必要な部分をカットすれば割と色々使えそうです。


goolgeのスプレッドシートでカンマ区切りを複数セルに展開


インプット
1,2,3,4

これを複数のセルに入れたい、という時に使うものです。

A1セル:1,2,3,4
B1セル:=split(A1,",")

これで、B1以降のセルにデータが展開されます。
C1以降は実データが入っているように見えますが、元データを修正すると追従します。

B1セル:式が入るが、見える実態は「1」
C1セル:「2」が直接入る。但しA1を変更すると追従する
D1セル:「3」
E1セル:「4」


ちなみにデータが空の場合、通常はセルを詰める動作になるのですが、
設定によって詰めない方法もあります。

説明:
SPLIT("a,b,c", ",", TRUE, TRUE)
これの第四引数

空のテキストを削除 - [任意]
SPLIT の結果から空のテキストを削除するかどうかを指定します。デフォルトは TRUE で、連続する区切り文字を 1 つの区切り文字として扱います。
FALSE にすると、空の値のセルが連続する区切り文字の間に追加されます。


参考:
http://webbeginner.hatenablog.com/entry/2014/09/22/104019


スプレッドシートで複数エリアの文字を含むデータを合算(arrayformula)

例)
以下のようなデータがあるときに、数字だけ合算したい、という時に使いました。

A列
1冊
2本
3本
2人
1本
203本
20本


原因は総数を見たいけど、フォームで行が自動で追加されるので、式列を用意出来ないなーと思ったこと。
どんどん増えていく行にも対応できるうえ、正規表現か何かで数字だけ取ってくる必要がある。

ということで、こんな感じにしました。

=arrayformula(sum(iferror(value(regexectract(A:A,"\d+")),0)))

1つずつ説明します。

regexectract
正規表現で数字だけ取ってくる

value
獲得した値を数値化

iferror
範囲指定がA:Aなので数字が無くてエラーがになるので、その場合は0を入れる

sum
値を合算

arrayformula
これで式内が範囲指定(A:A)でも対応可能


という形でシンプルになりました。

最初は
=countif(A:A,"3本")*3 ...
とcountifを沢山列挙する方法しか思いつかず、苦戦しました。

↑このページのトップヘ