Friday, September 30, 2016

Data Entry / Write Back in Tableau using Google Forms and Google Sheets

Here is a neat technique you can use to gather data right from inside your Tableau dashboard and write it to a 'database', which you can then connect to live to see real-time results.

Tableau is a read-only tool.  This is on purpose.  However sometimes there are scenarios where you might want to capture commentary, or host a poll/survey from inside Tableau, and you want to write that data back somewhere else.  Enter Google Forms and Google Sheets.  This technique works because Google Forms can accept entries via URL.  And Tableau and send URL actions which include dynamic inputs from your data.  Therefore we can post data to a Google Form from any Tableau dashboard to achieve 'write back' functionality.  I will walk through two use cases, but Im sure you will find many other creative ways of using this.

First step is to create your Google Form.  Create questions that are the same data type as what you want to have your users enter in tableau (numbers, string/text, etc).

For long Text input choose Paragraph:

And for numerical input you still choose text type of input, but choose Short Answer:


Note the unique ID of your form.  You will need this later when we make the URL Action in Tableau.

Right click in background and choose ‘View Page Source’:

Search for ‘entry’ and you will see them listed in order.  First occurrence is Question 1, next is Question 2, etc.  We will need to reference these when we create our URL action in Tableau.

Now let's set up a couple things in Tableau before we get to the URL actions (will need to make parameters and will need a sheet to act as the 'send' or 'submit' button).

Create a Parameter(s) in Tableau of the data type you want (string, integer, float, etc).  Show the Parameter(s) on your dashboard somewhere.  In this example I have a set of numerical parameters used for voting and another parameter used to capture comments (lets focus on the comment for now).

Create a new worksheet which uses the ‘Shape’ mark type and add a custom shape for your ‘Submit’ button.

Add this worksheet to your dashboard.

We will now create a URL Action to post the data to the Google Form.  Construct your URL so that it contains the Unique ID for your Google Form we found earlier in the browser, as well as all the entries/responses/inputs/parameters in it as well (separating them with ‘&’).  The form of the URL should look like this:

https://docs.google.com/forms/d/[UNIQUE_ID_OF_YOUR_GOOGLE_FORM]/formResponse?ifq&entry.[ENTRY_1_ID]=[INSERT_PARAMETER_YOU_WANT_TO_ENTER_FROM_TABLEAU]&submit=Submit
https://docs.google.com/forms/d/1irBqi5wLkhEnMGqM87o5dbSF7_m1Ia9Uj6QCKo2J86U/formResponse?ifq&entry.1379436949=&submit=Submit

If you need to submit multiple parameters/entries from this submission/click then separate them with ‘&’s and enter as many as you want:

…./formResponse?ifq&entry.[ENTRY_1_ID]=[PARAMETER_1]&entry.[ENTRY_2_ID]=[PARAMETER_2]&entry.[ENTRY_3_ID]=[PARAMETER_3]&submit=Submit


Add the Parameter values using the arrow on the side:



Then after you submit your first response you can return to the Google Form in the browser and you will see your first ‘comment’.  Click the Spreadsheet icon to create (and then see) the Google Sheet:



And now let's look at what the Numerical inputs for out beer voting parameters look like in the Google Sheet of Responses:

Awesome!  Now we can open a new Tableau workbook to connect to this Google Sheet and build whatever dashboards we want from it.  If you are using Tableau 10 just select ‘Google Sheets’ in the Connect To Data list:

In 9.3 and earlier you will need to use the Web Data Connector to connect to Google Sheets.  Go here and scroll down until you get to the Google Sheets Connector:



Now you have a live connection set up to the sheet of responses – create whatever you want with it!  Here is a simple example using the voting/poll results:



Credit to Dash Davidson who showed me this technique in his presentation at Tableau Conference 2015!

-The Don

