Google Apps Scriptで数列を記入する方法とR1C1形式について

photo credit: Superbokehtheorie via photopin (license)

みなさん、こんにちは！
タカハシ(@ntakahashi0505)です。

GoogleスプレッドシートとGoogleAnalyticsアドオンを使って、ブログの低品質コンテンツを自動であぶり出すシステムを作成してきました。

前回は別のスプレッドシートからのデータをVLOOKUPで取得する方法についてお伝えしまして

スプレッドシートで別ファイルのデータをVLOOKUPで取得する方法

GoogleスプレッドシートではIMPORTRANGE関数を使って別の異なるスプレッドシートからVLOOKUPでデータを引っ張ってくることができますので、その方法についてお伝えしていきます

これでブログの各記事について過去30日間の検索流入数と平均ページ滞在時間を自動で出力することができました。

ですが、一点だけまだシステムとしては不足している部分がありました。

というのも、こちらの記事で紹介している通り

Google Apps ScriptでWordPressの更新情報をチャットワークに送る

今回はGoogle Apps ScriptとIFTTTを活用してWordPressの記事公開のお知らせを自動でチャットワークに送るシステムを作りましたので、その方法についてお伝えします。

新しい記事が投稿されたらIFTTTを使ってスプレッドシートに追加されるようにしたのですが、検索流入数と平均ページ滞在時間の計算式だけ反映されない状態なのです。

今回はGoogle Apps Scriptで数式を入力する方法です。setFormulaR1C1というメソッドとR1C1形式でのセル指定の方法についてお伝えしつつ、本システムを完成させたいと思います。

おさらい：WordPressの新規記事をスプレッドシートにリストしチャットワークに送るシステム
1. IFTTTでスプレッドシートに追加されない数式をどうするか
2. 新規投稿をチャットワークに送るGoogle Apps Scriptを利用する
Google Apps Scriptでスプレッドシートに数式を記入する
1. setFormulaR1C1メソッドで数式を記入する
R1C1形式での数式の書き方
まとめ
連載目次：SEO的に低品質なコンテンツなコンテンツを自動であぶり出す

おさらい：WordPressの新規記事をスプレッドシートにリストしチャットワークに送るシステム

では、今回修正を加えていくスクリプトの内容についておさらいをしておきたいと思います。

IFTTTでスプレッドシートに追加されない数式をどうするか

まず、スクリプトが動作するスプレッドシートはこちらです。

公開されている記事の一覧がリストされておりましてIFTTTによって新規記事も自動で追加されます。

H列、I列に検索流入数と平均ページ滞在時間が数式で出力されているようになっているのですが、IFTTTでは数式を登録するような機能はありませんので、新規記事についてH列、I列は空のままになってしまいます。

そこを何とかしようというのが今回のお題です。

新規投稿をチャットワークに送るGoogle Apps Scriptを利用する

それで、WordPressの新規記事が投稿されたときに、チャットワークにその内容を通知するというGoogle Apps Scriptを作成したのですが、それを利用します。

チャットワークに送るついでにH列、I列の数式を書き込む処理もしてもらおうという魂胆です。

では、そのスクリプトもおさらいをしておきます。

/* WordPressで公開された記事をチャットワークで宣伝する */
function cwNewPost() {

  var mySheet=SpreadsheetApp.getActiveSheet(); //シートを取得
  var maxRow=mySheet.getDataRange().getLastRow(); //シートの使用範囲のうち最終行を取得

  /* 全ての列について繰り返し */
  for(var i=1;i<=maxRow;i++){

    //6列目がnullであればチャットワークにメッセージを送信
    if(mySheet.getRange(i,6).getValue()==""){

      /* チャットワークに送る文字列を生成 */
      var strBody = "[info][title]「いつも隣にITのお仕事」新着記事公開のお知らせ[/title]" +
        mySheet.getRange(i,2).getValue() + "n[hr]" + //記事タイトル
        mySheet.getRange(i,3).getValue() + "n" + //URL
        mySheet.getRange(i,1).getValue() + "[/info]"; //公開日

      /* チャットワークにメッセージを送る */
      var cwClient = ChatWorkClient.factory({token: 'XXXXXXXXXXXXXXXX'});　//チャットワークAPI
      cwClient.sendMessage({
        room_id:XXXXXXXX, //ルームID
        body: strBody
      });

      /* 送信済みのツイートには6列目に"send" */
      mySheet.getRange(i,6).setValue("send");

    }
  }       
}

スプレッドシートの記事一覧についてF列に「send」という値がないものについて

チャットワークに送る文字列strBodyを生成
チャットワークにメッセージを送る
F列に「send」と記入

という処理です。

今回、その後の処理として、H,I列に数式をコピーするという処理を追加していきます。

Google Apps Scriptでスプレッドシートに数式を記入する

setFormulaR1C1メソッドで数式を記入する

Google Apps Scriptでスプレッドシートの任意のセルに数式を記入する場合は、setFormulaR1C1というメソッドを使います。

書き方は

Rangeオブジェクト.setFormulaR1C1(数式)

です。数式はダブルクォーテーションで囲います。

R1C1形式での数式の書き方

数式としては、1行目であればG列に

=RIGHT(C1,LEN(C1)-20)

H列、I列にそれぞれ

=VLOOKUP(G1,IMPORTRANGE(“スプレッドシートID”,”LPへの検索流入!A:B”),2,FALSE)

=VLOOKUP(G1,IMPORTRANGE(“スプレッドシートID”,”滞在時間!A:B”),2,FALSE)

と入力する必要があります。

しかし、setFormulaR1C1メソッドではR1C1形式で数式を書かないといけません。

R1C1形式とは、基準となるセルからの行（R:Row）、列（C：Column）それぞれの移動数によって表現する形式です。移動数は[]内に数値で記述します。

例えば基準セルから、行方向下に3行、列方向左に2列ならば「R[3]C[-2]」と表します。

従いまして、上記G列～I列に記述するステートメントは

/* 新しい行に数式をコピーする */
mySheet.getRange(i,7).setFormulaR1C1("=RIGHT(RC[-4],LEN(RC[-4])-20)"); //pagepath
mySheet.getRange(i,8).setFormulaR1C1("=VLOOKUP(RC[-1],IMPORTRANGE("スプレッドシートID","LPへの検索流入!A:B"),2,FALSE)"); //LP検索流入セッション数
mySheet.getRange(i,9).setFormulaR1C1("=VLOOKUP(RC[-2],IMPORTRANGE("スプレッドシートID","滞在時間!A:B"),2,FALSE)"); //平均滞在時間

となります。

「"」はダブルクォーテーションのエスケープ文字ですね。

これをスクリプトの最後に追加することで、チャットワークへの通知を行った段階で数式も入力されることになります。