[Organizational AI Utilization #229] Automatically transcribing Gmail content to a spreadsheet using GAS makes it easier to use as source data for AI.

Hello! This is Terada.

Currently, I serve as the Representative Director of the AI Digital Community (ADC), a community for AI practitioners such as AI promoters at digital-related companies, as well as the Representative Director of FURIKAKE Partners Inc., which supports the 'xAI' transformation of client businesses, and AI Portalize Inc., which provides products that support organizational AI utilization, supporting organizational AI utilization from various perspectives!

In a previous article, I believe I discussed how using a spreadsheet as a database makes many things easier to manage.

When building that database, it is extremely convenient to automatically pull data from various sources to create a structure. In this article, I will explain the procedures and methods for summarizing email content into a spreadsheet, including the source code itself.

This article is structured in three parts: how to select items to transcribe, how to design filters for target emails, and the implementation procedure.

※Click here for past GAS-related articles

Step 1: Decide which items to transcribe

The first thing you should do is not write code, but decide 'what columns to create in the sheet.' The thinking behind this selection is to work backward from 'what you want to do with the sheet.'

• If you want to manage responses: Receiving date/time, sender, and subject are sufficient. It is standard to add 'Response Status' and 'Person in Charge' columns to be filled in manually.

• If you want to analyze or aggregate content: You will need information from the body of the email. However, I do not recommend transcribing the entire body. This is because it makes the sheet heavy and results in unnecessary duplication of confidential information. By using a format like 'First 200 characters of the body + link to the original email,' you can achieve both list visibility and security.

• If you want to know if there are attachments: In most cases, it is sufficient to record the 'number of attachments' as a numerical value rather than the file itself.

Based on this approach, the code in this article will transcribe the following six items: Receiving date/time / Sender / Subject / Body (first 200 characters) / Number of attachments / Link to the email.

Step 2: Apply filters to the emails to be retrieved

If you transcribe every email, the volume will become enormous and it will quickly fail. There are two ways to narrow down the targets, and using them together is the best practice.

The first is Gmail search operators. GAS email searches can specify conditions using the exact same syntax as the Gmail search bar. Here are some commonly used ones.

• from:sender@example.com (Filter by sender)

• to:support@example.com (Filter by recipient. Useful for extracting inquiry addresses)

• subject:【Inquiry】 (Filter by keyword in the subject line)

• label:transcription-target (Filter only for emails with a specific label)

• has:attachment (only those with attachments)

• newer_than:2d (within the last 2 days; essential for narrowing down the scope for scheduled execution)

• -subject:advertisement (adding a minus sign at the beginning acts as an exclusion condition)

These become an AND condition when separated by spaces.
Example: "to:support@example.com newer_than:2d -subject:auto-reply".

The second method is to combine it with Gmail's filter feature.
You can create a filter in Gmail settings to "automatically apply a label to matching emails," and then have the GAS side retrieve them using "label:○○." Since the condition management is visible on the Gmail screen, you won't need to touch the code if you want to change the conditions later.

[Key Point]
Before writing the code, first type the same query into the Gmail search bar to confirm that only the intended emails are hit. By verifying here, you can prevent most "not getting enough" or "getting too many" issues after implementation.

Step 3: Implementation

Preparing the Sheet

Create a new spreadsheet for transcription, name the sheet "Mail Log," and enter the headers in the first row.

• Column A: Received Date/Time / Column B: Sender / Column C: Subject / Column D: Body (beginning) / Column E: Attachment Count / Column F: Email Link

Full Code

Open "Extensions" -> "Apps Script" in the spreadsheet, paste the following, and rewrite the settings at the beginning.

// ======== 設定（自分の環境に合わせて書き換える） ========
const SEARCH_QUERY = 'to:support@example.com newer_than:2d'; // 取得条件（Gmail検索演算子）
const SHEET_NAME = 'メールログ';  // 書き込み先シート名
const BODY_LENGTH = 200;          // 本文を先頭何文字まで転記するか（0なら本文列は空にする）

// メイン関数：条件に合う新着メールをシートへ転記（1時間おきトリガーで実行）
function syncMailToSheet() {
  const props = PropertiesService.getScriptProperties();
  const processed = JSON.parse(props.getProperty('processedIds') || '{}');

  const threads = GmailApp.search(SEARCH_QUERY, 0, 100);
  const rows = [];

  threads.forEach(function(thread) {
    thread.getMessages().forEach(function(msg) {
      const id = msg.getId();
      if (processed[id]) return; // 転記済みのメールはスキップ
      rows.push(buildRow_(msg));
      processed[id] = Utilities.formatDate(msg.getDate(), 'Asia/Tokyo', 'yyyy/MM/dd');
    });
  });

  if (rows.length === 0) {
    console.log('新着メールはありません');
    return;
  }

  // 受信日時の昇順に並べ替えて追記
  rows.sort(function(a, b) { return a[0] - b[0]; });
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_NAME);
  sheet.getRange(sheet.getLastRow() + 1, 1, rows.length, rows[0].length).setValues(rows);

  // 転記済みID一覧を整理して保存
  cleanupProcessed_(processed);
  props.setProperty('processedIds', JSON.stringify(processed));
  console.log(rows.length + ' 件を転記しました');
}

