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).

##### You said:

i want you to provide the Script for ICICI Bank Credit card Also the email id would be credit_cards@icicibank.com and subject of the email would be "ICICI Bank Credit Card Statement for the period________" so i want you to create an code this time i want you to add one more formality so i want you to 1) Make a Folder in the Name of ICICI Bank Credit Card and 2) Create a google sheet inside the folder and make a Table- Serial No-Subject of the emal and a hyper link of the email. So before you code is that possible

###### ChatGPT said:

Yes—totally possible. Here’s exactly how I’ll implement it for ICICI Bank Credit Card:

- Search scope (backfill first): `from:credit_cards@icicibank.com subject:"ICICI Bank Credit Card Statement for the period" has:attachment newer_than:2y`

- Foldering: Create (or reuse) a Drive folder named “ICICI Bank Credit Card.”

- Google Sheet inside that folder:

  - Create a Sheet (e.g., “ICICI_CC_Statement_Log”) inside the same folder.

  - Table columns: Serial No. | Subject | Email Link.

  - The Email Link will be a clickable hyperlink to the Gmail thread (using `GmailThread.getPermalink()`), so you can jump straight to the email.

- Saving attachments: Save PDF attachments from matching emails into ICICI Bank Credit Card (skip inline images/signatures).

  - (Optional, easy to add) Subfolders by last-4 card number if the subject contains `XXXX1234`.

- De-duplication: Remember what’s already saved (MessageID + filename + size), so running again or setting a trigger won’t duplicate files or rows.

- Run modes:

  - Backfill function (last 2 years).

  - Daily function for new mail (trigger-ready).

- Robustness: Skips corrupt/empty blobs; logs counts; won’t mark emails read unless you ask.

If you’re good with this plan (root folder only for now, Sheet with Serial/Subject/Email Link, PDFs only, 2-year backfill + daily trigger), say “go ahead” and I’ll paste the full Apps Script in one shot. After this, we can clone the same pattern for CAMS with its own folder and log.

##### You said:

ok lets code

###### ChatGPT said:

Awesome—here’s the complete Apps Script for ICICI Bank Credit Card with everything you asked:

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

- Creates (or reuses) a Google Sheet inside that folder with columns:
  Serial No | Subject | Email Link

- Backfills for last 2 years, then you can set a daily trigger

- Saves PDF attachments (skips inline images) and avoids duplicates

- Each email is logged once with a permalink to the Gmail thread

> Paste this into Extensions → Apps Script (standalone or bound to a Sheet) and run `runBackfill_ICICI_CC()` once to initialize + backfill.

### How to use

1. Open Extensions → Apps Script and paste the code. Save.

2. Run `runBackfill_ICICI_CC()` and complete authorization.

3. Check Drive: a folder “ICICI Bank Credit Card” is created with a Sheet “ICICI_CC_Statement_Log” inside.

   - The Sheet logs: Serial No | Subject | Email Link (click to open the email thread).

   - PDFs are saved in the same folder.

4. Set a daily trigger for `runDaily_ICICI_CC()` (e.g., every morning IST).

If you later want subfolders by last-4 card number (e.g., `ICICI Bank Credit Card/Card 1234/…`) or year/month subfolders, say the word and I’ll extend this to auto-extract card tails and file accordingly.

##### You said:

i want to make some changes in the code now i want to make a master code so this would it would look emails from ICICI bank and icici bank credit card both of the logics have been written i want to further add few more- add HDFC Bank email id- hdfcbanksmartstatement@hdfcbank.net-HDFC Bank Combined Email Statement for (example,this would be varaible November)-(this would be varaible 2025). i want to further add e-maail form-NSDL-CAS@nsdl.co.in-subject- (Varaible) (*November 2025*) (NSDL ID - *105052309* (varaible). I want to Further add a Logic on Indexation in continuation of existing code (April *2024* to March *2025*) so consider the logic of Financial Year (example--ICICI Bank-No of Bank Account--Financial Years. So the period logic is extra

###### ChatGPT said:

Yes—this is doable, and the clean way now is to convert everything into a single “master” script driven by profiles (ICICI Bank, ICICI Credit Card, HDFC Smart Statement, NSDL CAS). Each profile will define:

- Gmail search query (sender + subject pattern)

- How to extract a Key (Account last-4 / Card last-4 / NSDL ID / “Combined”)

- How to extract the Period (Month + Year from subject when present)

- How to compute Financial Year (FY) from that period (Apr–Mar)

- Where to save: `Root Folder > Key Folder > FY Folder > (optional Month folder) > PDFs`

- A log Google Sheet inside each root folder with:
  `Serial No | Subject | Email Link | Email Date | Period | FY | Key`

Below is a complete master Apps Script implementing all of the above (including FY logic and backfill + daily trigger functions).

## Master Apps Script (ICICI Bank + ICICI CC + HDFC + NSDL CAS)

> Paste into Apps Script and run `runBackfill_ALL()` once (last 2 years).
> Then create a trigger for `runDaily_ALL()`.

## What this master code will do (your new “period logic”)

For each email it will:

1. Read the Period Month + Year from the subject (e.g., “November 2025”).

2. Compute the Financial Year using Apr–Mar rule:

   - Nov 2025 ⇒ FY 2025-26

   - Feb 2025 ⇒ FY 2024-25

3. Save the PDF into:

### ICICI Bank

`ICICI Bank / Account 3543 / FY 2025-26 / 2025-11 / <pdf>`

### ICICI Credit Card

`ICICI Bank Credit Card / Card 1234 / FY 2025-26 / 2025-11 / <pdf>`
(If last-4 isn’t present in subject, it goes to `Card Unknown`.)

### HDFC Combined

`HDFC Bank / Combined / FY 2025-26 / 2025-11 / <pdf>`

### NSDL CAS

`NSDL CAS / NSDL ID 105052309 / FY 2025-26 / 2025-11 / <pdf>`

And each root folder gets its own log Sheet inside it.

## After backfill: set trigger

Create a time-driven trigger on `runDaily_ALL()` (daily morning IST).

If you want one extra refinement: for HDFC and NSDL, if attachments sometimes arrive as ZIP, I can add an optional “unzip and save PDFs” toggle—say so and I’ll update the master code accordingly.

##### You said:

All of these documents has to be with in one main Folder called Financial Statements

###### ChatGPT said:

Done — we’ll put everything inside one main folder:

`Financial Statements/`
→ `ICICI Bank/…`
→ `ICICI Bank Credit Card/…`
→ `HDFC Bank/…`
→ `NSDL CAS/…`

Below is the updated master code with a single change in design: every profile’s root folder is created under `Financial Statements`.

Just replace your previous script with this one (or update accordingly).

### Folder structure you’ll see in Drive

`Financial Statements/ICICI Bank/Account 3543/FY 2024-25/2024-11/...pdf`
`Financial Statements/ICICI Bank Credit Card/Card 1234/FY 2025-26/2025-11/...pdf`
…and so on.

### Next step

Run `runBackfill_ALL()` once. Then set a time-driven trigger for `runDaily_ALL()`.

If you want one single consolidated log sheet also inside `Financial Statements` (in addition to per-profile logs), tell me—easy to add.

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
--- | --- | --- | ---