5 Replies Latest reply on Apr 18, 2018 3:07 PM by Ken Flerlage

    Blank sheets on Tableau Public (Google Sheets)

    Paul Bischoff

      Hi all,

       

      I'm using Tableau Public 10.5 to create some charts that I want to embed on my website via Tableau Public. I connected to Google Sheets, embedded my credentials, made the charts, and saved/uploaded them to Tableau Public. So far, so good.

       

      A day later when I go to look at the sheets on Tableau Public, they're blank. There's no error message and requesting an update doesn't work. I'm not really sure why this is happening. Here's a screenshot: https://prnt.sc/j64gij

       

      Edit: Here's the link: https://public.tableau.com/views/VPNSpeedTestResults/Simple?:embed=y&:display_count=yes

       

      To open the workbook on my PC, I first have to open from Tableau Public. Trying to open it locally results in a "Missing required context variable: username" error.

       

      So now I can access the workbook. I have to reconnect the data source to Google Sheets, including re-authenticating my Google account. This fixes the problem of the blank sheet, but only for a few more hours. Then it goes blank again.

       

      I can't save the data source to Tableau Public without an extract, and Tableau Public doesn't allow me to make extracts. I'm not sure if I'm doing something incorrect of if I just don't understand the limitations of Tableau Public. Is it even possible to use Google Sheets without extracts?

       

      My Google Sheet imports data from a JSON file. It all displays fine but can take a few seconds to load up the data, so maybe Tableau is grabbing null data before Google Sheets is done populating the cells? Or maybe the Google Sheets connection is broken somehow? I've looked around for a couple days and can't find a solution, so any advice would be appreciated.

       

      Best,

       

      Paul

        • 1. Re: Blank sheets on Tableau Public (Google Sheets)
          Ken Flerlage

          Tableau Public will actually create an extract when you save the workbook--it only works with extracts.

           

          I have seen this problem before with Google Sheets. Is the sheet set up to update when it is opened? If so, it is likely in the middle of updating when Tableau tries to extract the data. If it catches it at the right time (while the scripts are still running in Google Sheets) then no data will be pulled back to Tableau Public. The best solution I've found for this is to write a small script in Google Sheets which essentially just copies the data from one sheet to another. You'll then have to set this to run after the primary sheet has been updated. While this has a chance of causing the same problem, this script will tend to update much faster than loading data from json.

           

          function sheetCopy() {

               var sss = SpreadsheetApp.getActiveSpreadsheet();

               var ss = sss.getSheetByName('<SFrom Sheet Name>');

               var range = ss.getRange('A:H');

               var data = range.getValues();

           

               var ts = sss.getSheetByName('<To Sheet Name>');

               ts.getRange('A:H').setValues(data);

          }

           

          The above solution worked pretty well for me but it would still fail from time to time. In the end, I chose to write a Python program that would pull the json data and write it to a Google Sheet.

          1 of 1 people found this helpful
          • 2. Re: Blank sheets on Tableau Public (Google Sheets)
            Paul Bischoff

            Thanks! I'll give this a try. I'm not sure if the problem has to do with Tableau somehow losing access to the Google Sheet or if it's accessing the sheet before the cells are populated. As you said it can take a few seconds to populate from the JSON file, so hopefully this will help solve or at least rule out the latter problem.

            • 3. Re: Blank sheets on Tableau Public (Google Sheets)
              Ken Flerlage

              Great. If it solves your problem, please be sure to come back and mark my answer as correct. Thanks!

              • 4. Re: Blank sheets on Tableau Public (Google Sheets)
                Paul Bischoff

                Thanks, I modified this script a bit and it seems to have solved the problem for the most part. The issue is definitely that Tableau is trying to grab data from the spreadsheet before it has time to import the JSON data. I'll keep playing around with it because, as you mentioned, it's not completely reliable, but I'm sure with a bit of fine tuning I can get it working consistently.

                 

                Note that I couldn't use your function in a cell because setValues can't work that way, so I set up a once-daily trigger instead.

                 

                Thanks!

                • 5. Re: Blank sheets on Tableau Public (Google Sheets)
                  Ken Flerlage

                  Great. Glad to hear I was able to at least put you on the right track.