Google Sheets + Apps Script: Build a Unified Operational Sheet on Top of an Existing Live Production System
Budget: $250 – $750 USD
We are looking for a freelancer to design and implement a new Google Sheet called PLANILLA_UNIFICADA (Unified Sheet) on top of an existing live production system.
This is not a new system from scratch. We already have a working production workflow using Google Sheets, Google Apps Script, and a Python interceptor. What we need is a new central operational sheet that sits on top of the current system and stays fully synchronized with it.
Our current production system has 3 main spreadsheets:
1) PEDIDOS LISTOS
This is the commercial base. It receives delivery note data coming from our ERP flow through a Python interceptor + Apps Script.
It contains fields such as delivery note number, date, delivery note date, customer, address, salesperson, total, quantity, province, notes, paid date, paid, dispatched, and transport.
2) DESPACHOS_v2_LIMPIO
This is the operational / warehouse / logistics spreadsheet.
It handles operational intake, prepared status, transport classification, route calculation, route configuration, and delivered historical records.
Relevant sheets include FRACCIONADOS, REPORTE_Transportes, Transportes, Ruta, Ruta_excluidos, CONFIG_RUTA, and HISTÓRICO_ENTREGADOS.
3) DESPACHOS
This is another production spreadsheet used for the real dispatch / delivery event through a QR/form flow.
From there, dispatch status and transport are already synchronized back into PEDIDOS LISTOS.
The new Unified Sheet must become the main operational screen for the team, while the existing spreadsheets continue to exist and continue to work.
Very important:
changes made in the Unified Sheet must update the existing production spreadsheets, and changes made in the existing production spreadsheets must also be reflected back into the Unified Sheet.
This must be truly bidirectional in practice, not just one-way reporting.
The Unified Sheet must show, at minimum:
- Delivery note number
- Date
- Customer
- Address
- Salesperson
- Transport
- Prepared
- Dispatched
- Paid
- Notes
The sheet must be simple for operators, but it can use hidden technical columns if needed.
Expected behavior must be very specific:
- If a user marks Prepared in the Unified Sheet, it must behave exactly as if that same delivery note had been marked as prepared in DESPACHOS_v2_LIMPIO for the corresponding operational workflow.
- If a user marks Prepared in DESPACHOS_v2_LIMPIO, the Unified Sheet must reflect that change.
- If a user marks Dispatched in the Unified Sheet, it must update the existing dispatch-related sources accordingly and reflect the same final result currently expected by the live system.
- If a user marks Dispatched in the existing sheets, the Unified Sheet must reflect it.
- If a user marks Paid in the Unified Sheet, it must update PEDIDOS LISTOS.
- If Paid is changed in PEDIDOS LISTOS, the Unified Sheet must reflect it.
- If Transport is changed in the Unified Sheet, it must update the correct existing source and remain synchronized.
- If notes exist in multiple sources, the Unified Sheet should show one human-friendly consolidated notes field.
Every delivery note that exists in PEDIDOS LISTOS must appear in the Unified Sheet, even if it is not yet prepared, not yet dispatched, not yet paid, and not yet routed.
Important concern:
we do not want a fragile solution that overloads Google Sheets with unnecessary constant cross-file calls, excessive triggers, race conditions, or unstable behavior under daily use.
This is a real production environment, so stability matters more than cleverness.
We are open to the freelancer proposing the best architecture, but the expected functional behavior is already quite clear and must be respected.
We can provide Apps Script files, Python interceptor files, screenshots, and a more detailed system description in a PDF attachment.
This is not a new system from scratch. We already have a working production workflow using Google Sheets, Google Apps Script, and a Python interceptor. What we need is a new central operational sheet that sits on top of the current system and stays fully synchronized with it.
Our current production system has 3 main spreadsheets:
1) PEDIDOS LISTOS
This is the commercial base. It receives delivery note data coming from our ERP flow through a Python interceptor + Apps Script.
It contains fields such as delivery note number, date, delivery note date, customer, address, salesperson, total, quantity, province, notes, paid date, paid, dispatched, and transport.
2) DESPACHOS_v2_LIMPIO
This is the operational / warehouse / logistics spreadsheet.
It handles operational intake, prepared status, transport classification, route calculation, route configuration, and delivered historical records.
Relevant sheets include FRACCIONADOS, REPORTE_Transportes, Transportes, Ruta, Ruta_excluidos, CONFIG_RUTA, and HISTÓRICO_ENTREGADOS.
3) DESPACHOS
This is another production spreadsheet used for the real dispatch / delivery event through a QR/form flow.
From there, dispatch status and transport are already synchronized back into PEDIDOS LISTOS.
The new Unified Sheet must become the main operational screen for the team, while the existing spreadsheets continue to exist and continue to work.
Very important:
changes made in the Unified Sheet must update the existing production spreadsheets, and changes made in the existing production spreadsheets must also be reflected back into the Unified Sheet.
This must be truly bidirectional in practice, not just one-way reporting.
The Unified Sheet must show, at minimum:
- Delivery note number
- Date
- Customer
- Address
- Salesperson
- Transport
- Prepared
- Dispatched
- Paid
- Notes
The sheet must be simple for operators, but it can use hidden technical columns if needed.
Expected behavior must be very specific:
- If a user marks Prepared in the Unified Sheet, it must behave exactly as if that same delivery note had been marked as prepared in DESPACHOS_v2_LIMPIO for the corresponding operational workflow.
- If a user marks Prepared in DESPACHOS_v2_LIMPIO, the Unified Sheet must reflect that change.
- If a user marks Dispatched in the Unified Sheet, it must update the existing dispatch-related sources accordingly and reflect the same final result currently expected by the live system.
- If a user marks Dispatched in the existing sheets, the Unified Sheet must reflect it.
- If a user marks Paid in the Unified Sheet, it must update PEDIDOS LISTOS.
- If Paid is changed in PEDIDOS LISTOS, the Unified Sheet must reflect it.
- If Transport is changed in the Unified Sheet, it must update the correct existing source and remain synchronized.
- If notes exist in multiple sources, the Unified Sheet should show one human-friendly consolidated notes field.
Every delivery note that exists in PEDIDOS LISTOS must appear in the Unified Sheet, even if it is not yet prepared, not yet dispatched, not yet paid, and not yet routed.
Important concern:
we do not want a fragile solution that overloads Google Sheets with unnecessary constant cross-file calls, excessive triggers, race conditions, or unstable behavior under daily use.
This is a real production environment, so stability matters more than cleverness.
We are open to the freelancer proposing the best architecture, but the expected functional behavior is already quite clear and must be respected.
We can provide Apps Script files, Python interceptor files, screenshots, and a more detailed system description in a PDF attachment.