【GAS】Google スプレットシート で BOM付きUTF-8 な CSV出力をしてみよう！

Chrome 管理コンソールなど、BOM付きUTF-8 のCSVしかインポートを受け付けないシステムがあるようです。Windows なら「メモ帳」で UTF-8 保存すると BOM付きになります。ただし Windows10よりBOMなしUTF-8がデフォルトになったようなので、明示的にBOM付きUTF-8での保存が必要です。macOS なら、CotEditor あたりを使えば、BOMあり、なしを選択できます。

Chromebook では Chromeブラウザしかつかえなーい！

下記の事例のように、Chromebook ではBOM付きUTF-8に保存することが難しい模様。

• Chrome管理コンソールで、ユーザーの表示が文字化けします （G Suite Help Forum）

Google スプレットシート > 形式を指定してダウンロード > カンマ区切りの値（.csv 現在のシート）

は、BOMなしのUTF-8なのでした。
下記のようにバイナリエディタ（HexEdit）でみるとBOMがないのが分かります。



ちなみに BOMありの場合には、先頭が EF BB BF で始まります。
参考：Byte_order_mark  (Wikipedia)



CotEditor で見ても BOMなしですね。



BOMありの場合

じゃあ Google Apps Script で実装できるのかな

と思って

• Google Apps Script でファイルを保存してみる（Ghost in the web）

を参考に作成してみました。ちょっとハマったところがあったり、バタバタしていたので、小一日掛かってしまいました。

• ツール：https://github.com/kimipooh/outputSheetToCsvFile

以下、ツールの利用方法です。

実装 - Google スプレットシートに CSV出力のカスタムメニューを追加する

今回３種類追加してみました。

• OUTPUT to CSV カスタムメニューを追加
1. Shift＿JISコードの CSVを保存
2. UTF-8（BOM付き）の CSVを保存
3. UTF-8（BOM無し）のCSVを保存

なお Microsoft Excel で文字化けせずに開くことができるのは、「１」「２」です。つまりは Googleスプレットシートの標準機能で CSV保存したものは Microsoft Excel で開くと日本語が文字化けしまーす。

STEP 1. Google スプレットシートを作成

適当に日本語テキストを入力します。

な感じ。

STEP 2. スクリプトエディタを開き、サンプルコードをコピー＆ペーストします。

1. ツール > スクリプトエディタを開きます。

