10 Replies Latest reply on Mar 24, 2016 10:55 AM by Brendan Lee

    Refresh Google Sheets WDC data source on Tableau Server

    Wouter Cuypers

      Short story: I have been experimenting with the Google Sheets connector from the Web Data Connector SDK. It works as a data source in Tableau Desktop. I now want to refresh this data source on Tableau Server which works once and then fails on subsequent refresh attempts.

       

      Slightly longer story. I adapted the SDK example for the Google Sheets connector so that the call back would redirect to our Tableau Server and would be using our Google API key. Then I imported this WDC into Tableau Server using tabadmin.

       

      Next I used this imported WDC in Tableau Desktop to create a data source based on a public Google Sheets document (anyone with the link can view it). This works perfectly and refreshes.

       

      After finishing my dashboard I published it to Tableau Server, embedding my password and configuring the Google Sheets WDC to refresh hourly. When I then monitor the extracts, it works *once* and then fails on subsequent refreshes. This is the reported error:

       

      com.tableausoftware.nativeapi.dll.DataSourceException: The table "[TableauTemp].[webdata-direct.17m4pdh0br5y5e1559wpc0z31su7]" does not exist. Unable to connect to the server "localhost". Check that the server is running and that you have access privileges to the requested database.

       

      Is there anyone else experiencing this or has achieved Google Sheets refreshes in Tableau Server? Thanks in advance!

        • 1. Re: Refresh Google Sheets WDC data source on Tableau Server
          eric templeton

          I'm having the same issue. Did you find any solution or workaround?

          • 2. Re: Refresh Google Sheets WDC data source on Tableau Server
            Matt Gizbert

            Hi!

             

            Have you been sure to import the WDC in Tableau Server (instructions here: Web Data Connectors in Tableau Server )?

             

            Also - if you install Tableau Desktop onto the same machine as Tableau Server, do you see any different behaviours than on your laptop/desktop computer?

             

            mg

            • 3. Re: Refresh Google Sheets WDC data source on Tableau Server
              Wouter Cuypers

              Hi Matt,

               

              I have indeed imported the WDC using the tabadmin commands. Otherwise Tableau Server doesn't trust the WDC. So then it won't refresh even once.

               

              The second suggestions I haven't tried yet. But I don't immediately see what help it would be.

               

              I'm currently working with Tableau Support to figure it out. I'll keep everyone posted here on my progress

              • 4. Re: Refresh Google Sheets WDC data source on Tableau Server
                eric templeton

                I talked a little bit with Tableau support as well. Google access tokens are short lived (typically only valid for an hour). Access tokens are always issued with a refresh token in the response.

                 

                My theory is that the token is valid when you first publish the workbook to server, and might complete the initial extract refresh within the valid token window. When the extract refresh runs again tomorrow (or a few hours later), the token has expired and needs to be refreshed. Tableau server probably tries to use the same expired token again and fails to create a table, which throws the missing table error.

                 

                Long story short- it seems that there is no logic in place for the sheets web data connector to check if the token is valid, and use the refresh token accordingly.

                • 5. Re: Refresh Google Sheets WDC data source on Tableau Server
                  Wouter Cuypers

                  It could very well be something like that. But the WDC looks specifically built to support this scenario, and in Tableau Desktop it works fine. We also work with other Google authenticated data sources on our Server, such as Google BigQuery, and that works great.

                   

                  I've also tried avoiding the oAuth authentication by fully opening the access to my test data in Google Sheets. But even when anyone with the link can view/edit, the WDC still requires me to login.

                  • 6. Re: Refresh Google Sheets WDC data source on Tableau Server
                    Brendan Lee

                    Hey Wouter,

                     

                    Eric Templeton is correct on his reasoning about Google Sheets access tokens.  That is exactly the reason that the extracts will fail after a certain amount of time.  We do not have support for refreshes of Google Sheets that require OAuth.  The Google Big Query and Google Analytics OAuth support unfortunately cannot be used for Google Sheets.

                     

                    But I do have an answer for you about getting a sheet with the right permission set so you don't have to authenticate.  Try following the instructions here: View and change published files - Docs editors Help

                     

                    That should allow you to access the sheet from the WDC without logging in.

                     

                    -Brendan

                    • 7. Re: Refresh Google Sheets WDC data source on Tableau Server
                      Robert Suarez

                      Wouldn't you know it we are having the EXACT same issue. In our case we have our own REST API which is providing the data. We generate an access token which actually never expires. I uploaded the WDC into our Tableau server and embedded the token in the Javascript file so you don't have to store any credentials on your own. After importing I opened Tableau Desktop, pointed to and connected to the WDC on our Tableau server and without even providing a token was able to pull in all the data without an issue. I published the data source to Tableau Server which shows correctly as a WDC. After being published I forced a manual extract refresh. This worked, EXACTLY ONCE. Any subsequent attempts by schedule or manually to refresh the extract fails with an "Unknown Failure". If I overwrite the WDC and restart the server without changing the access token I can again refresh the extract exactly once and every attempt after that fails. I likewise have a ticket open with Tableau support but so far no progress other than being asked to confirm the Tableau Run As user can access the "UNC" path of the file which is clearly unrelated.

                      • 8. Re: Refresh Google Sheets WDC data source on Tableau Server
                        Brendan Lee

                        Hey Robert,

                         

                        This is troubling, thank you for the detailed scenario.  Can I ask a few follow up questions to help troubleshoot?  I want to help get this sorted out for you.  What version of Tableau Server are you running?  And are you seeing this behavior with any other connectors?  It would be helpful to know if the scenario worked for a connector that didn't need auth.

                         

                        Thanks,

                        Brendan

                        • 9. Re: Refresh Google Sheets WDC data source on Tableau Server
                          Robert Suarez

                          Here's the details:

                           

                          9.2.0 (9200.15.1201.0018) 64-bit

                           

                          Not seeing this with other connectors although those have been quite limited in testing as this was really our first use case for them. I'd have to create or think of another connector we could use for testing.

                          • 10. Re: Refresh Google Sheets WDC data source on Tableau Server
                            Brendan Lee

                            I'd recommend trying something from the SDK, like the simple StockQuotesConnecotr_final connector.


                            I'd also recommend trying to find a more detailed error message.  Support should be able to help with that.  I'd hope that there is a more helpful error message that hints at the problem somewhere lower in the logs.