Me again, so I digged deeper into the service account 403 issue when trying to upload.
Turns out google service accounts created after 15th April 2025 have 0 storage quota for drive, making them unusable to access my drive. Service accounts should only access shared drives.
A workaround exists where a service account can impersonate a real user, but it requires Domain-wide Delegation activated in your cloud console which should be used with caution.
Granting this service account access to your organization’s data via domain-wide delegation should be used with caution. It can be reversed by disabling or deleting the service account or by removing access through the Google Workspace admin console.
My old service account has a storage quota:
You can test it yourself using an HTTP Request node using your service account credential (with http node usage option enabled). For scopes in the service account credential you can use: https://www.googleapis.com/auth/drive, https://www.googleapis.com/auth/drive.appdata, https://www.googleapis.com/auth/drive.photos.readonly