Service Account failure in Google Sheets connector

[Udit107710]

I successfully created a Google Sheets source and fetched data using the Client authentication method (auth_type: client) in PyAirbyte. However, when trying to use the Service authentication (auth_type: Service), the connection fails with the following error:

Writing `source-google-sheets` logs to file: /tmp/airbyte/logs/source-google-sheets/source-google-sheets-log-J84VRT9CP.log
Traceback (most recent call last):
  File "/Users/chinta.gaurav/Desktop/untitled folder/google_sheets.py", line 113, in <module>
    source_google_sheets.check()
  File "/Users/chinta.gaurav/Desktop/untitled folder/cache_env/lib/python3.10/site-packages/airbyte/_connector_base.py", line 323, in check
    raise exc.AirbyteConnectorCheckFailedError(
airbyte.exceptions.AirbyteConnectorCheckFailedError: Connector check failed. (AirbyteConnectorCheckFailedError)
------------------------------------------------------------
AirbyteConnectorCheckFailedError: Connector check failed.
    Please review the log file for more information.
    More info: https://docs.airbyte.com/integrations/sources/google-sheets
    Connector Name: 'source-google-sheets'
    Failure Reason: "Config validation error: 'Client' was expected"
    Log file: /tmp/airbyte/logs/source-google-sheets/source-google-sheets-log-J84VRT9CP.log

Service Auth config:

{
  "spreadsheet_id": "YOUR_SPREADSHEET_ID",
  "credentials": {
    "auth_type": "Service",
    "service_account_info": {
      "type": "service_account",
      "project_id": "YOUR_PROJECT_ID",
      "private_key_id": "YOUR_PRIVATE_KEY_ID",
      "private_key": "-----BEGIN PRIVATE KEY-----\nYOUR_PRIVATE_KEY\n-----END PRIVATE KEY-\n",
      "client_email": "YOUR_SERVICE_ACCOUNT_EMAIL",
      "client_id": "YOUR_SERVICE_ACCOUNT_CLIENT_ID",
      "auth_uri": "https://accounts.google.com/o/oauth2/auth",
      "token_uri": "https://oauth2.googleapis.com/token",
      "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",

"client_x509_cert_url":"https://www.googleapis.com/robot/v1/metadata/x509/YOUR_SERVICE_ACCOUNT_EMAIL",

"universe_domain": "YOUR_DOMAIN"
    }
  }
}

This issue occurs despite using the correct configuration format and credentials for the Service authentication method. Notably, the same configuration works without any issues in the Airbyte UI. I was able to create a Google Sheets source in the UI using Service authentication, and it successfully validated and fetched data. However, when using this identical configuration in PyAirbyte, the validation fails.

[Udit107710]

@aaronsteers
Any plan or bandwidth to pick this up?

[Udit107710]

You need to pass string in the service_account_info key along with escaping \n to \\n