A local Python script needs to be developed to automatically fill a Google Sheet with data from the company's internal service.
Main logic:
1. Connect to the Google Sheet.
2. Find rows where the ID is filled but two target values are missing.
3. Form a link based on the template:
https://internal-service.example/item/{ID}
4. Retrieve the two values (via API, if it exists, otherwise via Playwright).
5. Write the values back to the Google Sheet.
6. Mark the row as processed.
7. Continue processing the next rows.
Requirements:
• Python
• Google Sheets API
• Priority to use the official API
• If no API — Playwright
• No OCR, screen recognition, or mouse coordinates
• Confidential data must not be logged
• Configuration via .env
• Test mode (without writing to the sheet)
• Do not process already filled rows
• Batch write changes to Google Sheets
• Proper error handling and retries
It is necessary to provide:
- source code;
- requirements.txt;
- example .env.example;
- installation instructions;
- running instructions;
- brief architecture description.
Before starting implementation, please:
1. Suggest an architecture.
2. List the necessary accesses.
3. Ask clarifying questions.
4. Indicate the cost, deadlines, and estimated number of hours.