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

みなさん、こんにちは!
タカハシ(@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の新規記事をスプレッドシートにリストしチャットワークに送るシステム

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

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

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

VLOOKUPで検索流入数と平均滞在時間を求める

公開されている記事の一覧がリストされておりまして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」という値がないものについて

  1. チャットワークに送る文字列strBodyを生成
  2. チャットワークにメッセージを送る
  3. 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)"); //平均滞在時間

となります。

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

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

まとめ

以上、GoogleAppsScriptに数式を記入するsetFormulaR1C1メソッドについて、またR1C1形式での表記方法についてお伝えしました。

これにてブログの低品質コンテンツをあぶり出すシステムを作成が、ほっておいても自動で更新される状態になりましたね。

スプレッドシート、GoogleAnalyticsアドイン、GoogleAppsScriptと組み合わせていますが、組み合わせることで色々なことが自動化できると思います。しかも無料です…!

ぜひ、また素敵なシステムが思いついたらお伝えできればと思います。

どうぞお楽しみに!

連載目次:SEO的に低品質なコンテンツなコンテンツを自動であぶり出す

  1. GoogleAnalyticsアドオンを活用して自動で低品質コンテンツをあぶり出す
  2. 新規投稿も自動で追加更新されるWordPress記事一覧スプレッドシートの作り方
  3. スプレッドシートで別ファイルのデータをVLOOKUPで取得する方法
  4. Google Apps Scriptで数列を記入する方法とR1C1形式について
タイトルとURLをコピーしました