【GAS】古いGmailを定期的にゴミ箱へ移動

以前からずっと動かしていたのですが、GASで定期的に古いメール、特に「update」カテゴリや「Promotions」カテゴリに入るメールをゴミ箱へ移すScriptを見直しました。

まだ無駄が多い感じがしますが、現在はこんな感じで30日以上前に受信した「update」カテゴリに入っているメールをどんどんゴミ箱へ移動させています。

var maxDate = new Date();
  maxDate = maxDate.setTime(maxDate.getTime() - (30 * 24 * 60 * 60 * 1000));
  var maxDateFormat = new Date(maxDate);
  var mDate = maxDateFormat.getMonth() + 1;
  if(mDate < 10){mDate = "0" + mDate};
  var dDate = maxDateFormat.getDate();
  if(dDate < 10){dDate = "0" + dDate};
  var maxDateText = maxDateFormat.getFullYear() + '/' + mDate + '/' + dDate;
  var threads = GmailApp.search('category:updates before:' + maxDateText);
  for (var i = 0; i < threads.length; i++) {
      threads[i].moveToTrash();
      Utilities.sleep(1000);
}

あとは定期的にScriptが動いてくれればOK。
sleepは入れなくても構わないと思いますが、以前頻繁に削除を走らせていた時に、「やり過ぎ」的なエラーメッセージが飛んできたからだったと思います(笑

まぁGmailなので削除する必要性も無いといえば無いのですが、気になる方は定期的に削除をしても良いと思います。

「GmailApp.search」の中にメール検索条件を入れているわけですが、例えば「label:read」を入れて既読メールだけを削除したり「has:attachment」を指定して、添付ファイルの付いているメールだけを削除しても良いですね。

Google Spreadsheetで自分用の簡単なメール送信予約スクリプトを作る。

Google SpreadsheetではExcelで使える数式でガツガツ作りこむことも可能ですが、その場合シートがどうしても重くなりがち。

Query関数で引っ張ったほうがサクッといくことがあります。
今回はQuery関数を使って、前から作っておきたかった簡単なメール送信予約シートを作りました。

今までChromeの拡張を使ったりしてたのですが、有料ですとかフリーミアムモデルで途中から有料ですとか言われたものの、そんなものSpreadsheetで作れるのに有料とかいい商売してるなーと感じてました。

Spreadsheetに簡単なフォームを生成して、あとは1分おきとか5分おきにScriptを回しておくだけ。

1時間くらいで作ったというのもあって、変数定義の部分の変更くらいはいれようかなぁ。

ま、こんなスクリプトを書いてみました。

var sheetNumber = 1
function sendEmails() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[sheetNumber - 1];
  var now = new Date();
  var nDate = now.getYear() + "/" + parseInt(now.getMonth() + 1) + "/" + now.getDate();
  var nTime = now.getHours() + ":" + now.getMinutes() + ":00";
  sheet.getRange("J1").setValue(nDate);
  sheet.getRange("K1").setValue(nTime);
  sheet.getRange("A1").setFormula('QUERY(\'Form Responses 1\'!A:F;\"select * where E = date \"&text(J1,"\'\'YYYY-MM-DD\'\'\")&\" and F = timeofday \'\"&K1&\"\'\")');
  var rows = sheet.getLastRow();
  var dataCount = rows - 1;
  var emailAddress,subject,message,dataCount2;
  if(dataCount > 0){
  for(var i = 0 ; i < rows-1 ; i++){
    emailAddress = sheet.getRange(i+2,2).getValue();
    subject = sheet.getRange(i+2,3).getValue();
    message = sheet.getRange(i+2,4).getValue();
    MailApp.sendEmail(emailAddress, subject, message);
  }
    
    var latestDate = sheet.getRange(rows,5).getValue();
    var latestTime = sheet.getRange(rows,6).getValue();
    sheet.getRange("M1").setValue(latestDate);
    sheet.getRange("N1").setValue(latestTime);
    sheet.getRange("A1").setFormula('QUERY(\'Form Responses 1\'!A:F;\"select * where E = date \"&text(J1,"\'\'YYYY-MM-DD\'\'\")&\" and F > timeofday \'\"&K1&\"\'\")');
    rows = sheet.getLastRow();
    dataCount = rows - 1;
    sheet.getRange("A1").setFormula('QUERY(\'Form Responses 1\'!A:F;\"select * where E > date \"&text(J1,"\'\'YYYY-MM-DD\'\'\"))');
    rows = sheet.getLastRow();
    dataCount2 = rows - 1;
    if(dataCount == 0 && dataCount2 == 0){
      DeleteSheet();
    }
  }
}
function DeleteSheet() {
    SpreadsheetApp.flush();
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheets()[sheetNumber];
    var datarange = sheet.getDataRange();
    var lastrow = datarange.getLastRow();
    var values = datarange.getValues();
    for (i=lastrow;i>=2;i--) {
        var tempdate = values[i-1][2];
        {
            sheet.deleteRow(i);
            function deleteAllResponses() {}
        }
    }
}
function deleteAllResponses() {
  var form, urlForm = SpreadsheetApp.getActiveSpreadsheet().getFormUrl();
  if (urlForm) {
    form = FormApp.openByUrl(urlForm);
    if (form) form.deleteAllResponses();
  }
}

