Streamlining Gmail Processing Including Attachment Files Using Gemini with Google Apps Script

GasTips / spreadsheet
Google Apps Script / spreadsheet / gmail / Gemini

Gists

Abstract

A new library, MimeTypeApp, simplifies using Gmail messages and attachments with the Gemini API for tasks like text analysis. It converts unsupported formats for seamless integration with Google Apps Script and Gemini.

Introduction

Recently, I published MimeTypeApp, a Google Apps Script library that simplifies parsing Gmail messages, including attachments, for use with the Gemini API. Ref This library addresses a key challenge: Gmail attachments come in various MIME types, while the Gemini API currently only accepts a limited set for processing. MimeTypeApp bridges this gap by providing functions to convert unsupported MIME types to formats compatible with Gemini. With MimeTypeApp, you can streamline your workflows that involve parsing Gmail messages and their attachments for tasks like text extraction, summarization, or sentiment analysis using the Gemini API. This report introduces a sample script that demonstrates how to leverage MimeTypeApp to achieve this functionality. By leveraging Google Apps Script’s integration capabilities, MimeTypeApp allows you to create powerful applications that seamlessly connect Gmail, Spreadsheets (for storing results or extracted data), and the Gemini API.

Flow

This is a flow diagram of the script. This script is run with Google Apps Script.

  1. Retrieve emails from Gmail.
  2. Retrieve the metadata, the email body, and the attachment files from each email.
  3. When the attachment files are included in the email, convert the attachment files to PDF format.
  4. Upload the converted files including the email body to Gemini.
  5. Summary the email body and the attachment files by generating content with Gemini API.
  6. Put the result into the Data sheet.

Usage

1. Create an API key

Please access https://ai.google.dev/gemini-api/docs/api-key and create your API key. At that time, please enable Generative Language API at the API console. This API key is used for the following scripts.

This official document can also be seen. Ref.

2. Sample script

In this case, you can get the script by copying a Google Spreadsheet. This Spreadsheet includes the script. The URL for copying is as follows.

https://docs.google.com/spreadsheets/d/1D3GB0KCnBcZdd7OF0iLiYR_of0RY2xTyzlr5bgLtAdw/copy

When you open this Spreadsheet, you can see a “Data” sheet which has the header row.

When you open the script editor of this Spreadsheet, you can see the following script files.

3. Set API key

Please open the script editor and set your API key to apiKey in Code.gs.

4. Script

The scripts of MimeTypeApp.gs and GeminiWithFiles.gs can be seen at here and here, respectively. Here, the script in Code.gs is shown as follows.

/**
 * First, please set your API key for using Gemini API.
 */
const apiKey = "###";

