Google Sheet connector not connecting

Casper Badenhorst shared this problem 8 months ago
Defect Fixed

We have an issue connecting the google sheets connector.

Everything goes to plan up until the PIN needs to be validated. At which point it simply states that the connection could not be established.

/1e941ea027b5956fd41784eeda73bcbd

This is rather frustrating. To say that I have tried 30 times is probably an understatement.

This is the error from AWS showing OAUTH2 failure which is weird...

/3dc3b7fd19841dd7fe544b0a2b6d10dd

Any help will be much appreciated

Comments (2)

photo
1

Hi Casper,

We currently have two Google Sheets connectors on the marketplace but only the V4 version will work, due to changes in Google's authentication method. The new method now requires a ClientID and Client Secret in addition to the PIN. Also please note that it's in beta testing so may have some issues.

Are you using the V4 Google Sheets connector?

There's some guidance on creating the Client ID and Client Secret here: OAuth2 Authentication | AdWords API | Google Developers

You'll also need to enable the Google Drive and Google Sheets API Enable and disable APIs - API Console Help (google.com)

Kind regards,

Chris

photo
1

Hi Chris, yes I can confirm that we are using V4 of the connector. I will also look into the link you have sent through and will revert back ASAP.


Casper

photo
1

Hey Casper,


Just letting you know a new connector has been created to solve the authentication issue (which I think you may now have), though we're still running into some issues returning the sheets. This is being investigated and we will get back to you with updates soon.


Thanks for your patience on this!


Regards,

David

photo
1

I am not sure we have the new connector yet. We only downloaded from the Marketplace a few days ago. Mind emailing me a link to download it?

I don't mind waiting for the issues to be resolved...

photo
1

Hi Casper,

There are definitely issues with the new Connecters so once it is resolved and a new-new Connector is uploaded to the Marketplace I will let you know.

Cheers,

Neal

photo
1

Thanks @Neal.

We have used the connector and it connects. The issue is that no data is being returned as indicated in a previous post by David. Here is the results:

b614f8535542d6e8bc87489cb15913a6

photo
1

Thanks Casper,

Yeah, that's that's what we're seeing too. I'm keeping an eye on this one and hopefully we'll have it sorted soon.

Cheers,

Neal

photo
1

HI Neal,

Just following up - any ETA on a revised connector release? Happy to help test if needed.

-Andrew

photo
1

Hi Andrew,

The connector is in code review at the moment (which will also need testing), but there is progress being made. I will keep you updated as I get more.

Cheers,

Neal

photo
1

Thanks for the update.

photo
1

Hi Neal,

I just thought that I would check in. Do we have any progress on the testing or an ETA on the release?

Thanks,

Casper

photo
1

Hi Casper,

Not at this stage unfortunately, but there looks like there is a Yellowfin code change that needs to happen as well, which would mean an upgrade of Yellowfin, not just a new connector. This is not the sort of news we wanted to have, but I will keep you up to date on this as much as I can. This change needs review, so I am unsure of the timeline at this stage.

Cheers,

Neal

photo
1

Hi Neal,

Just wanted to ask if the necessary code change was included in the most recent release on March 16th?

-Andrew

photo
1

Hi Andrew,

This issue is still be resolved, but we are pushing to get a working solution available as soon as we can. I will let you know when something else comes through.

Cheers,

Neal

photo
1

Hi Neal,

Just chasing an update on this if available

Thanks!

photo
1

Hi Andrew,

There is testing underway of the new connector as well as the code change required for this to work, though there are still some issues to be resolved. I will keep checking in on this and let you know where it's at.

Cheers,

Neal

photo
1

Hi Neal, it has been some time since we have had an update on this. Could you please let us know where we are with this?


Casper

photo
1

Hi Casper,

Thank you for your patience on this. Both the new connector and the required code changes have been earmarked for the 8.0.10 and 9.6 releases which are due out mid-late June. Once the testing has been completed on this I will give you a final confirmation that it is going to be fixed as part of that release.

