葛飾でひっそり暮らすIT屋の G Suite メモ

Google Apps で快適業務ライフなノウハウを伝えます

【Google Spreadsheet】タイムスケジュール表の作成を簡単にする3つの関数

【追記】

Spreadsheet や Excel では時間を計算すると誤差が出るようです。

それにより MATCH や VLOOKUP で時間をキーにした検索がうまくいかないことがあります。

回避策は下記の画像のように、比較する時間をテキスト化してから使うといいでしょう。

【追記終わり】

こんにちは、最近イベントの仕切りを任されててんやわんやしています。

イベントを運営するときに必要になるものの代表として、タイムスケジュールの作成があります。

これ、手打ちでポチポチ作っていくと非常に面倒なんですよね。修正入ったりすると全部手で打ち直しとか地獄としか思えません。

そこで、Google Spreadsheet を使って簡単に作ってしまおうというのが発端でこのエントリーを書くことになりました。

今回使用する関数は

  • VLOOKUP
  • OFFSET
  • MATCH

の3つです。

オマケで IFERROR も使っていますが、表示の調整用なのでまあまあ。

大まかな作成の流れは動画でどうぞ。

構成

このタイムスケジュールを実現するためには4つのシートを作成します。

  • 設定シート
  • 各項目への時間の割当
  • スケジュールのリスト
  • タイムスケジュール

といったところです。

全部同じシートに収めてもできるのですが、ちょっとごちゃごちゃするのでシートを分けます。

では、各シートの内容を確認しましょう。

設定

「設定」シートでは下記のような表を作ります。

項目
時間単位 0:05
開始時刻 10:00

「時間単位」は最終的なタイムスケジュール表の時間の刻みを設定します。大抵のイベントでは5分で刻んでおけばちょうどいいんではないかと思います。

「開始時刻」はそのままの通り、イベントの開始時刻を設定します。

時間割当表

演目に対して割り当てる時間を表にしておきます。

演目 時間
前座 0:30
スイカ割りMAX 0:45
正拳突き一本勝負 0:45
カリフラワー投げ 0:30
脳髄ビリビリ対決 1:00

ここまではなんてことないですね。

タイムスケジュール

ここから関数が出てきます。

A 列は演目を順番に並べていくだけです。

B 列で色々と計算していきます。

演目 開始時刻
前座 10:00
スイカ割りMAX 10:30
正拳突き一本勝負 11:15
カリフラワー投げ 12:00
脳髄ビリビリ対決 12:30
カリフラワー投げ 13:30
正拳突き一本勝負 14:00
スイカ割りMAX 14:45
終演 15:30

まず、B2 セルでは設定シートの「開始時刻」を参照します。

='設定'!B3

2行目以降になると VLOOKUP 関数が登場します。

=B2+VLOOKUP(A2,'時間割り振り'!$A:$B,2,0)

上の数式は B3 セルのものです。

まず、前の演目の開始時刻を基準にするので B2 セルを参照します。

そこに、時間を足すのですが、前の演目に割り当てられた時間を足すことで B3 の開始時刻を取得できるので、VLOOKUP の第一引数は A2 セル、一つ前の演目名を指すことになります。

後は時間割当表から該当する値をとるよう書いているだけです。

時刻表

最後に、時刻を並べた表に演目を記入していきます。

時刻 演目
10:00 前座
10:05
10:10
10:15
10:20
10:25
10:30 スイカ割りMAX
10:35
10:40

この表では B2 セルに下記の計算式が入っています。

=IF(C2<>"", IFERROR(D1+OFFSET($B:$B,MATCH($C1,$A:$A,0)-1,0,1,1),""),"")

時間を基準に、開始時刻に該当する演目をタイムスケジュールシートからとってくるのですが、スケジュールシートでは左に演目名、右に時間になっているので VLOOKUP が使えません。

そこで OFFSET と MATCH を組み合わせて、検索キーで指定する列の左にある値を取るようにしています。

これでタイムスケジュールが完成です。

