External Google Sheets Writing and Sharing for Third-Party Reporting

When dealing with a lot of data, it's best to use separate service accounts for reading, inserting, and updating. This way, if there's a rate limit issue, it won't affect all the tasks because the workload is spread out among different accounts. This setup makes everything more reliable and keeps things running smoothly.

Distributing tasks across multiple service accounts enables better scalability and load distribution. As the workload increases, you can allocate additional resources to specific tasks without impacting the performance of others. This flexibility allows for seamless scaling to meet growing demands.

Moreover, it's advisable to employ distinct service accounts tailored to each environment, such as local, staging, and production. This practice enhances security by ensuring that the permissions and access levels are appropriately configured for each environment, minimising the risk of unauthorised access or accidental data leakage across different stages of development and deployment.

Message Queues using google pub sub

Rather than processing tasks directly in a job queue, a more efficient approach is to send messages to Google Pub/Sub. This accelerates write operations, eliminating queue wait times. In case of failure, the job queue can retry failed entries from Pub/Sub.

Cloud Functions operate independently from your system, ensuring continuous operation even if your system is offline.

Challenges of Updating Un-inserted Records

When updating a Google Sheet record, the process hinges on having the correct row ID. If an insert hasn't occurred or is still underway, the absence of a row ID will lead to the update failing.

When encountering an empty row ID during update attempts, the status will transition to "on hold." granting the insert more time before retrying the update. On Hold jobs are processed concurrently, prioritising inserts over updates during self-healing operations.

Replaying Items Manually

Manual replaying provides meticulous oversight when automated processes encounter errors, ensuring the accurate processing of each item in Google Sheets. While automated systems have error handling mechanisms, nuanced issues may require human intervention for effective resolution. Manual replaying offers controlled error resolution and maintains data integrity. Additionally, it provides a transparent audit trail, crucial for compliance, troubleshooting, and accountability.