Hi, I am not able to read data from a google sheet using

tap-google-sheets

I tried to configure its settings but stuck here. Can someone please help how to configure google sheets settings and test it? Thank you.

Can you share your config and the error you are getting?

My meltao.yaml file.

version: 1
default_environment: dev
project_id: add13831-5899-498c-b871-85e655dbe1e0
environments:
- name: dev
plugins:
  extractors:
  - name: tap-google-sheets
    variant: matatika
    pip_url: git+<https://github.com/Matatika/tap-google-sheets.git>
    config:
      child_sheet_name: Meltano Data Source
      key_properties:
      - Id

  loaders:
  - name: target-gsheet
    variant: singer-io
    pip_url: target-gsheet
    config:
      spreadsheet_id: 1yyVsEGVf4GvlX3GsCrr2UE1XUQR-A98fcYnxAy1edmg

I assume this is related to https://meltano.slack.com/archives/C06A1LKFAAC/p1719831088186729?thread_ts=1717580597.523459&cid=C06A1LKFAAC?

Here is error message:

Run invocation could not be completed as block failed: Cannot start plugin tap-google-sheets: Catalog discovery failed: command ['D:\\meltano\\my-meltano-project\\.meltano\\extractors\\tap-google-sheets\\venv\\Scripts\\tap-google-sheets.exe', '--config', 'D:\\meltano\\my-meltano-project\\.meltano\\run\\tap-google-sheets\\tap.56076fb3-a371-4612-b24b-57611665030d.config.json', '--discover'] returned 1 with stderr:
 2024-07-01 16:00:36,144 | INFO     | tap-google-sheets.config | OAuth authorization attempt was successful.