タイムスケジュールって、難しい作業ではないのですがとにかく手間がかかるんですね。

変更があったときなどはいちいち時刻表から演目探したり、入れ替えたりしなければいけません。

今回作った表ならばタイムスケジュールの時間や、割当時間表を変更すると自動的に時刻表の方も変わるのでだいぶ楽になると思います。

Google スプレッドシートで簡単にメールを一斉送信する Google Apps Script ライブラリ

以前、スプレッドシートのリストに対して項目の差し込みをしつつメールを送るというエントリーを書きました。

www.katsushikatech.net

で、僕自身必要になったときにこれを使っていたんですが、この長いコードをコピペするのもだるくなってたんですね。

ということで、Google Apps Script のライブラリを作りました。

github.com

API ID: MbJOSVUHvWQuWWvCLcZM4nbFSwZrFpnJA

Google Apps Script のスクリプトエディタでライブラリとして読み込むと使えるようになります。

使い方のイメージとしては下記のとおりです。

config のオブジェクトを作ってメソッドの引数にすると動きます。

一応、差し込み項目の動作確認などもできるよう test メソッドも用意しました。

あと、以前のエントリーのときとは違って、テンプレート文書も Spreadsheet 内に記述するようにしました。

でまあ、お約束なのですが、ご利用の際は自己責任でよろしくお願いします。

Google スプレッドシートの表を Google Apps Script の配列に格納するライブラリ

スプレッドシートを Google Apps Script で処理するときに、表を配列にすることが多々あるのですが、それを実現するライブラリを見つけることができませんでした。

探し方が悪いのか、需要が無いのかはわかりません。簡単すぎて作ってられるかっていうことかもしれませんが、ライブラリにしておくと便利だと思いました。

github.com

README にあるとおり、一行目が項目名になっている表をオブジェクトの配列にするものです。

GAS 標準のクラスとかであっても良さそうなもんですけどね。

Google Forms でフォーム申し込みへの自動返信メールを送信するためのライブラリを作りました

どうも、僕です。

相変わらず G Suite を使っているのですが、Google Apps Script (以下 GAS) はなんで G Suite Script にならなかったのかとモヤモヤしっぱなしです。

まあ、そんなことはどうでもいいのです。

僕は仕事で Google Forms を使うことがまあまああるんですが、フォーム送信時に入力されたメールアドレスに自動返信をするためのスクリプトを毎回書くのがスーパーめんどくさいんですね。

そういうわけで、GAS のライブラリを作りました。

github.com

Google Apps Script のスクリプトエディタでライブラリとして読み込むと使えるようになります。

使い方は README.md に記載してありますので参考にして下さい。

フォームからの自動返信に関しては Addon もあるんですね。

chrome.google.com

無償版はメールのフッターに提供元のロゴが入るとか、一部機能制限があるようです。

有償版といっても、フォームごとに $5 ですんで仕事で使うなら安いものです。

僕は自動返信の設定に GUI とか必要ないので自分のライブラリを使いますが、スクリプトを書けない人なら Addon を使ったほうがいいと思います。

ではでは。


【Node.js】Ubuntu に Express と Sequelize、forever をインストールしてブラウザでアクセスするまでのメモ

Ubuntu で Node.js と Express を使ったウェブサイト構築の解説は見かけるんですが、実行するコマンドだけをズラッと並べておきたいなと思ったのでこちらに残しておきます。

sudo apt-get update
sudo apt-get upgrade
sudo apt-get install mysql-server

curl -o- https://raw.githubusercontent.com/creationix/nvm/v0.32.1/install.sh | bash
nvm install 6.9.1

npm i -g express-generator
express app_name

cd path/to/app_name
npm i
npm i --save sequelize
npm i --save mysql
npm i -g sequelize-cli

# MySQL でデータベースを作成してから下記の手順を行う
sequelize init
sequelize model:create --name User --attributes name:string
sequelize migration:create
sequelize db:migrate

npm i -g forever
forever start bin/www

参考にしたサイトは下記の通り。

qiita.com

この記事に forever を追加しただけです。