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

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

Google Spreadsheet と Forms を使って簡易メルマガスタンドを作る

いまだにマーケティングツールとしてメルマガはよく使われる手法です。

でも、わざわざステップメールのサービスとか契約してっていうほどでもない場合もあるでしょう。

そんなとき、Google Apps を使って簡単にメールマガジンの購読申し込みのフォームを作って、配信することができたら結構便利。

というわけで、早速作っていきましょう。

まずは、Spreadsheet を作成します。

作成できたら、メニューの「挿入」から「フォーム」をクリックします。

f:id:katstech:20161027212800p:plain

f:id:katstech:20161027212804p:plain

フォームの編集画面になったら項目を作成します。

メールアドレスは必須ですが、それ以外は必要に応じて作ればいいですね。まあ、氏名くらいはつけておくと良いですが、登録のハードルを極力下げるならメアドだけという感じ。

ちなみに、Google Forms は項目の入力でメールアドレス以外が入力されると注意する機能があります。

f:id:katstech:20161027213149p:plain

Google さん、便利すぎます。

フォームができたら「送信」ボタンをクリックして、フォームのリンクをコピーします。

これをウェブサイトや SNS に公開してメルマガを募集するといいでしょう。

f:id:katstech:20161027213548p:plain

さて、ここからは Spreadsheet の編集です。

フォームを作成すると、Spreadsheet に「フォームの回答 1」というシートができています。

f:id:katstech:20161027213719p:plain

このシートにフォームで入力された値が蓄積されます。

これをそのまま使うのではなく、ちょっと加工していきます。

まずやるのは、メールアドレスのユニーク化。重複の削除ですね。

f:id:katstech:20161027214054p:plain

=UNIQUE('フォームの回答 1'!$C:$C)

なんでわざわざこれをやるかといえば、間違って二回登録した人に同じメールを二通送るのを防ぐためです。

それ以外にも、問い合わせフォームを作って、問い合わせをした方にメルマガを送ったりというときにも使えます。というか、むしろこっちのほうが UNIQUE の意義が大きいですね。

そして、メールアドレスをユニーク化したら、メールアドレスを元に氏名を取得します。

ただ、フォームの回答を見ると、氏名はメールアドレスの左にあります。これ、VLOOKUP だと取れないパターンですね。

こういうときには "OFFSET" 関数を使います。

OFFSET 関数は、指定したセルから縦横にずらしたセルのデータを取ってくる関数です。

上の画像で、シートの D1 セルに下記の関数が入力してあります。

=OFFSET($A$1,2,1)

最初の引数 "$A$1" は基準となるセルを指定しています。そして、順番に2行下、1列右というようにずらしています。

そうすると、関数の結果は A1 セルの2行下なので3行目、かつ1列右なので B 列の値になり、B3 という結果になります。

これをうまく使うと、VLOOKUP ではできなかった左側の列にある値をとってくることができるようになります。

より詳しい情報はこちらをどうぞ。

OFFSET - ドキュメント エディタ ヘルプOFFSET - ドキュメント エディタ ヘルプ

さて、実際にどうやってVLOOKUP の代わりにするかということですが、数式はこうなります。

=IFERROR(OFFSET('フォームの回答 1'!$B$1,MATCH($A2,'フォームの回答 1'!$C:$C,0)-1,0),"")

IFERROR は関数の結果がエラーのときに何も表示しないようにするために使っています。これを使わないと "#N/A" がズラッと並んで鬱陶しいんですよね。

肝心の "OFFSET" はまず、フォームの回答があるシートで名前が記載されている列の1行目を基準にしています。

そして、"MATCH" を使って、メールアドレスが回答の何行目にあるかを取得しています。"OFFSET" 関数では "MATCH" の結果から 1を引いた数を使っています。

なぜ1を引くかというと、"MATCH" で取得した数字は「何行目か」というものなので、その数字のまま "OFFSET" でずらすと1行多くずれてしまうんですね。

なので、1を引いて使っています。

列はずらす必要が無いので 0 としています。

こうすることで、名前の列の1行目から、特定のメールアドレスが回答の中で最初に出現する行のぶんより1少ない数だけ下にずらしたところにあるセルに入力されている名前を取得できます。

言葉にすると長くなりますね。。。

あとは、このセルを一番下の行までコピーすれば完成です。

ここまでできたら、メールを一括送信するためのマクロを書くと、申し込みフォームで受け付けたメールアドレスにメールを送る機能が実現します。

www.katsushikatech.net

このような感じで、Google Spreadsheet と Google Apps Script を組み合わせると、結構簡単にメルマガを送ることができます。

これらをマスターすると、他にも色々と応用できるのでチャレンジしてみて下さい。

ちなみに、Google Apps Script では1日に送信できるメールの数が决まっています。

support.google.com

無料試用版で500通ということですが、無料の Gmail だとどうなのでしょう。情報が見つからなかったのですが、100通くらいまでは送ったことがあります。

それ以上になるならばメール配信のサービスを使ったほうが良さそうです。

例えば、

www.benchmarkemail.com

あたりが有名ですね。

では。