追記1)シートが重くならないように一定条件でデータを削除するフローを追加と、それに伴う一部ソースを修正しました。

Google AnalyticsデータをGoogle Spreadsheetで取得して集計する 【2013年α版script】無料でデータを取得したい！

1ヶ月ほど前から、今までの方法でGoogle SpreadsheetからAnalyticsのデータを取得することが出来なくなっています。
完全にOAuth2認証が必須になっているようです。

海外無料ツールも最近有料になったりして、自分で何とか作れないものかと思い、まだ酷い状態ですが、簡単にScriptを作ってみました。

■Scriptを見る
※閲覧はGoogleアカウントが必要なようです。

エラー処理が中途半端だったり、色んな制約が発生しています。
改善の余地がありまくる無駄なScriptですが、少しずつ改良出来ればと思っています。

■使い方

1.Google Spreadsheetを新規に作成して、「tool」から「Script editor」を開く

2.Scriptを貼る

3.適当な名前を付けてScriptを保存した後に、「File」から「Upgrade authorization experience」を選択する。
※イケてなさすぎる点！！

4.「Yes」を選択。
要するに、Scriptを実行する人ごとに認証が求められるということ。。。これ、イケてない。。。
※一応、Googleのヘルプを見ている限り、この操作無くAPI Keyを保存しておけばイケるはずなんですが、色々試しても解決出来なかったので・・・

5.「Resources」から「Use Google APIs」を選択

6.Google APIs Servicesの中からAnalyticsをon！

7.「on」になったことを確認したら、フッターの「Google APIs Console」のリンクをクリック
※本当はauthorization experienceを同意していない場合、ここでAPI Keyを入力する必要がある。

8.飛んだ先のページでAnalytics APIを「on」に。

9.再びScriptの画面に戻って、一度ScriptをRunする。

10.ここで認証が求められるので、「Authorize」を選択。

11.「アクセスを許可」で認証通過！

■使い方
数式として使います。

式の形式は
getData(profileId, metrics, startDate, endDate, filters, dimensions, segment, sort, startindex,
maxresults)

赤字は必須項目です。

それ以外は一応、必須じゃないので省略して構わないのですが、式を動かしてみた際、色々とエラーが発生したので、裏側ではダミーで値をもたせていたりします。

項目の説明はGoogleのヘルプページが一番詳しいです。

ちなみにprofileIdはAnalyticsのURLからも分かりますが、ツールまたはScriptを組んで調べたほうが早いかもしれません。

URLの場合は「～/visitors-overview/a[account ID]w[web property ID]p[profile ID]/」こんな構造になっていますので、「profile ID」を拾ってしまえばOKです。

ツールやScriptに関しては私は有料ツールの無料1ヶ月期間に取得してしまいました。


■残りの項目説明
【metrics/dimensions】
Googleのこのページに書かれている左側がdimension、右側がmetricsです。

Metricsは結果として欲しいデータ、dimensionは切り口みたいなものです。
例えば、先週一週間の日次セッション数を出力したい場合、metricsに「visits」(セッション数)、dimensionに「date」(日次)を指定すれば完了です。

【startDate、endDate】
解析期間のはじめと終わり

【filters】
dimensionまたはmetricsのフィルタです。
filterで使える表現については、Googleのヘルプを参照してください。

【segment】
Advanced Segmentです。「すべて」は「-1」だったり、自分で作ったカスタムセグメントは数字の桁数が多いIDをそれぞれ持っています。

【sort】
単純なソートです。
複数のkeyを入れられますが、先頭から順に第1ソートキー、第2ソートキーとなるようです。

※Spreadsheetで集計していていつも思う不満が、ウェブで出来る「加重」ソートが出来ない事です。ウェブサイトのURL的には「smart」とか付いていたような気がしますが、このAPIを使ったソートでも実装して欲しいなぁ。。。

【startindex/maxresults】
開始位置と表示件数ですが、気にすべきは表示件数の方かもしれません。
ただ、Spreadsheetと連携させたデータ取得では、あまりデータ数を取得することが出来ないと考えておいたほうがいいです。


■制約事項
色々ありますが、、、最大の制約事項は「認証」をするため、そのSpreadsheetのアカウントに解析したいAnalyticsのユーザー権限または管理者権限が付与されていなければならない事でしょう。

一応gmailアカウントだけでなく、企業が使うAppsでも利用できましたので、その辺りでの差別はありません。


■最後に！！
言い訳ですがｗ
僕、エンジニアじゃないんでソースの中途半端感は許して！ｗ

でも、色々とご指摘いただけると嬉しかったりします。

【GAS】Gmailから本文を取得してSpreadsheetへ反映させる(メモ)

シートを動的に扱うことが多いので、グローバルにシートナンバーを定義してしまっていますが、Gmailの本文を取得して、indexOfとかでごにょごにょするのは結構楽しいです。

var sheetNumber = 1;

function dailyGmailGetBody() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[sheetNumber - 1];
  var threads = GmailApp.search('subject:"●●" from:"●●"',0,1); //検索条件を指定して、リストの1つ目のみを取得する

  var messages = threads[0].getMessages(); //メールの親スレッドメッセージを指定
  var tBody = messages[0].getBody(); //メッセージの本文取得

}