Tableau Voting Solution + Google Spreadsheet Data

We had a unique scenario arise where we needed to allow our users to vote on data within one of our dashboards.  The users needed to be able to vote on the data and then refresh the dashboard to see the voting results.  We use Google Apps, so we leveraged Tableau URL Actions with Google Forms to capture the voting results.  We also had the URL’s dynamically built using Tableau data and had the form auto-submit so they didn’t have to actually fill out or submit a form result.  The hard part of this whole solution was getting data from a Google Spreadsheet into Tableau.  Their is no easy way to do this in Tableau so this was our workaround solution in the mean time.

Here is rudimentary example of what it looks like in Tableau.  A user can click on a mark and a menu pops up where they can click a Priority Level.  Once they click the priority level, an auto-submission to a Google Form is sent.  The user is presented a browser window that says thank you for your submission.  This also could be implemented in Tableau with a custom button or with whatever creative option you come up with.

Actions

Step 1: Create a Google Form

In Google Drive, select Create-> New Google Form

Add a Form Field for every data element you want to collect.   I would recommend creating this form using a unique Google account similar to a service account.  This way it will survive if you ever leave an organization.

Step 2: Get Pre-filled URL

In your Google Form Editor, go to Responses->Get pre-filled URL then input sample data for the form fields and click submit.  You should then see a URL with parameters set to the sample data you entered.

Step 3: Modify Pre-Filled URL for Auto-Submission

In the pre-filled URL do the following:

  1. Replace viewform? with formResponse?ifq&
  2. Append &submit=Submit to the end of the string

Step 4: Create Actions on Dashboard

In your Tableau Dashboard select Dashboard->Actions->Add Action->URL  to create a new URL Action.  Select the Menu type of action so that you can display a menu of options.  Then paste your pre-filled URL in the URL section.  Replace the sample values with either dynamic values from your data set such as <Region> will select the Region and <ATTR(Username)> will select a calculated field that references the Tableau User’s username.  You can also hardcode values in for each action as shown below with a “1”.  A good example is if you wanted people to assign a priority level for each Region.  To do this you would create an action for each priority level with a different hardcoded value for each priority level.

https://docs.google.com/forms/d/DOCUMENT_ID/formResponse?ifq&entry.962169694=<Region>&entry.818637629=1&entry.1517356435=<ATTR(Username)>&submit=Submit

Step 5: Add Google Apps Script to Google Form Response Spreadsheet

In your Google Spreadsheet, click on Tools->Script Editor and paste the following code in the editor.  Change “FILE_NAME” to the name you want for the Excel version of the file.  This code will:

  • Convert the Google Spreadsheet to Excel and return as a Blob
  • Delete the Excel version if it already exists
  • Save the Excel version to Google Drive

After pasting in the code, Click Save, then Run saveResults() to test the code.  If prompted to authorize the script, select “Yes”.  If the script runs successfully, you should see an Excel version of the spreadsheet in your drive.

Now you need to add a trigger for the code.  Select Resources->Current Project Triggers and create a trigger to run saveResults().  You can run this on a timer, such as every few minutes, or whenever a form is submitted.

//this is the main function that deletes the current xls document in Drive, requests it to be converted to xls, then creates a new xls spreadsheet in Drive with the same name
function saveResults() {
 var fileName = 'FILE_NAME';
 deleteDocByName(fileName);
 var docID=SpreadsheetApp.getActiveSpreadsheet().getId(); //get the current spreadsheet id
 var doc = exportAsExcel(docID); //call function to convert to xls and return blob
 DriveApp.createFile(doc).setName(fileName) //save file to Drive
}

//this function converts an existing google spreadsheet to excel and returns a blob
function exportAsExcel(spreadsheetId) {
 var file = Drive.Files.get(spreadsheetId);
 var url = file.exportLinks['application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'];
 var token = ScriptApp.getOAuthToken();
 var response = UrlFetchApp.fetch(url, {
 headers: {
 'Authorization': 'Bearer ' + token
 }
 });
 return response.getBlob();

}

//this function deletes a google document by name
function deleteDocByName(fileName){
 var docs=DriveApp.getFilesByName(fileName) 
 if(docs.hasNext()){
 docs.next().setTrashed(true) 
 }
 }

Step 6: Install Google Drive Sync