2024-07-01 16:00:36,603 | INFO     | singer_sdk.metrics   | METRIC: {"type": "timer", "metric": "http_request_duration", "value": 0.457755, "tags": {"stream": "config", "endpoint": "<https://www.googleapis.com/drive/v2/files/1PCVwpJVnXkK5RiqW6qT6HPI1jw1_13UTfNhXT5Htsro>", "http_status_code": 403, "status": "failed"}}
Traceback (most recent call last):
  File "<frozen runpy>", line 198, in _run_module_as_main
  File "<frozen runpy>", line 88, in _run_code
  File "D:\meltano\my-meltano-project\.meltano\extractors\tap-google-sheets\venv\Scripts\tap-google-sheets.exe\__main__.py", line 7, in <module>
  File "D:\meltano\my-meltano-project\.meltano\extractors\tap-google-sheets\venv\Lib\site-packages\click\core.py", line 1137, in __call__
    return self.main(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "D:\meltano\my-meltano-project\.meltano\extractors\tap-google-sheets\venv\Lib\site-packages\click\core.py", line 1061, in main
    with self.make_context(prog_name, args, **extra) as ctx:
         ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "D:\meltano\my-meltano-project\.meltano\extractors\tap-google-sheets\venv\Lib\site-packages\click\core.py", line 923, in make_context
    self.parse_args(ctx, args)
  File "D:\meltano\my-meltano-project\.meltano\extractors\tap-google-sheets\venv\Lib\site-packages\click\core.py", line 1379, in parse_args
    value, args = param.handle_parse_result(ctx, opts, args)
                  ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "D:\meltano\my-meltano-project\.meltano\extractors\tap-google-sheets\venv\Lib\site-packages\click\core.py", line 2364, in handle_parse_result
    value = self.process_value(ctx, value)
            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "D:\meltano\my-meltano-project\.meltano\extractors\tap-google-sheets\venv\Lib\site-packages\click\core.py", line 2326, in process_value
    value = self.callback(ctx, self, value)
            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "D:\meltano\my-meltano-project\.meltano\extractors\tap-google-sheets\venv\Lib\site-packages\singer_sdk\tap_base.py", line 529, in cb_discover
    tap.run_discovery()
  File "D:\meltano\my-meltano-project\.meltano\extractors\tap-google-sheets\venv\Lib\site-packages\singer_sdk\tap_base.py", line 289, in run_discovery
    catalog_text = self.catalog_json_text
                   ^^^^^^^^^^^^^^^^^^^^^^
  File "D:\meltano\my-meltano-project\.meltano\extractors\tap-google-sheets\venv\Lib\site-packages\singer_sdk\tap_base.py", line 309, in catalog_json_text
    return json.dumps(self.catalog_dict, indent=2)
                      ^^^^^^^^^^^^^^^^^
  File "D:\meltano\my-meltano-project\.meltano\extractors\tap-google-sheets\venv\Lib\site-packages\singer_sdk\tap_base.py", line 300, in catalog_dict
    return t.cast(dict, self._singer_catalog.to_dict())
                        ^^^^^^^^^^^^^^^^^^^^
  File "D:\meltano\my-meltano-project\.meltano\extractors\tap-google-sheets\venv\Lib\site-packages\singer_sdk\tap_base.py", line 320, in _singer_catalog
    for stream in self.streams.values()
                  ^^^^^^^^^^^^
  File "D:\meltano\my-meltano-project\.meltano\extractors\tap-google-sheets\venv\Lib\site-packages\singer_sdk\tap_base.py", line 129, in streams
    for stream in self.load_streams():
                  ^^^^^^^^^^^^^^^^^^^
  File "D:\meltano\my-meltano-project\.meltano\extractors\tap-google-sheets\venv\Lib\site-packages\singer_sdk\tap_base.py", line 353, in load_streams
    for stream in self.discover_streams():
                  ^^^^^^^^^^^^^^^^^^^^^^^
  File "D:\meltano\my-meltano-project\.meltano\extractors\tap-google-sheets\venv\Lib\site-packages\tap_google_sheets\tap.py", line 100, in discover_streams
    stream_name = stream_config.get("output_name") or self.get_sheet_name(
                                                      ^^^^^^^^^^^^^^^^^^^^
  File "D:\meltano\my-meltano-project\.meltano\extractors\tap-google-sheets\venv\Lib\site-packages\tap_google_sheets\tap.py", line 138, in get_sheet_name
    response: requests.Response = config_stream._request(prepared_request, None)
                                  ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "D:\meltano\my-meltano-project\.meltano\extractors\tap-google-sheets\venv\Lib\site-packages\singer_sdk\streams\rest.py", line 274, in _request
    self.validate_response(response)
  File "D:\meltano\my-meltano-project\.meltano\extractors\tap-google-sheets\venv\Lib\site-packages\singer_sdk\streams\rest.py", line 193, in validate_response
    raise FatalAPIError(msg)
singer_sdk.exceptions.FatalAPIError: 403 Client Error: Forbidden for path: /drive/v2/files/1PCVwpJVnXkK5RiqW6qT6HPI1jw1_13UTfNhXT5Htsro

https://meltano.slack.com/archives/C06A1LKFAAC/p1719831134793509?thread_ts=1717580597.523459&cid=C06A1LKFAAC Have you read https://developers.google.com/identity/protocols/oauth2?

403 Forbidden

implies you haven't enabled the necessary APIs in Google Cloud Console, or supplied the correct scopes. From the README: > To get your google credentials we recommend reading and following the OAuth 2.0 Google API Documentation > > The tap calls the following Google APIs, these need to be enabled in Google Cloud Console > • spreadsheets.values.get > • drive.files.get > Consent for these scopes needs to be supplied in required scopes during OAuth client creation and requested in your authorization flow. >

<https://www.googleapis.com/auth/spreadsheets.readonly> <https://www.googleapis.com/auth/drive.readonly>

Setup all the settings:

Can you confirm if the refresh token has correct scopes set?

Here is .env file code:

TAP_GOOGLE_SHEETS_OAUTH_CREDENTIALS_CLIENT_ID='client-id-here'
TAP_GOOGLE_SHEETS_OAUTH_CREDENTIALS_CLIENT_SECRET='client secret-here'
TAP_GOOGLE_SHEETS_OAUTH_CREDENTIALS_REFRESH_TOKEN='https%3A//developers.google.com/oauthplayground'
TAP_GOOGLE_SHEETS_SHEET_ID='sheet-id-here'

You might wanna delete that. 😅 Your refresh token is incorrect though, you have supplied a URL where you need a token.

I am really sorry for that... 😄

Nothing to be sorry for, just letting you know that you leaked creds without realising maybe?

I tried different URLs here.. but last once i sent you. I also tried

<https://accounts.google.com/o/oauth2/auth?client_id={your> client ID}&redirect_uri={your redirect uri}&scope={your scopes}&response_type=code&approval_prompt=force&access_type=offline

but no sucess. Let me follow your suggested article on medium. Thank you so much.

Anyone coming across this: issue was incorrect refresh token and Drive API not enabled on Google Cloud Console.