gas-gmail-drive-etl

Store Gmail messages to Spreadsheet and Drive ( attachment files ) with your custom rules

AI Summary

GmailDriveETL GAS Library

Provides an ETL workflow that searches and filters Gmail messages, stores their metadata in a Spreadsheet, and saves attachments to a Drive folder.

Target Users

Intermediate Apps Script developers building internal tools or workflows who need to automatically archive and manage Gmail messages and attachments.

Problems Solved

Extracting needed Gmail messages and attachments and keeping them in sync across Sheets and Drive requires repetitive Gmail/Drive API code and bookkeeping.

Main Features

Search queries plus optional filter callback

Combines Gmail search operators with a user-supplied filter function for fine-grained message selection.

Automatic write to Spreadsheet

Define arbitrary columns (from, subject, etc.) and append a row simply by calling `SheetStore.store()` inside the extraction process.

Attachment saving with link generation

`FolderStore` saves attachments into Drive and links them back to the row in the sheet.

Customizable extraction process

`extractProcess` callback lets developers parse mail bodies or run extra logic, adapting the ETL to their workflow.

Usage Examples

Save yesterday's attachment mails to Sheet & Drive

function quickEtl() {
  // Prepare a Spreadsheet and Drive folder and set their IDs
  const sheetId  = 'YOUR_SHEET_ID';
  const folderId = 'YOUR_FOLDER_ID';

  // Create an extractor instance
  const extractor = GmailDriveETL.createGmailExtractor({
    sheetStore:  GmailDriveETL.createSheetStore(SpreadsheetApp.openById(sheetId)),
    folderStore: GmailDriveETL.createFolderStore(DriveApp.getFolderById(folderId)),
    // Query: e-mails from yesterday with attachments
    query: { after: Utilities.formatDate(new Date(Date.now()-86400000), Session.getScriptTimeZone(), 'yyyy/MM/dd'), has: 'attachment' },
    // Optional filter – pass all messages here
    filter: () => true,
    // Default extract process saves cols and files
    extractProcess(message, sheetStore, folderStore, files){
      sheetStore.store({
        message,
        cols: [ message.getFrom(), message.getDate(), message.getSubject() ],
        files
      });
      return true; // any return value is allowed
    }
  });

  // Run extraction
  extractor.execute();
}

Paste the code into the Apps Script editor, set the IDs, and run. The script

fetches Gmail messages matching the query,
saves attachments to the specified Drive folder, and
appends message data with file links to the Spreadsheet.