System Engineer Style | How to Automatically Aggregate 'Likes' on note Using Generative AI

Previously, I aggregated the times I received 'likes' to investigate the time slots when it is easiest to get likes on note.

How do you think I aggregated these likes?

Automatically aggregating 'You've received a like' emails

It is difficult to check 'You've received a like!' notification emails one by one and enter them into Excel.

If it takes 20 seconds to enter one like into Excel, 50 likes would take 1,000 seconds, or approximately 16.6 minutes. For 100 likes, it would take over 30 minutes.

If you continue this every time you post an article, it becomes a significant amount of work time, but with a program, it runs automatically every day at 1:00 AM, so it takes no time at all. Generative AI can be used not only for creating text but also for small automations like this.

The important thing is to clarify 'what you want to do'

Programs and generative AI are a very compatible combination.

If you have basic knowledge of programming, it is useful for checking and correcting generated code, but what is important at the initial stage is not advanced programming knowledge, but clarifying
"what you want to achieve"
.

If you can specifically convey what you want to do and the conditions, you can have generative AI create a draft of the program.

This time, to aggregate likes on note, I automated the following process.

I have set up Gmail to automatically apply a 'note like' label to like notification emails received.

Therefore, I told the generative AI:

I told it that.

Furthermore, since it runs every day at 1:00 AM, the target is limited to emails received between 0:00 and 23:59 of the previous day. Also, since I only need to know the 'subject' and 'date and time' of the email this time, I conveyed that as well.

Since I am using Gmail and Google Sheets, I asked Google's Gemini 2.5.

(Although I have processed the generated program slightly,) the program was generated exactly as expected.

function exportLabeledEmailsOfPreviousDayFiltered() {
  // エクスポートしたいラベル名を入力
  const LABEL_NAME = "noteスキ";

  // 検索クエリを作成
  const searchQuery = "label:" + LABEL_NAME;

  // 結果を格納する配列
  let data = [];

  // ヘッダー行を追加
  data.push(["件名", "日付"]);

  try {
    // 指定したラベルを持つメールスレッドを検索
    // ここでは最大500件を取得。大量のメールを処理する場合はページング処理が必要です。
    const threads = GmailApp.search(searchQuery, 0, 500);

    if (threads.length === 0) {
      Logger.log("指定されたラベルのメールは見つかりませんでした。");
      Logger.log("指定されたラベル '" + LABEL_NAME + "' のメールは見つかりませんでした。");
      return;
    }

    // プログラム実行前日の日付範囲を定義
    const now = new Date();
    // スクリプトが実行されるタイムゾーンを取得
    const scriptTimeZone = Session.getScriptTimeZone();

    // 前日の開始時刻 (00:00:00) を計算
    const startOfYesterday = new Date(now);
    startOfYesterday.setDate(now.getDate() - 1);
    startOfYesterday.setHours(0, 0, 0, 0); // 時刻を00:00:00に設定

    // 前日の終了時刻 (23:59:59) を計算
    const endOfYesterday = new Date(now);
    endOfYesterday.setDate(now.getDate() - 1);
    endOfYesterday.setHours(23, 59, 59, 999); // 時刻を23:59:59.999に設定

    Logger.log("検索対象日: " + Utilities.formatDate(startOfYesterday, scriptTimeZone, "yyyy/MM/dd HH:mm:ss") + " から " + Utilities.formatDate(endOfYesterday, scriptTimeZone, "yyyy/MM/dd HH:mm:ss"));

    // 各スレッドを処理
    threads.forEach(thread => {
      // スレッド内のすべてのメッセージを取得し、日付でフィルタリング
      const messages = thread.getMessages();

      messages.forEach(message => {
        const messageDate = message.getDate(); // メッセージの日付を取得

        // メッセージの日付が前日の日付範囲内にあるかチェック
        if (messageDate >= startOfYesterday && messageDate <= endOfYesterday) {
          const subject = message.getSubject();
          data.push([subject, messageDate]);
        }
      });
    });

    if (data.length === 1) { // ヘッダー行のみの場合
        Logger.log("前日のメールは見つかりませんでした。");
        return;
    }

    // 新しいスプレッドシートを作成
    const ss = SpreadsheetApp.create("Gmailラベル '" + LABEL_NAME + "' 前日メール一覧");
    const sheet = ss.getActiveSheet();

    // データをスプレッドシートに書き込み
    sheet.getRange(1, 1, data.length, data[0].length).setValues(data);

    Logger.log("スプレッドシートが作成されました: " + ss.getUrl());

  } catch (e) {
    Logger.log("エラーが発生しました: " + e.toString());
  }
}

I have set the created program to run automatically every day at 1:00 AM using a time-driven trigger in Google Apps Script.

You can also ask generative AI how to set up Google Apps Script, but I recommend that you do not run the generated program as is; instead, check the permissions and processing content, and try it out with a small amount of data first.

Small automations are the gateway to improvement

The task I automated this time is a small one—aggregating 'likes' on note—but when data is continuously accumulated through automation, you can perform the following types of analysis:

If you aggregate them manually every day, you might get tired of it and stop halfway through. However, once you build a system and automate it, you can continue to accumulate data for a month, half a year, or a year.

The value of generative AI is not just in creating text quickly.
"It transforms tasks that were too troublesome to continue into systems that can be sustained."

As a system engineer, I would also like to suggest using it to automate repetitive tasks and improve work efficiency.

For those who want to know more examples of using generative AI

Generative AI can be used in various situations, such as organizing information, analyzing data, creating images, and automating tasks through programming.

I have summarized the examples of generative AI usage that I have actually tried in the following article.