// メール1件を行データに変換
function buildRow_(msg) {
  const body = BODY_LENGTH > 0
    ? msg.getPlainBody().replace(/\r?\n+/g, ' ').substring(0, BODY_LENGTH)
    : '';
  return [
    msg.getDate(),                      // A: 受信日時
    msg.getFrom(),                      // B: 差出人
    msg.getSubject() || '(件名なし)',   // C: 件名
    body,                               // D: 本文（先頭のみ）
    msg.getAttachments().length,        // E: 添付ファイル数
    'https://mail.google.com/mail/u/0/#all/' + msg.getId() // F: メールへのリンク
  ];
}

// 転記済みIDのうち7日以上前のものを削除して、記録の肥大化を防ぐ
function cleanupProcessed_(processed) {
  const limit = new Date(Date.now() - 7 * 24 * 60 * 60 * 1000);
  Object.keys(processed).forEach(function(id) {
    if (new Date(processed[id]) < limit) delete processed[id];
  });
}

// 初回に一度だけ手動実行：1時間おきのトリガーを登録
function setupTrigger() {
  ScriptApp.getProjectTriggers().forEach(function(t) {
    if (t.getHandlerFunction() === 'syncMailToSheet') ScriptApp.deleteTrigger(t);
  });
  ScriptApp.newTrigger('syncMailToSheet').timeBased().everyHours(1).create();
}

Code Highlights

• Duplicate prevention mechanism: The ID of the transcribed email is recorded, and it will be skipped in subsequent runs. By combining the search condition "newer_than:2d" (last 2 days) with the ID retention period (7 days), you can maintain a state without omissions or duplicates with lightweight processing.

• Replies to threads can also be picked up: Gmail handles emails by thread, but since this code manages each email within a thread by ID, replies that arrive in the same thread after transcription will also be properly transcribed during the next execution.

• Handling the body: You can adjust the number of characters to transcribe with BODY_LENGTH. If you don't need the body for management purposes, set it to 0.

Initial Execution and Trigger Setup

1. Save the code, select "syncMailToSheet" in the function selection, and run it. The first time, an authorization screen for Gmail and Spreadsheet access will appear, so please grant permission (for the "Unverified" warning, proceed by clicking "Advanced" -> "Go to...").

2. Confirm that the intended emails are being transcribed to the sheet. If too many or too few are being captured, please re-verify the SEARCH_QUERY in the Gmail search bar.

3. If there are no issues, execute "setupTrigger" from the function selection to set up automatic execution every hour. You can confirm the registration via the clock icon in the left menu.

Operation Check Checklist

• Are emails that meet the criteria received and then added to the sheet after the next execution?

• Are the same emails not being transcribed in duplicate?

• Are emails that do not meet the criteria (excluded items) not being transcribed?

• Are there no errors appearing on the "Executions" screen?

Security Precautions for Internal Implementation

Emails are a collection of confidential information. Please handle this mechanism with particular care.

• Obtain internal approval before implementation: The act of copying email content to another location (a spreadsheet) may be subject to information management regulations. Especially when targeting mailboxes that include correspondence with customers, be sure to check with the information systems department or your supervisor before proceeding.

• Keep the sharing scope of the destination to a minimum: While only the individual can see their mailbox, a spreadsheet can be seen by anyone depending on the sharing settings. The principle is to limit sharing only to "those who have permission to read those emails."

• Keep the transcribed information to a minimum: Avoid transcribing the full body of emails or copying attachments; transcribe only the items necessary for your purpose. This is why the code in this article only captures the "beginning of the body + link."

I will also list some dangerous configuration examples.

• Setting an entire personal inbox as a transcription target without any conditions.

• Storing email logs containing customer personal information in a sheet shared with "anyone with the link."

• The script continues to run after retirement or transfer, leaving behind a transcription sheet that no one manages (clearly state the creator and manager).

Application

• Development into response management: If you add a "Response Status" column to the sheet and combine it with the "Spreadsheet to Slack notification" GAS introduced previously, you can complete a series of mechanisms: "Email received -> Transcribed to sheet -> New arrival notification to Slack -> Fill in response status."

• Collaboration with AI: Accumulated inquiry logs can be used as source data for FAQs or as sources for NotebookLM.

• Extracting items from standard emails: For emails with a fixed format, such as application emails, it is possible to further develop the process by using regular expressions to extract items like "applicant" and "amount" from the body and separate them into columns.

Utilizing email information in spreadsheets

The GAS for email transcription involves three steps: (1) Select items by working backward from your goal -> (2) Narrow down the target using Gmail search operators (verify in the search bar first) -> (3) Paste the code and automate it with duplicate prevention.

By consolidating information in a spreadsheet, it becomes easier to do various things, such as creating bots using it as a source, generating documents, or using it as a trigger for actions.

I hope you will try running it manually once to start!

By the way, this can also be achieved using Google Workspace Studio, which I mentioned previously, so please try whichever method is easier for you.

Thank you for reading!
Here are my past AI-related articles!

By the way, I have also published a book summarizing AI promotion in organizations. Please take a look if you are interested.