Go to your Google Drive in your browser and on the left hand side select “Install Drive for your computer”.  This will sync files from your Drive to a local directory.  If you have a server that you can install this one, then do so, otherwise you have to install on your machine.  However, a server has the obvious benefit of not turning off.  If you want to be able to access these files from other locations, sync to a network directory.  A network directory is usually your best option if wanting to publish this data and have it be dynamic.

Once you install Drive on a computer/server, then it should start syncing immediately.

Step 7: Link to Excel File in Tableau

Create a new data connection to the Excel file and start Creating Amazing in Tableau!

Note:  We actually have our ETL tool pick up the Excel file and load it into our DW, but this is a faster option.

Here are some additional methods to connect Google Docs to Tableau.  I am leaning to using an ETL tool to fetch data from the Google Spreadsheet, but don’t really want to publish the Google Spreadsheet to the web which is a requirement in many scenarios.

20 thoughts on “Tableau Voting Solution + Google Spreadsheet Data

  1. Thanks for sharing!

    I’m really interested in this but the code is not working for me. Below is the error I encountered:

    var file = Drive.Files.get(spreadsheetId);
    ReferenceError: “Drive” is not defined. (line 13, file “Code”)

    I’m not sure of the problem with this code here. Any suggestions would be greatly appreciated!!

    1. To get that line of code to work, you will need to ensure that the Drive API is turned on. To do that, go to the script editor and when viewing the code, click the Resources menu and select “Advanced Google Services”. When you get into the services, make sure the Drive API is turned on. After that you should be good to go. Good Luck!

      1. Jann, Sorry I just saw this, but I just updated the script to use DriveApp rather than DocsList, thanks for the heads up!

  2. The delete file doesn’t seem to be working for me. The script creates the file and then when run again it creates a second xlsx file. Any ideas what might be going on?

    1. Bummer, well here’s another version of the deleteDocByName function that you could use that worked for me as well.

      function deleteDocByName(fileName){
      thisFile = myFolder.getFilesByName(fileName);
      while (thisFile.hasNext()) {
      var eachFile = thisFile.next();
      var idToDLET = eachFile.getId();
      var rtrnFromDLET = Drive.Files.remove(idToDLET);
      }
      }

  3. This is amazing! I’m having the same problem as Lauren though. I pasted the new delete function you posted but then I get an error that myFolder is not defined. Is there a way to fix this?

    Thanks! Great help!

    1. Hi Gabriel,

      I consulted one of my colleagues who uses this on a regular basis in Production and they confirmed that they used to have an issue where the script wouldn’t delete the file for some reason. The would happen sporadically. When this would happen, it would create a duplicate file with a “(1)” at the end. Here is what they did to resolve the issue.

      First they used the standard deleteDocByName function that is in the post. Here is it for reference:

      //this function deletes a google document by name
      function deleteDocByName(fileName){
      var docs=DriveApp.getFilesByType(fileName)
      for(n=0;n

  4. Thanks for the quick reply Kris!

    I added the changes to the saveResults function, but now I’m getting an error with the delete function you referenced saying that the for-loop is missing a “;”. Do you know where this is supposed to go? Here is my code for reference.

    Thank you for being so helpful! You’re fantastic!

    //this is the main function that deletes the current xls document in Drive, requests it to be converted to xls, then creates a new xls spreadsheet in Drive with the same name
    function saveResults() {
    var fileName = ‘googletest’;
    var fileName1 = ‘googletest(1)’;
    deleteDocByName(fileName); //delete the file
    deleteDocByName(fileName1); //delete the duplicate filename if it exists
    Utilities.sleep(5000); //sleep for 5 seconds
    deleteDocByName(fileName); //delete the file
    var docID=SpreadsheetApp.getActiveSpreadsheet().getId(); //get the current spreadsheet id
    var doc = exportAsExcel(docID); //call function to convert to xls and return blob
    DriveApp.createFile(doc).setName(fileName) //save file to Drive
    }

    //this function converts an existing google spreadsheet to excel and returns a blob
    function exportAsExcel(spreadsheetId) {
    var file = Drive.Files.get(spreadsheetId);
    var url = file.exportLinks[‘application/vnd.openxmlformats-officedocument.spreadsheetml.sheet’];
    var token = ScriptApp.getOAuthToken();
    var response = UrlFetchApp.fetch(url, {
    headers: {
    ‘Authorization’: ‘Bearer ‘ + token
    }
    });
    return response.getBlob();

    }

    //this function deletes a google document by name
    function deleteDocByName(fileName){
    var docs=DriveApp.getFilesByType(fileName)
    for(n=0;n if(docs[n].getName() == fileName){ //telling me error is in this line
    var ID = docs[n].getId()
    DriveApp.getFileById(ID).setTrashed(true)
    }
    }
    }

    1. Eeek, yeah it looks like the browser interpreted the less than sign as a tag. However, here is a simpler version of the function that I just tested. It works well.

      function deleteDocByName(fileName){
      var docs=DriveApp.getFilesByName(fileName)
      if(docs.hasNext()){
      docs.next().setTrashed(true)
      }
      }

  5. That was fast!

    It looks like I’m still getting the same error that the ; is missing. It looks like the code snippet may have been altered by the browser again. Maybe you could tell me where the greater than sign goes as well as the missing portion?

    Thanks!

    1. Just saw this, but I reposted a newer version that is browser friendly and simpler.

      function deleteDocByName(fileName){
      var docs=DriveApp.getFilesByName(fileName)
      if(docs.hasNext()){
      docs.next().setTrashed(true)
      }
      }

  6. Modified the deleteDocByName function to make it simpler and it seems to be deleting the document everytime without issue. Post is updated.

    function deleteDocByName(fileName){
    var docs=DriveApp.getFilesByName(fileName)
    if(docs.hasNext()){
    docs.next().setTrashed(true)
    }
    }

  7. Hey Rich,

    I am still getting a duplicate file with a (1) at the end.

    Here is the delete portion of the file. Any idea what’s going on?

    //this function deletes a google document by name
    function deleteDocByName(fileName){
    var docs=DriveApp.getFilesByName(fileName)
    if(docs.hasNext()){
    docs.next().setTrashed(true)
    }
    }

  8. Sorry I meant Kris in the previous post.

    One other question if your ok with it.

    I am trying to auto refresh my excel spreadsheet in Tableau Deskop and can’t figure out how to do it in Mac.

    I found this for windows for an auto refresh, which may be helpful to others. How would I do this in a Mac? Also how I figure out my server name? Or my Database name? A little confused.

    C:\Program Files\Tableau\Tableau 9.0\bin>tableau refreshextract–server
    https://blah_blah_server_name –username YourServerSignIn –password
    YourServerPwd –datasource “Some_Table” –source-username YourDatabaseSignIn
    –source-password YourDatabasePassword

    Thanks!

    1. Hi Brook,

      Bummer…google apps script does get a bit wonky at times. We have found that for some reason or another it simply doesn’t execute correctly. What has worked for us is adding another line of “deleteDocByName(fileName);” before saving the new file, or a little more brute force and adding a few more delete lines just to be safe.

      Tableau did release their new Web Data Connector framework that allows us to now build custom web data connectors. It is still being developed by us in the community, but is starting to mature quickly. One of the community members has already built a web data connector for google sheets that you can try out. With a web data connector, you can skip the step of converting to excel and saving and have Tableau connect directly to the google spreadsheet. The only thing I haven’t tested with the connector is if it requires the user to login every time when the extract is being refreshed on the server.

      To use the new web data connectors, open Tableau and select the Web Data Connector data source and input a URL to an existing Web Data Connector that you have built, or someone else in the community has built.

      Link to Tableau Web Data Connector Community Page: http://community.tableau.com/community/developers/web-data-connectors

      Link to Google Spreadsheets Connector: http://tableau.github.io/webdataconnector/Examples/GoogleSheetsConnector.html

      Regarding your question about refreshing excel data in Desktop, I may need a little bit more info. Unfortunately, the Tableau Data Extract Utility is only available for Windows right now, so you don’t have a great option. You could hack away at the Extract Utility similar to how it was done to use tabcmd on linux, but its a lot of effort. http://community.tableau.com/thread/118422 Another option, if you feel like writing some code would be to use the Tableau Data Extract API. With it you could open the existing TDE, and write new rows to it. The Extract API is actually fun and gives you some great flexibility.

      Hope that helps!

      -Kris

  9. This is a great solution. Thanks for sharing it.
    Just a heads up regarding the file duplication: your file name should be different from your original sheets file in order to replace the excel file at every update, This will keep you with a single Excel document.

    Thanks,
    V.

Leave a Reply

Your email address will not be published. Required fields are marked *