Skip to content

##### You said:

HI i wanted to undestand that can i write a google aap script which would be able to get data from a respective email and save the attachment in a desired folder. Suppose an email comming from ICICI Bank with a subject of Statement and i want it to save the attachment in a folder Name ICICI Bank and save the folder only. Questions if any happy to address

###### ChatGPT said:

Absolutely—you can do this with Google Apps Script. Here’s a ready-to-use script that:

- Searches Gmail for emails (e.g., from ICICI Bank with “Statement” in the subject)

- Creates (or reuses) a Drive folder named “ICICI Bank”

- Saves only real file attachments (skips inline images)

- Avoids duplicates across runs

- Optional: filter by file type or time window

Paste this into Extensions → Apps Script in your Google Sheet or a standalone project, then run `runJob()`.

### How to use (step-by-step)

1. In Google Drive, click New → Google Apps Script (or open Extensions → Apps Script from a Sheet).

2. Paste the code. Update:

   - `SEARCH_QUERY` to match your bank’s sender/subject (Gmail search operators work great).

   - `FOLDER_PATH` to where you want to store files (e.g., `"Finance/ICICI Bank"`).

   - `SAVE_ONLY_THESE_MIMES` if you want only PDFs, keep as is; or set to `[]` to allow all.

3. Click Run → runJob. Authorize on first run.

4. (Optional) Set an automatic schedule:

   - Triggers → Add Trigger

   - Choose `runJob`, Event source: Time-driven (e.g., every hour or every day at 8:00).

### Useful query tweaks

- Only recent mails: add `newer_than:30d`

- Exact sender: `from:alerts@icicibank.com`

- Subject contains both words: `subject:(ICICI Statement)`

- Unread only: `is:unread` (not typical for this use case, but possible)

### Support for multiple banks

Make multiple triggers with different queries/folders, or turn `SEARCH_QUERY`/`FOLDER_PATH` into parameters and call `saveFor('query','folder')` for each bank. Example:

### Notes & tips

- Duplicates: The script remembers what it saved (by message ID + filename + size). Renamed or re-sent files are treated as new.

- Inline images: Skipped by default to avoid logos/signatures.

- Permissions: Files land under your Drive. Share the folder if a team needs access.

- Speed: If you have many matches, consider adding `newer_than:` to keep runs fast.

If you want me to tailor it for ICICI credit card e-mails vs bank statements, specific senders/subjects you receive, or to auto-create year/month subfolders, say the word and I’ll adjust the code.

##### You said:

The email is estatement@icicibank.com and the subject is -ICICI Bank Statement from (November) (which is a varaible) and the Last digit is -XXXXXXXX3543 is the account number so i have multiple accounts so i want the folders to be as ICICI Bank> Account number>then the bank statements. So i want the code to initially run the same for last 2 years and post this i would like to make a trigger. Please dont provide the code now. i have one more question that once this is done i have would like to apply the same to Credit Statements and cams statment how can the same be done. So Update the requirements and answer the expandability of the code before you code

###### ChatGPT said:

Got it—here’s an updated spec (no code yet) and how we’ll make it expandable to Credit Card and CAMS statements.

# Finalized requirements (v1.1)

## A. Scope & sources

- Mailbox filter: `from:estatement@icicibank.com` AND subject contains: “ICICI Bank Statement from (Month)” (Month varies).

- Time window (backfill): Process last 2 years of mail (use Gmail’s `newer_than:2y` in the search query).

- Attachments: Save only actual file attachments (skip inline logos/signatures). Default to PDF only.

## B. Parsing & filing logic

- Account detection: Extract the masked account tail from subject (pattern like `XXXXXXXX3543` → account 3543 as the key).

- Folder hierarchy:
  `ICICI Bank` → `Account 3543` → (optionally Year/Month) → files

  - Your minimal ask: `ICICI Bank/Account 3543/<files>`

  - Recommended (clean archives): `ICICI Bank/Account 3543/2025/11/<files>`

- Filename strategy: Keep original attachment name; optionally prefix with `YYYY-MM-<SubjectTail>` to avoid collisions.

- De-duplication: Remember what’s already saved (by MessageID + AttachmentName + Size) so repeated runs don’t duplicate.

- Safety: Skip corrupted/empty blobs; handle timeouts gracefully.

## C. Run modes

- Backfill mode: Single on-demand run with `newer_than:2y` to populate the last 24 months.

- Maintenance mode: Scheduled time-driven trigger (e.g., every morning) for new emails only.

## D. Logging & observability (optional but useful)

