Contact person: Business Client

Phone:Show

Email:Show

Location: Mumbai, India

Budget: Recommended by industry experts

Time to start: As soon as possible

Project description:
"### Project Title:
Develop/Fix a Web-Based Letter Tracking Dashboard with Google Apps Script Backend (View-Only for Users, Full Edit for Admin)

### Project Description:

I need a freelancer to build or fix a simple web-based dashboard for tracking official letters (tasks) in a government/office setting. The dashboard is already partially coded (I will provide the HTML frontend file and Google Apps Script backend code as attachments). It's designed for monitoring letters received from departments, assigning them to officers, tracking deadlines, status, ageing (how old the letter is), and KPIs like total tasks, completed, overdue, etc. The interface is in English and Hindi (Devanagari script).

The current setup uses:
- **Frontend**: An HTML file with JavaScript and CSS (opened locally in a browser like Chrome). It includes forms for adding/editing tasks, tables for displaying data, KPI cards, ageing buckets (e.g., 0-3 days fresh, >30 days overdue), filters, and download report functionality.
- **Backend**: Google Apps Script (GAS) deployed as a Web App, connected to a Google Sheet for data storage. The script handles CRUD operations (Create, Read, Update, Delete) for tasks and officers.
- **Data Storage**: Google Sheets (two sheets: "Letters" for tasks, "Config" for officers list).
- **Key Features Already in Code**:
- **Dashboard View**: Shows KPI cards (total letters, completed, in progress, overdue, upcoming). Ageing analysis with color-coded buckets (green for fresh, red for overdue). Table listing all letters with columns like Sr. No., Date Received, Letter Age (days), From Department, Subject, Officer, Priority (Low/Medium/High), Status (e.g., In Progress, Completed), Next Action, Deadline, Days Left.
- **Add/Edit/Delete Tasks**: Modal form to input details like date received, department, subject, officer, priority, deadline, status. Tasks can be exempted from ageing calculations.
- **Filters**: By officer, status, department, subject, or days remaining range.
- **Officers & Statuses Management**: Configurable list of officers (e.g., अधिकारी A, B, C) and statuses (e.g., In Progress, Completed, Blocked). Can add/delete via settings modal.
- **Ageing & Calculations**: Automatically calculates letter age, days left to deadline, and colors rows based on age (e.g., red for >30 days). KPIs update dynamically.
- **Download Report**: Exports the table data as CSV.
- **Test Mode**: For offline testing using localStorage (no Google Sheets needed).
- **Deployment**: GAS is deployed as a Web App URL, which the HTML fetches data from via fetch API (GET/POST requests).
- **UI Elements**: Responsive design with modals, toasts for notifications, legends for colors, and Hindi labels.

**Current Issue**: When running in production mode (TEST_MODE: false), adding, editing, or deleting a letter shows a "Failed to Fetch" error (as seen in the attached screenshots). The dashboard loads data fine, but modifications fail. This might be due to CORS, deployment settings, or script permissions. In test mode, it works offline but doesn't persist data.

**Main Requirements**:
1. **Admin Full Access (Me on My PC)**: I should be able to open the HTML file locally on my PC, connect to the backend, and have full control to add, edit, delete tasks/officers, feed new data, and change anything. No login needed for me – it should just work seamlessly.
2. **View-Only for Others**: Other users (e.g., colleagues) should access a URL (e.g., a hosted version of the dashboard) to view the latest updates in real-time. They can see the dashboard, KPIs, table, filters, and ageing analysis, but **NO ability to add, edit, delete, or change anything**. They shouldn't need to download files – just open a URL in their browser.
3. **Data Sync**: All changes I make should update in real-time (or on refresh) for view-only users.
4. **Security**: Data stored securely (e.g., in my Google Sheet). Prevent unauthorized edits.
5. **No Internet Dependency for Core View?**: If possible, make it work offline for viewers, but sync when online.
6. **Fix the Error**: Resolve the "Failed to Fetch" issue for modifications.
7. **Improvements**:
- Add search functionality across all columns.
- Email notifications for overdue tasks (optional, if easy).
- Better mobile responsiveness.
- Handle large data (up to 1000+ letters).
- Error handling and logging.
8. **Deployment**: Host the view-only dashboard somewhere free/simple (e.g., GitHub Pages, Google Sites, or a separate GAS deployment). Provide instructions for me to update the code if needed.
9. **Testing**: Provide a test mode and ensure it works on Windows/Chrome.

**Tech Stack (Preferred, but Open to Suggestions)**:
- Stick to HTML/JS/CSS for frontend (no frameworks unless necessary).
- Google Apps Script + Google Sheets for backend (free and simple).
- But **I want bidders to suggest alternative options or methods** to achieve the same (e.g., using Firebase for backend, Airtable instead of Sheets, a full web app with Node.js, or even Excel-based with VBA if it meets view-only URL requirement). Explain pros/cons, cost (prefer free/low-cost), and why it might be better. For example, if Google Sheets is slow for many users, suggest a database alternative.

**Deliverables**:
- Fixed/Updated HTML file and Google Apps Script code.
- Deployed Web App URL for admin (me) and separate view-only URL for others.
- Step-by-step instructions: How to open/edit on my PC, how others access view-only, how to add new officers/statuses.
- Video demo or screenshots showing it working.
- Source code with comments.

**Budget**: $50-150 (depending on complexity and alternatives suggested). Fixed price preferred.
**Timeline**: 3-7 days.
**Skills Needed**: Google Apps Script, JavaScript, HTML/CSS, Web Development, API Integration.

**Attachments** (I will upload):
- Letter [login to view URL] (frontend HTML/JS/CSS).
- google script [login to view URL] (Google Apps Script code).
- Screenshots of the dashboard and error.

Please bid with:
- Your experience with Google Apps Script or similar.
- Estimated time.
- Any alternative methods you suggest (e.g., "Use Supabase for backend instead – free, faster queries, better security").
- Questions if needed.

Looking for reliable freelancers with good reviews. Thanks!" (client-provided description)