2. 中身を、outputSheetToCsvFile.gs (GitHUB）にあるコードに置き換えます。

3. 保存します。

な感じですね。

STEP 3. コードのいくつかの箇所を変更する

var drive = DriveApp.getFolderById('=== Folder ID ===');

=== Folder ID === は、スプレットシートを保存した Googleドライブのフォルダに移動して、URLの

https://drive.google.com/drive/u/0/folders/=== Folder ID====

の部分をコピー＆ペーストで入れて下さい。

var base_fileName = "aaa";

ファイル名のプレフィックス名を設定してください。

aaa_UTF8.csv、aaa_Shift_JIS.csv などで保存されます。

var entries 内の name 変数の値を変更することで、

上記の名称を変更できます。

spreadsheet.addMenu("OUTPUT to CSV", entries);

カスタムメニューの名称を変更できます。

※上記変更をしたら保存してください。

STEP 4. Google スプレットシートを再読込する

開いていた スクリプトエディタが閉じ、カスタムメニュー「OUTPUT to CSV」が表示されているはずです。



のように、それぞれ選択してみましょう。
最初の一度だけ、「承認が必要」画面がでてきます。


「続行」ボタンを押すと



のように警告がでますが、自分がつくったものですので「詳細」リンクをクリックし、
「outputSheetToCsvFile（安全ではないページ）に移動」
リンクをクリックしてください。



そうすれば、このコードが Googleドライブへアクセスしてよいかどうかの許可をきいてくるので「許可」ボタンを押します。

※もし警告を出したくないなら、下記を参考に申請してみてください。
https://support.google.com/cloud/answer/7454865

以上で完了です。
３つのCSV出力をすると下図のように、３つのファイルができると思います。

• aaa_UTF8N.csv = UTF-8 (BOMなし）
• aaa_UTF8.csv = UTF-8 (BOMあり）
• aaa_Shift_JIS.csv = Shift_JIS

です。

STEP 5. 正しくコンバートされているか確認する

aaa_UTF8N.csv

下記のようにバイナリエディタ（HexEdit）でみるとBOMがないのが分かります。



CotEditor で見ても BOMなしですね。

aaa_UTF8.csv

ちなみに BOMありの場合には、先頭が EF BB BF で始まります。
参考：Byte_order_mark  (Wikipedia)

BOMありの場合

aaa_Shift_JIS.csv

のように正しく判定されていますね！！

2018年4月13日 @kimipooh

【GAS】Google Analytics データを Googleスプレットシートに集約し、PDFでメール添付する方法

最近 Google Script を使ったプログラムにハマりつつあります。

さて今回は、

1. （前半）複数ある Google Analytics データの一部を Googleスプレットシートで一括取得
2. （後半）一括取得した Googleスプレットシートの特定シートデータをメール本文（HTML）と PDF にして添付したメール送信

について紹介したいと思います。

毎回ログインして調べるのはやってられない！

ということと、問い合わせや登録関係に Googleフォーム + Google スプレットシートを使い始めて、Google Script でゴニョゴニョしだしたので、やってみるか！というノリでやってみました。

ようするに、ウェブサイトのアクセスログが知りたい！っていう度に、Google Analytics にログインして調べるのはやってられない！ そこはまぁ簡単に実装できたけど、メール添付できたら更に楽だなぁというノリです。

（前半）複数ある Google Analytics データの一部を Googleスプレットシートで一括取得

やりかたはネットで調べたら結構でてきます。

• Googleアナリティクスの分析はスプレッドシートのアドオンで全自動化しよう（文武両道）

が一番参考になりました。

その他の参考サイト

• Googleアナリティクスの面倒なレポート作成をGoogleスプレッドシートで解消！ （第1回）（Impress）
• Googleアナリティクスの自動デイリーレポート＆よく使うMetrics一覧（いつも隣にITのお仕事）
• Google AnalyticsからGoogleスプレッドシート上にレポートを出す方法（bambooshot TV）

今回、「2018年4月1日から一日前までの自動取得」を設定してみます。

【G Suite専用】待ち遠しい、 Googleスプレットシートに多数の枠線が追加されるらしい

Google スプレッドシートでテキストの回転や会計の表示形式などをサポート（G Suite アップデートブロク）によれば、

• 賃借対照表などの財務諸表でよく使われるさまざまな太さの枠線や二重の枠線などを選択できます

とあります。これ結構便利そうだなーと思いました。
テキスト回転は、一般 Googleアカウントのスプレットシートではできたのですが、これがG Suiteにもやってくるということなのでしょうかねぇ。あるいは同時リリースだったのか...。

ただ計画的リリース設定にしているので、枠線などの追加は 3月6日までお預け〜状態です。。。

2017年2月24日 @kimipooh

【備忘録】Google Script を使った G Suite の Google Group for Business への一括メンバー登録方法について

以前、

• Google Apps Manager (GAM) を使っていろいろやろう

でG Suiteからユーザーやグループ情報を取得する方法はメモっていましたが、すっかり失念してました。そのときにはあまり必要に感じてなかったのですが、Google Group for Business に1000を超えるメンバーを一括追加する必要がでてきました。Google Group for Business へのメンバー追加を G Suite の管理コンソールから行う場合には、25名ずつしか登録できません。まぁ出来なくはないですし、これまで 300ぐらいならチマチマとしてました。ただ今後チマチマやったいられないのと 1000を超えてくるとやる気が失われます。ので、API経由で登録せざるを得ません。そこでいろいろ試してみました。

今回は、Google Script 編になります。

Google Groups へのメンバー移行 を参考に

Google Script を使った方法を試してみた！

数百程度ならまぁ、これでも問題ないと思います。2000ぐらいも、まぁいけなくはないかなーという感じです。ただし一括は200〜300ぐらいが限界です。

1. 6分以上かかる実行はエラーになる
2. 処理が遅い
3. ２回目の実行時にエラーがでることがある
   　※スクリプトを閉じて開き直して実行しなければならなかったり。。。
   
   （var group = AdminGroupsSettings.Groups.get(groupEmail); でエラー）

とはいえ、Googleスプレットシートで手軽に登録できる便利さは中々のものです。
ので紹介はしておきます。スクリプトについては、Google Groups へのメンバー移行 (http://net-newbie.com/apps/GoogleGroupsMigration.html) を参考に、そこに掲載されていたスクリプト「Mailman2Groups.gs」（GitHUB）をフォークして、若干カスタマイズして「Mailman2Groups.gs (kimipooh）」（GitHUB）公開しました。

利用のための準備（管理コンソール）

1. G Suite 管理コンソールにログイン
2. セキュリティ > API リファレンス > API アクセス > APIアクセスを有効にする にチェックをいれて保存します。

これで、G Suite アカウントより、G Suiteの Admin API が利用できるようになります。

普段許可していないのであれば、 一時的にオンにして使用し終わったらオフにすればいいでしょう。

利用のための準備（Google スプレットシート）

1. G Suiteの管理者（管理コンソールにログインできる）アカウントにて、Googleドライブにアクセスし、Googleスプレットシートを新規作成
2. ツール→スクリプトエディタをひらいて、「Mailman2Groups.gs (kimipooh）」（GitHUB）のコードを入れて保存
3. APIの有効化
1. リソース＞Developers Consoleプロジェクトの作成
   （Admin SDK、Groups Settings API）
2. リソース＞Googleの拡張サービス
   （Admin Directory API、Group Settings API）

上記は、Google Groups へのメンバー移行 (http://net-newbie.com/apps/GoogleGroupsMigration.html) を参照してみてください〜。

利用方法

Step 1. グループ情報とメンバー情報の準備

上で作成した Google スプレットシートにおいて、Google Group for Business の作成とメンバー追加のための情報を付与します。

A列に項目を書き、B列に値を書きます。
email = グループ名

name = グループの表示名

description = グループの説明

owners = グループのオーナー（一人以上いないと、スクリプト実行時にエラーがでて動きません。もし何らかの理由でグループのオーナーを不在にしたければ、管理コンソールより、グループのオーナーを削除してください。オーナーを削除してもG Suite管理者はグループを管理できます。）

members = 追加したいメンバーのメールアドレスを列挙してください。

Step 2. グループ作成とメンバー追加（Google Scriptの実行）

1. ツール > スクリプト エディタを開く
2. function addGroupMembers(groupEmail, users)  関数内の
    var start = 1;
    var end = 250;
   をチェックし、一度に登録する数を調整する。
   手元では250ぐらいなら一括で行けました。300だと失敗することがありました。
3. 実行 > main でグループ作成とメンバー追加の処理を開始
   
   　※6分以内に終了するか、終了しきれずにエラーがでるか、Backgroud error がでるかのどれかかなと思います。
1. 終了しきれずにエラー：start と endの差をもう少し少なめに設定して再実行してみてください。
2. Background error：一度スクリプトエディタを閉じて、再度 Googleスプレットシートの ツール > スクリプト エディタより開いて、実行 > main で再実行してみてください。
4. エラー無く終了すれば、
    var start = 251;
    var end = 500;
   など処理する行を指定しないそて、再度実行（実行 > main）してみてください。
5. 1200 登録数があって、ラストは
    var start = 1000;
    var end = users.length;
   など、最終行（users.length）を指定すると処理が少しは速まるかなーと思います。

Step 3. グループの設定

初期設定で、「招待されたメンバー以外投稿不可」にしていますが、その他の細かい設定は入れ込んでいません。ので適宜設定するとよいです。初期設定をスクリプトにて変更したければ

function updateGroupSettings(groupEmail) {

  var group = AdminGroupsSettings.Groups.get(groupEmail);

  group.replyTo = "REPLY_TO_LIST";      // 返信はMLへ

  group.allowExternalMembers = "true";  // 組織外メンバーを許可

  group.isArchived = "false";           // メッセージをアーカイブしない

  group.whoCanJoin = "INVITED_CAN_JOIN";    // 招待した人しか登録できない（kimipooh）

  AdminGroupsSettings.Groups.patch(group, groupEmail);

} // updateGroupSettings()

にいろいろ追加すればいいでしょう。

何を追加できるかは、

• Google Apps Admin SDK > Group Settings API 

を参照にしてみてください。

たとえば、

•   group.whoCanJoin = "INVITED_CAN_JOIN";  

は、

entry/apps:whoCanInvite/text()（string）

==

ALL_MEMBERS_CAN_INVITE -- Managers and members can invite a new member candidate.

ALL_MANAGERS_CAN_INVITE -- Only managers can invite a new member. This includes the group's owner.

NONE_CAN_INVITE -- No one can invite a new member candidate.
===

となっているので、この３つの値を文字で指定することになるので上記のような設定になります。

entry/apps:maxMessageBytes/text() （integer）＜メッセージサイズの設定＞
は、
===
The maximum size of a message, which, by default, is 1Mb.
===

と値は「整数」でといっているので、

• group.maxMessageBytes = 25;

とかにすると、　25Mb 指定になるのかな〜って思いますね。  

まぁ、とにかくこれで、2000ぐらいまでなら、まぁまぁ出来なくはないね、、って感じじゃないかなと思います（遅いけど）。

2016年11月17日 @kimipooh

Google Apps Scriptを使って Googleスプレットシートのセル内連続改行を１つにしてみよう！

OS9Google Apps Scriptはほぼ触っていませんでしたが、Googleスプレットシートを割と使うようになってきたので、少しずつ触ってみようと思っています。

今回は、 Google Apps ヘルプフォーラムで質問のあった、

• スプレッドシートで連続改行を一つにしたい（Google Apps ヘルプフォーラム）

をチャレンジしてみたいと思います。

※正規表現について、指摘を受けたので修正（2016/5/18）

自分で関数を作る

いろいろ探すより、これぐらいなら自分で作った方が早いですよ〜

でも既存で関数ありましたけど...（後述）

【備忘録】Googleスプレットシートに「テキストを列に分割」機能が実装！！！

もう3日前の情報なのですが、これはとても朗報です！！
カンマ（カスタムの区切り文字もOK）のテキストデータを、列に分割してくれるっていうんですから！

これでデータ・ソースをコピ＆ペーストで綺麗に表にしてくれるわけですしね！



適当な場所にコピーして、右端のアイコンをクリックし「テキストを列に分割」するといけます。

関連情報

• Google スプレッドシートでテキストを列ごとに分割（Google Appsアップデートプログ）
• セルのテキストを複数の列に分割する（Googleドキュメントエディタヘルプ）

カスタムの区切り文字の指定

カンマの場合には、「テキストを列に分割」を選択することで、即座に列が分割します。
それ以外は指定が必要です。



最初に「:」で区切って、後からスペース区切りにすることだって簡単ですよね。
ただ分割した先の列にデータがあっても上書きされるので注意（まぁGoogleスプレットシートの変更履歴の詳細版で戻るとは思いますけど... ファイル→変更履歴を表示→詳細な版を表示）。上記のようにするためには、一旦「:」で区切って、さらにスペースで区切りたい場合には、その列分だけあけておいてやるって形になるかと思います。

これがあるとログの簡易整理とかも捗りそう...

2016年3月7日 @kimipooh