13 comments:

  1. Hi, thank you so much for sharing this!

    However, I think the link should start with "https://docs.google.com/forms/d/e/" - one "e" is omitted. At least in my case it doesn't work w/o it. :)

    Stanislava

    ReplyDelete
  2. Hi Stanislava - thanks for your input! I just double checked my older example and it is working without the extra "e" like you mention, but perhaps due to the region you are in or maybe you have google enterprise (just some guesses) it is requiring an extra letter in the URL? Either way thank you for your tip as others may find this helpful as well!

    ReplyDelete
  3. Hi Jesse,

    I am using Tableau 10.1.5 and my personal Google Account.

    The strange thing is that it is working without any problems in Desktop, but when I upload it in Tableau Online or Tableau Public, it just doesn't work - no entries in the google sheet appear.

    I have 60 parameters and I checked them one by one from Desktop yesterday (all of them are included in the dashboard, where the Submit "button" is).

    Another approach which finally worked in my case is when:
    - for each parameter a calc.field is created
    - then the calc. field(s) should be added in a View (in Level of Detail shelf). This view should be included in the dashboard.
    and... it works :D

    I also tested:
    - pass static parameters, i.e &entry.688393943=YourName (I was thinking that the string should be in "" or '', but if I do it. it concatenates them to the str, so I receive "YourName" or 'YourNamne')
    Static parameters are working w/o problem. The problem comes when I add parameters. (I tested with just one static value, then replaced it with a parameter)
    The result is still the same - when I submit it from T. Desktop - a response is submitted, when t. Online or T. Public - no.

    In summary:

    - Constant values (static, hardcoded strings) are working in Tableau Desktop, Tableau Server and Tableau Public
    - Dynamic values from parameters - working only in Tableau Desktop
    - Dynamic values from parameters - working in Tableau Desktop, Tableau Server and Tableau Public
    after using calc. field fontaining the parameters. The calc. field should be included in a view, which is the Dashboard with Submit "button"


    I guess it can be an issue from Tableau side, (which will answer to "d" added in the link) or I just have some mistake, but I really can't see anything wrong. I double check everything :)
    So I will do it with 60 calc fields :)


    Thank you once again for sharing.

    Best,
    Stanislava

    ReplyDelete
  4. ...And also, most of the parameters are numeric. I was thinking that it might be a problem, when parsing the values, but again it is not this, because the same problem occurs when there are string parameters only in the link.

    Stanislava

    ReplyDelete
  5. Guess Google forms page source coding got changed now, unable to locate "entry xxxxx".

    Thanks for sharing!

    ReplyDelete
  6. I really appreciate information shared above. It’s of great help. If someone want to learn Online (Virtual) instructor lead live training in Tableau.kindly contact us http://www.maxmunus.com/contact
    MaxMunus Offer World Class Virtual Instructor led training on Tableau. We have industry expert trainer. We provide Training Material and Software Support. MaxMunus has successfully conducted 100000+ trainings in India, USA, UK, Australlia, Switzerland, Qatar, Saudi Arabia, Bangladesh, Bahrain and UAE etc.

    For Free Demo Contact us:
    Name : Arunkumar U
    Email : arun@maxmunus.com
    Skype id: training_maxmunus
    Contact No.-+91-9738507310
    Company Website –http://www.maxmunus.com


    ReplyDelete
  7. Thank you so much. This is very helpful.

    Will this procedure work with any other survey sites like survey gizmo etc ?

    ReplyDelete
    Replies
    1. Also one more question... is there a way to refresh data source automatically ?

      Delete
    2. so when ever a user submits a response that should show up in the tableau data source automatically..

      I have tried it using live connection to the google sheets ... but until i refresh the data source the data source is not getting updated

      Delete
  8. This is an awesome post.Really very informative and creative contents. These concept is a good way to enhance the knowledge.I like it and help me to development very well.Thank you for this brief explanation and very nice information. got a good knowledge.
    Tableau Online Training|
    SAS Online Training |
    R Programming Online Training|

    ReplyDelete
  9. Iam very impressive your site gives the best and the most interesting information. This is just the kind of information that i had been looking for, i'm already your rss reader now and i would regularly watch out for the new posts, once again hats off to you..

    Tableau online training


    ReplyDelete
  10. Are you looking for Business Leads , data entry of specific criteria?

    Well, I am providing targeted Business Leads,(Company Name,Website,Business Phone Number,Direct Email address) data entry of your Specific Criteria/Location(Zip Code,City,State,Country) with high accuracy. Data Entry and Web Research Service

    ReplyDelete
  11. This comment has been removed by the author.

    ReplyDelete