- Sheet log (optional): Timestamp, From, Subject, Email Date, Attachment Name, MIME, Drive File ID, Status.

- Counters: Total emails matched, attachments considered, saved, skipped (duplicate/inline/wrong MIME).

- Error notes: Any parse failures (e.g., subject didn’t expose an account tail).

## E. Privacy & governance

- Don’t store PANs or full account numbers in logs/props; just the last four digits (already masked in subject).

- Everything saves to your Drive; share the root `ICICI Bank` folder if needed.

# Expandability plan

We’ll build a profile-driven framework so each “statement type” is just a config entry, not a new script.

Each profile defines:

- name (for logs)

- Gmail query (sender/subject/MIME/time)

- regex to extract the routing key (e.g., last 4 digits, card tail, PAN)

- destination path template, e.g.
  `ICICI Bank/Account {acct_tail}/YYYY/MM` or `ICICI Credit Cards/Card {card_tail}/YYYY/MM` or `CAMS/{PAN}/YYYY/MM`

- MIME whitelist (PDF)

- filename post-processor (optional)

You’ll then run either:

- `runAllProfilesBackfill()` (once), and

- a daily trigger to `runAllProfilesDaily()`.

## 1) ICICI Credit Card statements

- Likely senders (examples; you’ll confirm): `creditcards@icicibank.com`, `estatement@icicibank.com`, `icici@` variants for credit card unit.

- Subjects (common patterns):
  “ICICI Bank Credit Card Statement for November 2025 – XXXX1234”
  “Your ICICI Bank Credit Card e-Statement (XXXX1234) for Nov 2025”

- Key extraction: last 4 digits of card: `XXXX(\d{4})` or similar.

- Destination: `ICICI Credit Cards/Card {card_tail}/YYYY/MM`

- Add-ons: If attachments sometimes compress into ZIPs, allow optional unzip & save PDFs inside.

## 2) CAMS (Consolidated Account Statement)

- Likely senders (examples; you’ll confirm): `camsmailer@camsonline.com`, `noreply@camsonline.com`, etc.

- Subjects (typical):
  “Consolidated Account Statement for PAN XXXXX1234” or
  “CAMS CAS for <Month YYYY> – PAN XXXXX1234”

- Key extraction: PAN mask or last 4 of PAN from subject/body (regex like `PAN\s+[A-Z]{5}\d{4}[A-Z]` or masked variant `XXXXX\d{4}`).

- Destination: `CAMS/{PAN_mask}/YYYY/MM`

- Security note: Store masked PAN (as present in subject) in folder names/logs; never full PAN.

# How the unified solution will work (high level)

1. Profiles array

   - ICICI Bank (Savings/Current) profile

   - ICICI Credit Card profile

   - CAMS CAS profile
     Each with: `query`, `subjectRegex`, `extractKeyFn`, `pathTemplate`, `mimeWhitelist`.

2. Search step

   - For backfill: append `newer_than:2y has:attachment`.

   - For daily: `newer_than:7d` (or just process all and rely on dedup).

3. Process step

   - For each matched thread → each message → each attachment:

     - Skip inline & wrong MIME

     - Parse subject, extract key (acct/card/PAN)

     - Build path from template (inject `YYYY/MM` if enabled)

     - Save file, mark as saved (dedup key), and log.

4. Scheduling

   - Run `Backfill` once (manual).

   - Add time-driven trigger for the daily maintenance run.

# Assumptions I’m making (so we stay unblocked)

- Subject always includes a clear masked tail (`XXXXXXXX3543`, `XXXX1234`) or masked PAN for CAMS. If any message doesn’t, we log it to “Unparsed” and still save under a generic folder (e.g., `ICICI Bank/Unknown/`).

- Attachments are PDFs (most common for statements). If ZIPs appear, we can optionally enable unzip.

- Your desired minimal hierarchy is Bank → Account → files; I’ll also include a toggle to enable Year/Month subfolders for cleaner archives.

# What you’ll get when I code it

- A single Apps Script file:

  - Clean config block for the three profiles (ICICI Bank, ICICI Credit Card, CAMS).

  - Functions: `runBackfill()`, `runDaily()`, `runProfile(profile, backfill=true|false)`.

  - Robust dedup, logging (optional to a named Sheet), and error handling.

  - Optional Year/Month subfoldering toggle.

- Short setup steps + trigger guidance.

If you’re happy with this spec, I’ll translate it into code exactly as above (keeping your “last 2 years then trigger” flow), and you can drop in any exact sender/subject variants you receive for credit cards or CAMS later if needed.

##### You said:

