https://linen.dev logo
Join Slack
Powered by
```Hello. I need help installing meltano tap-googl...
# plugins-general
l
Copy code
Hello.
I need help installing meltano tap-google-sheets.

I'm having this error in extraction and I don't know how to fix it. I followed all the steps in this link, but I had no result with any variant.

"FatalAPIError(msg)
singer_sdk.exceptions.FatalAPIError: 403 Client Error: Forbidden for path: /drive/v2/files/1MOggzSeyNo1KSaibNxjBkGe2pkJ2hVyezDyHWhqxEac"

Please. I need help. I am using the Matatika variant.
Thank You
a
Hi @luis_henrique_barbosa_da_silva I suspect this is due to the scopes on your oauth client. The client you use must have sheets in the allowed scopes
Also, you mentioned a link where you followed some steps. Which link was that?
l
Hi @aaron_phethean , how are you? Thanks for your return. So, i used this link of install meltano in Meltano Hub: https://hub.meltano.com/extractors/tap-google-sheets--matatika/
Do you think my error is a permission error within the Google API?
a
Yes, that’s what I’m thinking. Find the client ID in your google console and add the sheets scope. https://developers.google.com/identity/protocols/oauth2
This step to setup the google credentials and API access is always a bit of a pain unfortunately. I can’t recall when it happens, but the refresh token can expire / be invalidated by google. In some of our google taps we also support a service credential, but this one only has the client ID & refresh token approach. Once setup you could use our Community Edition way to manage the refresh token, and refresh your access tokens https://www.matatika.com/docs/getting-started/community-edition#specify-a-custom-google-oauth-provider.
l
So what I did to access the Google API: 1. Enabled Google Sheets API in GCP. 2. I created an Oauth 2.0 credential in the credentials tab and with the Json file, I got the client_id and client_secret. I created Oauth in "desktop app" format 3. In my research I found how to access the refresh token: https://accounts.google.com/o/oauth2/auth?client_id=CLIENT_ID&redirect_uri=urn:ietf:wg:oauth:2.0:oob&scope=https://www.googleapis.com/auth/spreadsheets&response_type=code&access_type=offline curl -X POST -H "Content-Type: application/x-www-form-urlencoded" -d "code=YOUR_AUTHORIZATION_CODE&client_id=YOUR_CLIENT_ID&client_secret=YOUR_CLIENT_SECRET&redirect_uri=urnietfwgoauth2.0:oob&grant_type=authorization_code" https:// accounts.google.com/o/oauth2/token I took the refresh_token and configured my Tap. @aaron_phethean
@aaron_phethean can you tell me where i can find these other taps? "In some of our google taps we also support a service credential, but this one only has the client ID & refresh token approach"
a
Those steps to create the client seem reasonable, although I thought it would have been a web application client.
This tap has the two authorisation mechanisms https://github.com/Matatika/tap-google-analytics
One more thing, you can see the scopes you want when you exchange the authorisation code for a refresh token. That looks ok: https://developers.google.com/sheets/api/scopes
To debug, you could try calling the google sheets api with your credentials and confirm it can access the google api
l
@luis_henrique_barbosa_da_silva how did you get it to work with desktop app? whenever I try the first link (for generating the authorization code) with my desktop app 
client ID
I get the error: 
Error 400: invalid_request
@aaron_phethean what do you suggest using for 
redirect_uri
? I’ve been trying https://localhost but getting stuck on generating the refresh token from there (using this doc as a guide https://docs.google.com/document/d/1FojlvtLwS0-BzGS37R0jEXtwSHqSiO1Uw-7RKQQO-C4/edit)
a
I think this is the important part of that document: Modify the following link/url by replacing the highlighted parameters. The redirect_uri and scope must match the redirect_uri and scope associated to your client_id and secret…. So if you look in our docs we show how to set the redirect uri to what we need, when you make up the url yourself - you need to set them both to the same thing.
l
@luke_mueller I created an Oauth credential in GCP and in the Oauth consent screen I created an app giving access permission to a personal email (this email will be responsible for validating the API with the Refresh Token) With that, when doing the steps described by me up there, I can find the refresh token.
@aaron_phethean Thanks for the help and availability you have for me. I will test your recommendations and come back if I have more questions. Thank you very much
l
@luis_henrique_barbosa_da_silva thanks for that! was able to get a refresh token but getting the following error when running 
meltano config tap-google-sheets test
Copy code
Plugin configuration is invalid
tap_google_sheets.client.GoogleForbiddenError: HTTP-error-code: 403 {'code': 403, 'message': 'Request had insufficient authentication scopes.', 'errors': [{'message': 'Insufficient Permission', 'domain': 'global', 'reason': 'insufficientPermissions'}], 'status': 'PERMISSION_DENIED', 'details': [{'@type': '<http://type.googleapis.com/google.rpc.ErrorInfo|type.googleapis.com/google.rpc.ErrorInfo>', 'reason': 'ACCESS_TOKEN_SCOPE_INSUFFICIENT', 'domain': '<http://googleapis.com|googleapis.com>', 'metadata': {'method': 'google.apps.drive.v3.DriveFiles.Get', 'service': '<http://drive.googleapis.com|drive.googleapis.com>'}}]}: Unknown Error
l
@luke_mueller Yes. This is a error I find here too. I'm going to do these instructions from @aaron_phethean above to see if it solves the problem. If you solve this problem, can you communicate with me?
Unfortunately my error remains. I believe I don't know where my error is in the settings.
Copy code
023-06-20 22:51:35,959 | INFO | tap-google-sheets | OAuth authorization attempt was successful.
2023-06-20 22:51:48,634 | INFO | singer_sdk.metrics | METRIC: {"type": "timer", "metric": "http_request_duration", "value": 12.665393, "tags": {"stream": "config", "endpoint": "<https://www.googleapis.com/drive/v2/files/1MOggzSeyNo1KSaibNxjBkGe2pkJ2hVyezDyHWhqxEac>", "http_status_code": 403, "status": "failed"}}
Copy code
403 Client Error: Forbidden for path: /drive/v2/files/1MOggzSeyNo1KSaibNxjBkGe2pkJ2hVyezDyHWhqxEac
I don't know what else to do in the configuration to give the necessary permission to access the worksheet. Note: this worksheet posted in the error is a test. There is nothing confidential inside it.
a
Hi @luis_henrique_barbosa_da_silva (sorry for the delay, I’m in the UK). One of those errors showed a permission problem access the google drive api. I now recall that tap also calls the drive api for some file metadata. If you add that drive scope too it should all work!
l
Hi @aaron_phethean . No problem. I'm from Brazil and I like to work late at night (kkkk) Do I add this scope only in config.yml?
Copy code
settings:
 - scope: ...
Can you help me with this configuration in scope?
a
No, actually I think it's at the point you created the oauth client. If you didn't specify the drive API when you created the client and when you exchanged the code for a refresh token then the tap won't be able to call the API https://github.com/Matatika/tap-google-sheets/blob/master/tap_google_sheets/tap.py#L89
l
Hi @aaron_phethean I added the scopes both in the GCP project (in the Oauth credentials) and a generation of refresh_token by adding the correct scopes (in what I sent you above, in fact the authorization scope was only for sheets.response) and still I get the request error (not finding the sheets in drive/file). Do you have recent knowledge of someone who did this extraction to be able to help me? Thank you very much for all your help and availability.
a
Hi @luis_henrique_barbosa_da_silva sure, our tests do this everyday and our team creates the clients quite often. I personally did this a few weeks ago. Best next step is to check a few things, and we’ll double check the instructions here: https://github.com/Matatika/tap-google-sheets 1. Check Google Sheets API and Google Drive API are enabled in Google Cloud Console 2. Did you get an access token with your client/refresh token and try the api request to drive and sheets? https://developers.google.com/identity/protocols/oauth2 3. Did you try creating a client and using that client in our app? https://www.matatika.com/docs/getting-started/community-edition#specify-a-custom-google-oauth-provider 4. Did you double check the user who signed in with OAuth can actually access the file? 5. Did you already share your simple project? You or I could create one in our cloud and copy the environment under pipelines -> environment to run it locally https://www.matatika.com/docs/getting-started/cloud
@luis_henrique_barbosa_da_silva I checked the docs and added Drive API to the setup notes https://github.com/Matatika/tap-google-sheets/commit/8fc90f2a692ec807e55b79cd58e236f94e74d064 I went through the steps and wrote a medium article https://medium.com/@aaron.phethean/google-oauth-refresh-tokens-are-a-total-pain-d7d98c8fa7f8 I committed the example project I created with our Matatika Community Edition https://github.com/aphethean1/sheets-example#readme
l
Hi @aaron_phethean Thank you very much!!!! Your documents were essential for solving the problem. The main issue was the refresh_token authentication and the scopes passed to it. This document: https://medium.com/@aaron.phethean/google-oauth-refresh-tokens-are-a-total-pain-d7d98c8fa7f8 it was fundamental!!! I want to thank you for doing the Github update and all your help. I was honored to exchange knowledge with you.
a
Delighted you got it going! Be great to hear what you build. 👍
l
chiming in here to say that this unblocked me as well, thanks for your well-written article @aaron_phethean 🙂 i did run into an issue where my sheets were stored in a shared drive and so i was getting 404 errors when running tests, but moving it to my personal drive worked just fine. a problem for another day
a
Brilliant. Thanks for sharing @luke_mueller
l
Hi @aaron_phethean How do I extract the worksheets where the gid is non-zero? Exemple: https://docs.google.com/spreadsheets/d/{sheets_id}/edit#gid=956665646
a
Looks like sheet name is supported ‘child_sheet_name’, but I don’t see and gid support. We could review a PR if you’re up for adding the feature.
l
It would be amazing. Some sheets are uploaded to Drive and don't have a non-zero gid initially. Even informing the 'child_sheet_name' it does not recognize because the gid is different from zero.
a
Ok! Interesting. Be great if you could create an issue to understand the user story in any case. Direct on the repo if you get some time 💪
l
Hey @aaron_phethean, how are you? Is it too complicated for you to implement authentication through the Service Account in Tap-Google-Sheets? It would also be interesting to have this option in the Tap settings, since the manual Refresh Token generates a lot of work.
a
Hi @luis_henrique_barbosa_da_silva it’s fairly straight forward. Happy to accept PRs, but we don’t need it so will get to it with low priority.
Open in Slack
PreviousNext
Powered by