仕事メモとか

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

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


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

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

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を沢山列挙する方法しか思いつかず、苦戦しました。


スプレッドシートで、特定のセルの値を入力したら、更新セルに年月日時分秒が自動で入るようにする、の続編


スプレッドシートで、特定のセルの値を入力したら、更新セルに年月日時分秒が自動で入るようにする
http://workmemo.techblog.jp/archives/33589237.html

こちらの続きです。
トリガーを作って自動化したので、めでたしめでたし、と思っていたのですが、
この方法だと、変更を検知しているセルに「他セルからコピペ」で値を持ってきた場合、発動しないことがわかりました。

恐らく、セルにフォーカスして編集モードになったか、みたいな制御をしているのかもしれませんが、
これだとやりたいこととしては足りない状態です。

トリガーを眺めているのですが、定期的に変更を検知するとか、ボタンを用意するとか、違った方法にしないとダメかもしれません。
コピペでもトリガーが動かせる方法が見つかったらまたアップします。

↑このページのトップヘ