ok so lets do the same for ICICI Bank do you want any information for running the code

###### ChatGPT said:

Perfect—here’s the minimal info I’d need (plus sensible defaults I’ll use if you don’t change them). You can just reply with any changes; otherwise I’ll proceed with these.

### 1) Email matching

- Sender: `estatement@icicibank.com` ✅

- Subject contains (constant part): `ICICI Bank Statement from` ✅

- Examples (optional): paste 1–2 real subject lines so I can lock the regex for `XXXXXXXX####` (last 4 digits).

### 2) Account extraction

- Pattern for tail: appears as `XXXXXXXX3543` → I’ll extract `3543`. ✅
  (If there are variants like `XXXX 3543` or `(XXXX3543)`, share one example.)

### 3) Folder structure

- Root: `ICICI Bank` ✅

- Subfolder: `Account {last4}` (e.g., `Account 3543`) ✅

- Year/Month folders: Off by default (flat under account).

  > Turn on if you prefer `ICICI Bank/Account 3543/2025/11/…`.

### 4) Time window

- Backfill: last 2 years (from Dec 12, 2023 to today). ✅

- Ongoing trigger: daily (IST morning). ✅

### 5) Attachments & filters

- MIME: PDF only (skip others). ✅

- Inline images/signatures: Skip. ✅

- ZIP handling: Off (won’t unzip).

  > Turn on if ICICI sometimes sends ZIPs and you want PDFs inside saved.

### 6) Duplicates & safety

- De-dup: Yes (by MessageID + filename + size). ✅

- Corrupt/empty attachments: skip & log. ✅

### 7) Logging (optional)

- Log to a Google Sheet tab? Off by default.

  > Turn on if you want a tab like `Attachment Log` (Run Time, From, Subject, Email Date, Attachment, MIME, Drive File ID, Status).

### 8) Post-processing (optional)

- Mark emails read/starred after saving? (default: no changes).

- Filename tweak to avoid collisions? (default: keep original).

If the above defaults work, say “go ahead” and I’ll paste the script tailored for ICICI Bank with:

- Backfill (`newer_than:2y`) + Daily trigger-ready function

- Account-tail extraction from subject

- Foldering `ICICI Bank/Account ####`

- PDF-only, skip inline, de-dup, optional logging

Then we’ll reuse the same structure to add ICICI Credit Card and CAMS profiles next.

##### You said:

yes i have three Bank Accounts so it should look for the emails and then arrange the same.

###### ChatGPT said:

Awesome — I’ll set it up so it searches all matching ICICI e-statements from the last 2 years, pulls the last-4 account tail from the subject (e.g., `XXXXXXXX3543 → 3543`), and files PDFs under `ICICI Bank/Account 3543/…`. It’ll skip duplicates and inline images, and it’s trigger-ready.

Here’s the complete Apps Script. Paste it into Extensions → Apps Script (standalone or bound to a Sheet) and run `runBackfill_ICICI()` once. Then add a time-trigger for `runDaily_ICICI()`.

### How to run it (once) for your three accounts

1. Open Apps Script, paste the code, Save.

2. Click Run → runBackfill_ICICI and authorize.
   It will search all matching e-mails from the last 2 years, extract the last-4 tail from each subject, and save the PDFs to:

   - `ICICI Bank/Account 3543`

   - `ICICI Bank/Account 9271`

   - `ICICI Bank/Account 1045`
     …etc. (It auto-handles multiple accounts.)

### Set the daily trigger

- In Apps Script: Triggers → Add Trigger

- Choose function `runDaily_ICICI`

- Event source: Time-driven

- Select your preferred schedule (e.g., every day 08:00–08:30 IST)

### Notes

- If you want year/month folders, set `USE_YEAR_MONTH_SUBFOLDERS = true`.

- If any subject doesn’t include a clear tail, the file goes to `Account Unknown` (you’ll see a count in the logs).
  If you share an example subject later, we can tweak the regex to catch it.

- Turn on `LOG_TO_SHEET = true` to maintain a neat audit trail.

When you’re ready, we can clone this pattern to add ICICI Credit Card and CAMS as separate “profiles” (same engine, different query + extraction + folder template).

Do you like this personality?
ChatGPT can make mistakes. Check important info. See Cookie Preferences.
Select Certificate
Smartcard / Token User Pin
User PIN:
Signer.Digital
Signer.Digital - Confirm Key Listing / Usage
Current Domain
is trying to detect smartcards connected, or read certificate or use key for signing/encryption. Please select your option.
Licensed Sites

Website | License Status | Features | Action
--- | --- | --- | ---