mixiのイベントデータをGoogleフォームとスプレッドシートに移行した話

この年末を利用して、mixiのコミュニティ内のデータを移行させるということをやっていました。やりたいことは簡単な内容でしたが、経験不足という事もあっていろいろと躓いたこともありますので、顛末をブログにまとめておくことにします。

データ移行を行うことにした理由

mixiのランニング関連のコミュニティで、あるイベントを開催していました。

イベント内容は、コミュニティに参加しているメンバーが走った距離をイベントページに書き込んで合計し、全員の力を合わせてある目的地まで行こうというものです。最初のうちは全員のモチベーションが高かったのですが、mixiのアカウントやログインパスワードを忘れた、走行距離や累計の距離を計算するのがめんどくさいといった理由から、徐々に書き込みが減っていきました。最終的には私ともう1名ぐらいになってイベント自体が立ち行かなくなってしまったため、イベントの進め方を変える必要がありました。

移行先の選定

仕事でお問い合わせフォームを作るという経験があったので、同じようにGoogleフォーム + スプレッドシートを利用することにしました。

データの用意

mixiからのデータの抽出は、経験のあるnode + puppeteerを利用することにしました。
イベントページのHTMLの構成は分かりやすかったので、「これなら簡単に抽出できそうだな」と思っていたのですが、mixiへのログインで問題が発生しました。

f:id:ta9mi3:20200101200247p:plain

おそらく、以下の記事にあるように不正ログイン対策に引っかかっていると思われます。

sns.mixi.co.jp

User Agentをチェックしているのかなと思いましたが、変えてみても結果は変わらずでした。「さて、どうしたものか」と考え、「ログインできないならログインしなければいいじゃん」ということでイベントページそのものをPCに保存して、ローカルファイルを参照することにしました。手作業でmixiのイベントページにて全てのコメントを表示させ、イベントページを順番にPCに保存していきました。

データの抽出

ローカルファイルからPuppeteerでデータを取り出します。
コメント番号、投稿者、投稿日時、コメントの4点をcsvファイル化していきました。この作業はあまり難しくありませんでした。

for (var itemIdx = 0; itemIdx < itemList.length; itemIdx++ ){

	const NoAndName = await itemList[itemIdx].$$('a[class="COMMUNITY_EVENT_eventList__nameLink"]');

	const itemNo = await (await NoAndName[0].getProperty('textContent')).jsonValue();
	const userName = await (await NoAndName[1].getProperty('textContent')).jsonValue();
	const itemDate = await (await (await itemList[itemIdx].$('p[class="COMMUNITY_EVENT_eventList__date"]')).getProperty('textContent')).jsonValue();
	const itemText = await (await (await itemList[itemIdx].$('div[class="COMMUNITY_EVENT_eventList__textArea"]')).getProperty('textContent')).jsonValue();

	fs.appendFile('c:\\temp\\earth4.csv', itemNo + "," + itemDate + "," + userName + "," + "\"" + itemText.trim() + "\"\n", (err) => {
		if (err) throw err;
			console.log('done');
		});
}

データの整形

取り出したコメントには「イベント開始からの経過日数、走った距離、走った距離の累計、ひとこと」が書いてあります。いちおうフォーマットは決まっていましたが、ルール通りに記載されていないものや、ひとことしか書かれていないコメントなどがありましたので、自動的ではなく手作業でデータの整形を行っていきました。

整形後のデータの形式は、「投稿日時、投稿者、走行距離、コメント、経過日数、走行距離（累計）、目的地までの残りの距離」というものにしました。

データをスプレッドシートに挿入する

Googleフォームでテストデータを何件か入力し、作成されたスプレッドシートに上記のデータを挿入しました。挿入場所は、Googleフォームで追加されたデータとタイトル行の間になります。

GASの作成

投稿者の負担を考え、Googleフォームでは「投稿者、走行距離、コメント」のみ記載してもらうことにしました。スプレッドシートにデータを追加された場合に、「経過日数、走行距離（累計）、目的地までの残りの距離」を自動計算するように、GAS(Google Apps Script)を作成しました。

GASはあまり経験はありませんが、ExcelのVBAはよく使っていますので、スプレッドシートの操作に対する理解はあまり悩まなかったと思います。

公開と問題の発生

Googleフォームで追加したデータは、基本的にスプレッドシートの下に追加されていきます。そのため、スプレッドシートを閲覧した時に最下行まで行かないと、登録した自分のデータを確認できないという問題がありました。対応として、以下のことを考えました。

スプレッドシートを投稿日時(A列)の降順にする
Googleフォームによりデータが追加された場合、いちど投稿日時の昇順に直し、データの整形が終わったら降順に戻す

ところが、うまくいきませんでした。

問題の調査と対処

降順に戻した後、「経過日数、走行距離（累計）、目的地までの残りの距離」を自動計算するのですが、なぜかセルが空白となってしまいます。調査をしたところ、Googleフォームから受け取ったデータが最下行ではなく、なぜか必ず8行目に挿入されるという事象が発生していました。

うーん……

再び考え、以下の対応をすることにしました。

スプレッドシートは投稿日時の降順のままとする
Googleフォームから受け取ったデータの行番号を調べ、該当の行をスプレッドシートの2行目に移動させる（1行目はタイトルなので2行目からがデータです）

// 該当データを一番上の行に移動させる
var inputRange = e.range;
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];
var range = sheet.getRange(inputRange.getRow(), 1);
sheet.moveRows(range, 2);

経過日数などを計算させる

この対応で上手く動作するようになったので、とりあえず良しとします。
8行目に追加されてしまう原因は、おそらくGoogleフォームが持っている内部の行番号が影響しているのかなと思うのですが、原因が分かる人がいたら教えて頂きたいです。

最後に

ちょっと躓いたところなどを、何年か後の自分のためにまとめておきました。
まあ、mixiのイベントのコメントの移行をすることは二度と無いでしょうが、スプレッドシートの並び順を変えるとたいへんということだけは覚えておきたいと思います。

後は、イベント参加者がちゃんとGoogleフォームを使ってくれれば……。
なにとぞ、なにとぞー。

IT関連雑記帳

IT関連の話をつらつらと