/**
 * This is a sample script for parsing emails including attachment files using Gemini with Google Apps Script.
 * The parsed data is put into the Data sheet of the active Spreadsheet.
 * Author: Tanaike
*/
function run() {
  // Retrieve current data from Spreadsheet.
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const dataSheet = ss.getSheetByName("Data");
  const lastRowDataSheet = dataSheet.getLastRow();
  const rows = dataSheet.getRange("A2:G" + lastRowDataSheet).getValues();
  const messageIds = rows.map(([, id]) => id);
  const lastRow = rows[rows.length - 1];
  const now = Date.now();
  let startTime = now - (60 * 60 * 1000);
  if (lastRow[0] && lastRow[0] instanceof Date) {
    startTime = lastRow[0].getTime();
  }

  // Setting for Gemini API.
  const responseSchema = {
    description: "JSON schema of the result value.",
    type: "array",
    items: {
      type: "object",
      properties: {
        filename: { type: "string", description: "Filename" },
        summary: { type: "string", description: "Created summary" },
      }
    }
  };
  const forGeminiObject = { apiKey, model: "models/gemini-1.5-flash-002", responseMimeType: "application/json", responseSchema };

  // Retrieve emails from Gmail.
  // In this sample, emails received after the previous process are processed. When the script is run for the first time, emails received before the previous hour are processed. Of course, you can edit this search query to fit your situation.
  const threads = GmailApp.search(`after:${Math.floor(startTime / 1000)}`);

  const v = threads.reduce((ar, thread) => {
    thread.getMessages().forEach(message => {
      const id = message.getId();
      if (!messageIds.includes(id)) {
        const value = [message.getDate(), id, message.getSubject(), message.getFrom()];
        const body = message.getBody();
        const blobs = [Utilities.newBlob(body, MimeType.HTML, "HTMLBody_GmailMessage.html")];

        // Retrieve the attachments of Google Docs files.
        const ats = [...body.matchAll(/"https:\/\/drive\.google\.com\/open\?id\=(.*?)"/g)];
        if (ats.length > 0) {
          const fileIds = ats.map(([, id]) => id);
          blobs.push(...new MimeTypeApp().setFileIds(fileIds).getAs({ mimeType: MimeType.PDF }));
        }

        // Retrieve the attachments except for Google Docs files.
        const attachments = message.getAttachments();
        if (attachments.length > 0) {
          const validMimeTypes = [MimeType.PLAIN_TEXT, MimeType.PDF, MimeType.PNG, MimeType.JPEG, "audio/mpeg", "video/mp4"];
          const validFiles = attachments.filter(b => validMimeTypes.includes(b.getContentType()));
          if (validFiles.length > 0) {
            blobs.push(...validFiles);
          }
          const invalidFiles = attachments.filter(b => !validMimeTypes.includes(b.getContentType()));
          if (invalidFiles.length > 0) {
            blobs.push(...new MimeTypeApp().setBlobs(invalidFiles).getAs({ mimeType: MimeType.PDF }));
          }
        }

        // Generate content with Gemini.
        const g = new GeminiWithFiles(forGeminiObject);
        const fileList = g.setBlobs(blobs).uploadFiles();
        const q = [
          `Run the following steps.`,
          `1. Understand the uploaded files.`,
          `2. Summary each uploaded files within 50 words.`,
        ].join("\n");
        const res = g.withUploadedFilesByGenerateContent(fileList).generateContent({ q });
        value.push(res.find(({ filename }) => filename == "HTMLBody_GmailMessage.html")?.summary || "");
        const attach = res.filter(({ filename }) => filename != "HTMLBody_GmailMessage.html");
        value.push(attach.length > 0 ? attach.map(({ filename, summary }) => `${filename}: ${summary}`).join("\n") : "");
        value.push(`https://mail.google.com/mail/#search/rfc822msgid:${encodeURIComponent(message.getHeader("Message-ID"))}`);
        ar.push(value);
        console.log(`Message ${id} was processed.`);
      }
    });
    return ar;
  }, []);
  if (v.length > 0) {
    dataSheet.getRange(lastRowDataSheet + 1, 1, v.length, v[0].length).setValues(v);
  } else {
    console.log("No emails.");
  }
}

/**
 * Create a custom menu for the active Spreadsheet.
 */
function onOpen() {
  SpreadsheetApp.getUi().createMenu("run").addItem("Run script", "run").addToUi();
}

To generate the email body and attachment descriptions, the Gemini API was utilized with the following configuration:

  • Model: models/gemini-1.5-flash-002
  • Response MIME Type: application/json
  • Response Schema:
{
  "description": "JSON schema of the result value.",
  "type": "array",
  "items": {
    "type": "object",
    "properties": {
      "filename": { "type": "string", "description": "Filename" },
      "summary": { "type": "string", "description": "Created summary" }
    }
  }
}

In this setting, the batch process can be achieved. So, both the email body and all attachment files can be processed by one API call.

Rationale for Using responseSchema:

Based on current observations, the responseSchema approach appears to yield more reliable results than directly incorporating JSON schema into the prompt. However, it’s important to note that this might change in future API updates.

5. Testing

When the function run is run, when you have several emails, the following result is obtained.

The texts summarized email body and attachment files are put into the Data sheet. When the function run is run again, the emails after the last execution are retrieved. Also, you can directly check the email by “Message URL”.

Also, these summarized data can be used for the semantic search of emails including the attachment files.

Additional information

When you run the function run by the time-driven trigger, the emails can be automatically parsed into the Google Spreadsheet.

Note

  • The top image was created by imagen3.

 Share!