Cheers,

Neal

photo
1

Hi Neal,

With us being in July, I was wondering whether you can confirm whether this has already been resolved?

Hope to hear back from you soon.


Casper

photo
1

Hi Casper,

I can finally confirm that the new connector is released and the additional fixes required in Yellowfin have gone into both 9.6 and 8.0.10 which are available for download, and our documentation here on this has been added to reflect the new changes. Please let me know how you go after the upgrade and adding the new connector.

Cheers,

Neal

photo
1

Thanks Neal. We will get on to this one shortly and I will update you.

photo
2

Hi Neal,

We've tested with both the published sheets connector and the V4 Beta connector in the marketplace. We are still experiencing the same error

b614f8535542d6e8bc87489cb15913a6


Was there an update to the marketplace connector version still to be published?

-Andrew

photo
1

Hi Andrew,

I am chasing this up now as it seems like this has been missed in the process. I will let you know once these have gone up.

Cheers,

Neal

photo
1

Hi All,

There is some issues getting the connector uploaded to the marketplace, so please download it from the link below:

https://ftp.yellowfin.bi/f/0cf79c3cee392991

If anyone has any issues accessing this please let me know.

Cheers,

Neal

photo
1

Thank you Neal. We will get this donwloaded and testes ASAP and get back to you.

Casper

photo
1

Thanks Casper,

We've got the connector on the Marketplace now. Please use the V4 connector for this.

Cheers,

Neal

photo
1

Hi Neal,

Thanks for the new connector. The good news is that the connector is connecting now which is great! The bad news is that no new view can be created on this connection. I am attaching 2 images - the first from the the connection settings showing that the connection is connecting and that the tables are visible. The second being from the View creation menu showing that the actual tables do not pull through for us to actually create the actual views on any of the tables in the connection view. Your help will be greatly appreciated.

Thanks,

Casper

photo
1

Hi Casper,

Thank you for the update, and I am sorry we're still hitting roadblocks here. I wanted to check if you selected the specific files you wanted in the Include File(s) section prior to creating the view? From our updated documentation on the wiki here, I want to check that Step 14 below was followed:

/6016f90076fc1c0a2dbf232e2172bf26

Can you please confirm this?

photo
1

Hi Neal,

The connection was created and saved and when reviewing the Connection Pool settings, the tables appear. When trying to create the View, as the image indicates, it does not provide me the option of selecting any files. It is simply blank. If the files were visible in the "create view" window, I would have been able to do that. So the disconnect here is the step bewteen 13 & 14. 13 Works and files can be seen. 14 does not have any of the files visible. I am also attaching the debug log that we uploaded for another ticket from yesterday just in case that may help you to get some insights.

And to test it, I also tested on multiple browsers e.g. FireFox and Internet Explorer. We normally only use Chrome

Casper

photo
1

Hi Casper,

Thank you for the clarification, I think our instructions on the wiki will need some more clarification. The tables that you want to use in your views needs to be selected on the Datasource screen, similar to the image below:

/d087caceb20339bcbb52d2099e59f641

The above settings would allow views to be created off the tables EOM Activity Apr 2021 and CrowdStrikeProject only. The 3 Days on Us table would not be visible on the View screen as they were not selected as files to be included from the Datasource.

Please let me know if the above helps and if not, let's setup a screen share to look at what's happening so I can better communicate any issues to our devs.

Cheers,

Neal

photo
1

Hi Neal,


This is going to be a problematic as what you are indicating here is that anytime we want to create a new analysis, we will have to set up a new connection as the image that you are showing only appears when we actually set up the connection for the first time and the connection has been established. This will result in us having 100's of new connections. That means we cannot reuse an already established connection to our Drive which already has all the files listed as can be seen in the conenction pool when viewing the connection as below:

3b8663caba62ff7c22934ec1b42eacdf


