Automation Expert for Google Apps Script gmail and googlesheet
Budget: ₹600 – ₹1,500 INR
Durian Gmail Price Tracking & Reconciliation System
Objective
Build a Google Apps Script + Gmail + Google Sheets automation that imports all historical and future Durian emails, maintains item-wise price history, tracks price changes, and validates Retail Confirmation prices against Dispatch Confirmation prices.
No AI or paid APIs should be used.
⸻
Phase 1 – Historical Import
Import ALL historical Durian emails available in Gmail.
Process:
* Retail Confirmation emails
* Dispatch Confirmation emails
Import all historical data before enabling live monitoring.
Historical import must NOT send any alert emails.
⸻
Phase 2 – Live Monitoring
Automatically check Gmail every 15 minutes.
Process new Durian emails and update Google Sheets automatically.
Prevent duplicate imports.
⸻
Price Extraction Logic
For every item:
If Special TP exists:
* Use Special TP as the tracked price.
If Special TP is blank or unavailable:
* Use Unit Price Ex. Tax as the tracked price.
This tracked price will be used for:
* Price history
* Price increase alerts
* Price decrease alerts
* Retail vs Dispatch comparison
⸻
SHEET 1 – Retail Confirmation History
Store every item from every Retail Confirmation email.
Columns:
* Date
* Item Code
* Description
* Quantity
* Unit Price Ex. Tax
* Special TP
* Tracked Price
Requirements:
* Preserve all historical records.
* Never overwrite data.
* Every occurrence of an item must create a new row.
* Maintain complete date-wise history.
⸻
SHEET 2 – Dispatch Confirmation History
Store every item from every Dispatch Confirmation email.
Columns:
* Date
* Dispatch Number
* Item Code
* Description
* Quantity
* Unit Price Ex. Tax
* Special TP
* Tracked Price
Requirements:
* Preserve all historical records.
* Never overwrite data.
* Every occurrence of an item must create a new row.
* Maintain complete date-wise history.
⸻
SHEET 3 – Retail Item Summary
One row per item code.
Columns:
* Item Code
* Description
* Latest Price
* Previous Price
* Lowest Historical Price
* Highest Historical Price
* Last Update Date
* Total Price Changes
Automatically update whenever a new Retail Confirmation is received.
⸻
SHEET 4 – Dispatch Item Summary
One row per item code.
Columns:
* Item Code
* Description
* Latest Price
* Previous Price
* Lowest Historical Price
* Highest Historical Price
* Last Update Date
* Total Price Changes
Automatically update whenever a new Dispatch Confirmation is received.
⸻
SHEET 5 – Price Reconciliation
Compare Dispatch prices against Retail Confirmation prices.
Columns:
* Date
* Item Code
* Description
* Retail Price
* Dispatch Price
* Difference
* Status
Status values:
* Match
* Dispatch Higher
* Dispatch Lower
* Retail Record Missing
Matching Rule:
For each Dispatch item, compare against the latest available Retail Confirmation price for the same Item Code before the Dispatch date.
⸻
EMAIL ALERTS
Alert 1 – Retail Price Increase
When a new Retail Confirmation arrives:
If:
Current Tracked Price > Previous Tracked Price
Send email alert.
Include:
* Item Code
* Description
* Previous Price
* Current Price
* Increase Amount
* Increase Percentage
⸻
Alert 2 – Retail Price Decrease
When a new Retail Confirmation arrives:
If:
Current Tracked Price < Previous Tracked Price
Send email alert.
Include:
* Item Code
* Description
* Previous Price
* Current Price
* Decrease Amount
* Decrease Percentage
⸻
Alert 3 – Dispatch Price Mismatch
When a Dispatch Confirmation arrives:
If:
Dispatch Price ≠ Latest Applicable Retail Price
Send email alert.
Include:
* Item Code
* Description
* Retail Price
* Dispatch Price
* Difference
⸻
Alert 4 – Dispatch Without Retail Record
If a Dispatch item exists but no matching Retail Confirmation history exists:
Send email alert.
⸻
DUPLICATE PREVENTION
Use Gmail Message IDs internally to prevent duplicate imports.
Do not display Message IDs in user-facing sheets.
⸻
PERFORMANCE REQUIREMENTS
* Must process historical emails efficiently.
* Must support several years of email history.
* Must continue running automatically.
* Must not require manual intervention.
⸻
TECHNOLOGY
* Google Apps Script
* Gmail Service
* Google Sheets
* Time-based triggers
No AI.
No OpenAI API.
No monthly recurring costs.
⸻
DELIVERABLES
* Google Apps Script source code
* Google Sheet template
* Installation guide
* Trigger configuration
* Testing documentation
SUCCESS CRITERIA
* All historical Durian emails imported.
* Future emails imported automatically.
* Complete item-wise price history maintained.
* Retail and Dispatch history maintained separately.
* Automatic price increase alerts.
* Automatic price decrease alerts.
* Retail vs Dispatch reconciliation.
* Duplicate-free database.
* No paid services required.
Note:- developer to keep the Retail Confirmation History and Dispatch Confirmation History sheets as the master database, and generate all summary sheets automatically from them. This will make the system much more reliable and easier to maintain as your email volume grows.
Objective
Build a Google Apps Script + Gmail + Google Sheets automation that imports all historical and future Durian emails, maintains item-wise price history, tracks price changes, and validates Retail Confirmation prices against Dispatch Confirmation prices.
No AI or paid APIs should be used.
⸻
Phase 1 – Historical Import
Import ALL historical Durian emails available in Gmail.
Process:
* Retail Confirmation emails
* Dispatch Confirmation emails
Import all historical data before enabling live monitoring.
Historical import must NOT send any alert emails.
⸻
Phase 2 – Live Monitoring
Automatically check Gmail every 15 minutes.
Process new Durian emails and update Google Sheets automatically.
Prevent duplicate imports.
⸻
Price Extraction Logic
For every item:
If Special TP exists:
* Use Special TP as the tracked price.
If Special TP is blank or unavailable:
* Use Unit Price Ex. Tax as the tracked price.
This tracked price will be used for:
* Price history
* Price increase alerts
* Price decrease alerts
* Retail vs Dispatch comparison
⸻
SHEET 1 – Retail Confirmation History
Store every item from every Retail Confirmation email.
Columns:
* Date
* Item Code
* Description
* Quantity
* Unit Price Ex. Tax
* Special TP
* Tracked Price
Requirements:
* Preserve all historical records.
* Never overwrite data.
* Every occurrence of an item must create a new row.
* Maintain complete date-wise history.
⸻
SHEET 2 – Dispatch Confirmation History
Store every item from every Dispatch Confirmation email.
Columns:
* Date
* Dispatch Number
* Item Code
* Description
* Quantity
* Unit Price Ex. Tax
* Special TP
* Tracked Price
Requirements:
* Preserve all historical records.
* Never overwrite data.
* Every occurrence of an item must create a new row.
* Maintain complete date-wise history.
⸻
SHEET 3 – Retail Item Summary
One row per item code.
Columns:
* Item Code
* Description
* Latest Price
* Previous Price
* Lowest Historical Price
* Highest Historical Price
* Last Update Date
* Total Price Changes
Automatically update whenever a new Retail Confirmation is received.
⸻
SHEET 4 – Dispatch Item Summary
One row per item code.
Columns:
* Item Code
* Description
* Latest Price
* Previous Price
* Lowest Historical Price
* Highest Historical Price
* Last Update Date
* Total Price Changes
Automatically update whenever a new Dispatch Confirmation is received.
⸻
SHEET 5 – Price Reconciliation
Compare Dispatch prices against Retail Confirmation prices.
Columns:
* Date
* Item Code
* Description
* Retail Price
* Dispatch Price
* Difference
* Status
Status values:
* Match
* Dispatch Higher
* Dispatch Lower
* Retail Record Missing
Matching Rule:
For each Dispatch item, compare against the latest available Retail Confirmation price for the same Item Code before the Dispatch date.
⸻
EMAIL ALERTS
Alert 1 – Retail Price Increase
When a new Retail Confirmation arrives:
If:
Current Tracked Price > Previous Tracked Price
Send email alert.
Include:
* Item Code
* Description
* Previous Price
* Current Price
* Increase Amount
* Increase Percentage
⸻
Alert 2 – Retail Price Decrease
When a new Retail Confirmation arrives:
If:
Current Tracked Price < Previous Tracked Price
Send email alert.
Include:
* Item Code
* Description
* Previous Price
* Current Price
* Decrease Amount
* Decrease Percentage
⸻
Alert 3 – Dispatch Price Mismatch
When a Dispatch Confirmation arrives:
If:
Dispatch Price ≠ Latest Applicable Retail Price
Send email alert.
Include:
* Item Code
* Description
* Retail Price
* Dispatch Price
* Difference
⸻
Alert 4 – Dispatch Without Retail Record
If a Dispatch item exists but no matching Retail Confirmation history exists:
Send email alert.
⸻
DUPLICATE PREVENTION
Use Gmail Message IDs internally to prevent duplicate imports.
Do not display Message IDs in user-facing sheets.
⸻
PERFORMANCE REQUIREMENTS
* Must process historical emails efficiently.
* Must support several years of email history.
* Must continue running automatically.
* Must not require manual intervention.
⸻
TECHNOLOGY
* Google Apps Script
* Gmail Service
* Google Sheets
* Time-based triggers
No AI.
No OpenAI API.
No monthly recurring costs.
⸻
DELIVERABLES
* Google Apps Script source code
* Google Sheet template
* Installation guide
* Trigger configuration
* Testing documentation
SUCCESS CRITERIA
* All historical Durian emails imported.
* Future emails imported automatically.
* Complete item-wise price history maintained.
* Retail and Dispatch history maintained separately.
* Automatic price increase alerts.
* Automatic price decrease alerts.
* Retail vs Dispatch reconciliation.
* Duplicate-free database.
* No paid services required.
Note:- developer to keep the Retail Confirmation History and Dispatch Confirmation History sheets as the master database, and generate all summary sheets automatically from them. This will make the system much more reliable and easier to maintain as your email volume grows.