かもメモ

自分の落ちた落とし穴に何度も落ちる人のメモ帳

Spreadsheet

Google Spreadsheet ◯年の月末日のリストを自動で作りたい

忘れてたのでメモ Spreadsheet では 2024年1月 のような表記でも日付フォーマットの場合日にちを持っている。なので、2024-01-01 の 2024年1月 と 2024-01-31 の 2024年1月 は別物として扱われる。 例えば年始から各月末の変化をグラフにする際に年始は年始…

Google Spreadsheet VLOOKUP と ARRAYFORMULA でシートから検索した行の値を自動的に取得しする

やりかを良く忘れるのでメモ 特定のセルの値と合致 / 含む、行を探してその中からあるセルの値を取得する(VLOOKUP) 上記の処理を列全体で実行する (ARRAYFORMULA) 他のシートから検索した値と合致した行の値を取得する (VLOOKUP) =VLOOKUP(検索キー, 範囲, …

家計を支える技術 Google Spreadsheet を使って物件の比較検討をする

Google Spreadsheet の QUERY と ARRAYFORMULA を使って物件の比較検討シートを作る

Google Spread Sheet 入力のあるセル数(空白以外)を得たい

平均値を出す時とか。入力のあるセル数をカウントできると値を入力するだけになるので便利です。 COUNTIF(範囲, "<>") を使えばOK COUNTIF関数の構文は下記の通り COUNTIF(範囲, 条件) 条件部分に空白以外を表す等しくないを表す比較演算子 <> を指定すればO…

Google SpreadSheet ArrayFormula の中で IsDate が意図したとおりに動かないにハマる

下記のようなデータフォーマットが揃っていないスプレッドシートが有り日付が入っている場合のみ加工した値を出力させたかった A B 文字列 aaa 数値 123 日付 2022/01/01 存在しない日付 2000/2/30 空白 日付 1938/11/30 文字列 bbb ArrayFormula 内で IsDat…

Google SpreadSheet 複数のカラムでグルーピングしたい。ついでに複数カラムで並び替えたい

例えば都道府県別の飲食店のリストみたいなものがあって、都道府県別のカテゴリー別で店舗数を出したいみたいな時、都道府県とカテゴリーと複数のカラムでグルーピングする必要があります。 シート名店舗リスト A B C 1 店舗名 都道府県 カテゴリー 2 店舗 A…

Google SpreadSheet UTF-8 な CSV をスプレッドシートで開いたら日本語が文字化けするときのTIPS

UTF-8 なのにスプレッドシート変換で日本語が文字化けする時は、ファイルの先頭に日本語の文字を入れると上手くいくかも!

Python データにコンマ , を含む CSV をいい感じに扱いたい

Pyton で CSV を扱うなら標準ライブラリの csv を使うのが良い。

GAS SpreadSheet IMPORTRANGE で 外部シートを読み込もうとして 循環依存が検出されました。なエラーにハマる

PM っぽいことをしてて SpreadSheet を触るばかりの日々を過ごしています。 お久しぶりに SpreadSheet で他のシートからの参照をしててエラーになってしまったのでメモ。 IMPORTRANGE で外部のシートを読み込もうとしたら 循環依存が検出されました。 という…

GAS SpreadSheet 複数のシートのデータをまとめて表示したい

1日目, 2日目, 3日目のイベント情報のデータがあってそれぞれ別のシートにあるから全体を見れるように 1つのシートで表示したいみたいなケース day1 A B C D 1 id name day action 2 1 星宮いちご day1 xxx 3 2 霧矢あおい day1 xxx 4 3 紫吹蘭 day1 xxx day…

GAS SpreadSheet range 「Exception: 範囲の行数には 1 以上を指定してください。」にハマる

1行目にカラム名を入れてある SpreadSheet で1行目以下を使った GAS を書いていてうまく動作してなかったメモ。 エラー箇所を探す Slack連携で使っているスクリプトだったので、各関数をまずは単独で動くモックのスクリプトに変換しました。 次に怪しい箇所…

Google スプレッドシート GAS replace が見つかりません。

数値に `.replace()` をするとエラーになるので、数値が渡ってくるのか文字列が渡ってくるのか分からない場合は `.toString` したほうが安全。 今日も一日ご安全に!

Googleスプレッドシート QUERY で取ってきたデータの複数のセルをそれぞれフォーマットしたい

数値で計算しているシートから QUERY や IMPORTRANGE を使ってデータを取ってきて、SELECT文で必要な絡むだけにした上で単位を付けたりフォーマットして表示したい。 元のデータ例 シート名 販売目標 A B C D E 1 エリア 消費量 (kl, 2016) 目標売上量 (kl) …

Google スプレッドシート 自動的に生年月日の列から年齢を計算したい

Spreadsheet に次のような誕生日の入ったデータがあり、自動的に今、何歳なのか表示させたい。 A B C 1 名前 誕生日 年齢 2 星宮いちご 1999/03/15 3 霧矢あおい 1999/01/31 4 紫吹LAN 1998/08/03 5 藤堂ユリカ 1998/12/26 6 大空あかり 2000/04/01 7 神崎美…

GAS Spreadsheet 背景色を消したい

GASでスプレッドシートの背景色を白#ffffffではなくナシにしたい (CSSで言うところのtransparent) とき。 setBackground( null ) を使う setBackgroundColorではなくsetBackground。 まるっと1行背景色をナシにするには次のような感じ var sheet = Spreadshe…

Googleスプレッドシート 時間を数値にして計算したい。

勤怠表から時給換算とか、01:30:00な経過時間フォーマットな表のデータを1.5とかに置き換えたい時 VALUE を使う VALUE Google スプレッドシートで認識される日付、時刻、番号の書式の文字列を数値に変換します。 VALUE("12:00:00") 出典: VALUE - ドキュメン…

Googleスプレッドシート 数字を3桁コンマ区切りでフォーマットしたい。

金額とか、大きな数字を10,000のような3桁コンマ区切りにしたい時、セルに数字が入っているだけなら書式を変更すれば済みますが、合計: 10,000みたいにラベルを入れたいような時文字列になってしまうのでセルごと書式変更をすることができません。 例えばA1…

Googleスプレッドシート 月末までの日付で検索した一覧を取得したい

やりたいこと。 有効期限とかの日付の入っている次のようなデータのマスターシートがあるとして、検索月を入力したら有効期限が今月末までのデータをマスターシートから取得して一覧が表示されるようにしたい。 A B C D E F 区分 ID タイトル URL 有効期限 …

Google スプレッドシート nヵ月前の日付をチェックしたい

例えば、下記のような期限日が入力されているシートで期限まで3ヵ月切っているセルをハイライトしたいような時のメモ。 A B 1 案内日 期限(6ヵ月後月末) 2 2016/07/07 2017/01/31 3 2016/08/07 2017/02/28 4 2016/09/10 2017/03/31 5 2016/10/06 2017/04/30 …

Googleスプレッドシート 関数を含めた複数の値を文字列結合したい。

計算結果に単位を付け足したりしたいことが割りとあり、そんな時は文字列結合を使います。 例えば下記のようなプロジェクトでの工数管理シートで人日や週何日相当なのかとかを出したいと思います。 A B C D 1 項目 所要時間 人日 備考 2 ミーティング 7:50:5…

特定のラベルが付いたgmailでの受信を自動的にスプレッドシートに記録したい

サービスとかのメールフォームから送られた内容とかアラートとか、まぁDBに保存してダンプすれるのが本来良いと思うのですが、DBが無かったり諸々のアレでメールだけで受け取っていたりするような事もあると思います。 今回はGmailで受け取ったそれらのメー…

Googleスプレッドシート QUERY関数の条件(WHERE)での空白セルの扱い方

Google SpreadsheetのIMPORTRANGE関数で他のシートを参照させ、QUERY関数で表示させる条件をつける時、空白のセルがあるものを除外・空白があるものだけ表示させる方法のメモ。 QUERY QUERY(データ, クエリ, [見出し]) QUERY - ドキュメント エディタ ヘルプ…

Googleスプレッドシート QUERY関数の条件(WHERE)に日付を使いたい。

スプレッドシートのIMPORTRANGE関数とQUERY関数の組み合わせ便利ですね。 データがまとまったシートを作成しておいて、必要な情報をだけを表示させるのに使っています。 今回 日付を条件にしてデータを取得しようとしてハマったのでメモします。 例えばA列に…

Googleスプレッドシート Query関数 変数でキーワード検索(LIKE)したい

前回 他のスプレッドシートからデータを取ってきて表示するセルの指定と、WHEREでの表示させるデータの条件を付ける方法を書きました。この時は検索条件が=とか>とかでしたが、例えば住所を入れてあるセルから新宿区というキーワードがるものだけ取ってきた…

Googleスプレッドシート 他のシートから特定のセルを条件付きで取ってきて表示させたい

他のスプレッドシートで作成してあるリストから必要なセルだけを取ってきて、条件に合うものだけを表示させる方法のメモ。 IMPORTRANGEとQUERYという関数を使って実現することができました。 他のスプレッドシートからデータを取ってくる関数 IMPORTRANGE IM…

Google スプレッドシート セルに特定の値を入力したらその行の背景色をかえたい。

スプレッドシートで情報を管理してて、作業が完了したらセルにチェックを入れる。 その時にパッと見で判るように完了した行にまるっと背景色を付けたい。そんなメモです。 セルに背景色を付けるだけなら、条件式付き書式設定ルールで簡単に行うことができま…

Google スプレッドシート 入力時に同じ値がないかチェックしたい。

同じ列にデータを入力していて、入れたい値が重複してないか簡単にチェックしたいとかって時のメモ。例えば下のようなA列のデータがあるとします。 A 1 id 2 AAAA05 3 AAAA01 4 AAAA06 5 AAAA04 6 AAAA07 7 AAAA03 8 AAAA08 9 AAAA02 10 _______ A10にAAAA01…

Google Drive スプレットシートで1年後の月末を取得したい。

セルに入っている日付の1年後の月末を取得したいと思います。 当方IE6,7,8と闘い続けてきた過去のトラウマのせいでマイクロ◯フトを敵視していたので今までExcel使ったことありません。 1年後を取得する EDATE(開始日, 月数)関数というものがあるようです。 …

Google Apps Script 俺がスプレットシートの値を変更した時に特定のセルの値によって異なるシートに行(row)をまるっとコピーしたいんだが。

ちょっと前に流行ったラノベみたいなタイトルになってしまったが後悔はしていない。 例えば数箇所で行うイベントの予約をGoogleDriveのフォームで取っていたとして、予約のリストが回答というスプレットシートに反映されます。で、入金を確認した時にシート…

Google Apps Script スプレットシートでスクリプトのlogをとりたい。

何かと楽なのでGoogle Driveのスプレットシートを使うのですが、自動化をしたいとかでApps Scriptを書いている時、スクリプトエディターから実行させるだけ時はLogger.log()で書いておいてメニューの表示からログを表示させておけばよいのですが、セル内を変…