There has to be a better way - It would be as good as being in a scenario where we already have existing views and now want to create additional analysis and durind the View Creation process, the files do not show up. Happy to have a call and a demonstration if this does not make sense or you need more information.

Casper

photo
1

Hi Casper,

You won't need to create a new connection each time, but it would be great to have a call on this to go through it. I will be working with our technical writer to get the wiki updated with some additional details.

I am available until 2:30pm AEST today and then after 3:30pm AEST if you have some time today, otherwise a call on Monday if that suits?

The option to select more tables will be available on the datasource, and after selecting them you will need to either wait for the Google Sheets Background task to run (which runs every 30mins), or manually run it for these values to be included in the View list.

Please let me know when suits you for a call.

Cheers,

Neal

photo
1

Hi Neal,

We can have the call now.

Please join this URL when you are ready: meet.google.com/wgg-cpgz-utf

Casper

photo
1

Hi Casper,

Thanks for your time today. Glad we were able to get you working. I will be getting the documentation updated so it is clearer on the process.

Please keep us updated on any issues.

Cheers,

Neal

photo
1

Hi Neal,

Some bad news for you on this lovely day...

Firstly, The method that we walked through, althoug it worked the first time, is not working any longer. LOL

Selecting to create a new View, does not show the new selected sheets but only keeps reference to the previous selected sheet.

Connection View: These are the newly selected sheets

8769b51189fe03ce25154d60e38b483f

Creation of a new view to use the saved sheets, only shows the previously selected sheet:

35c4a355ad1560a1fba1d73944318d96

In addition, Any new sheets that were created, even though we manually refresh the schedule as indicated last time, does not show the new sheets created, even if they are put in the exact same folders with the exact same permissions.

Any advice?

Casper

photo
1

Hi Casper,

Hmmm, not too sure on that, can we have another screen share so I can see the process you are taking? I'm free from 10am-12pm and then from 1:30pm-2:30pm today if there is a time in those ranges that would work for you?

Cheers,

Neal

photo
1

Hi Neal,


We have finally figured out what the real issue is with the connector above. It appears that the connector only returns files where the service user that is used, is also the owner of the files. This is an issue. For instance, if ownership is stransferred, then the file will not "exist" for the connection to be used. Also, when new files are created, unless their ownership is transferred to the service user, they will not be available for the connector to use. This results in a scenario where multuple connections will need to be set up for each user who interrogates Google Sheet data.

Could you please explain why this is the case and if there is a workaround for this as it is not a sustainable option in instances where some of our systems create file extracts on the GDrive that we want to use within YellowFin.

Help! ;)


Casper

photo
1

Hi Casper,

I will speak with our developers on this as this may be a limitation of the updated GoogleSheets API and may not be something we can work around. I will provide you an update as soon as I can.

Cheers,

Neal

photo
1

Hi Casper,

Just an update to say I am still working on replicating this issue, so far I'm not seeing the same outcome but I have some more tests up my sleeve and will come back to you a bit later today.

Cheers,

Neal

photo
1

Hi Casper,

Sorry for the delay. I have tested various scenarios and I am still not able to see any issues seeing sheets I do not own. However, I cannot see them if I trigger the update manually but only if I wait for the Scheduled task to complete. Are you seeing the same at your end? Perhaps a screen share tomorrow would be good it you are free at all?

Cheers,

Neal

photo
1

Hi Casper,

Hope you are well. I wanted to see if you trigger the update manually that you do not see the additional files, but only if you wait for the Scheduled task to complete? Do you have some availability this week for a screen share?

Cheers,

Neal

photo
1

Hi Casper,

Hope you are well. I wanted to see if you trigger the update manually that you do not see the additional files, but only if you wait for the Scheduled task to complete? Do you have some availability this week for a screen share?

Cheers,

Neal

photo
1

Hi Casper,


I hope things are going well over there.


Just wanted to let you know I'll be closing this request due to inactivity. However, if you ever wanted to re-visit this or have anything else I can help you with, please let me know.


Cheers,

Neal

photo