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

51 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
    Replies
    1. HI, could you please post an example where you'd created 60 parameters and stored the value using calc. fields

      Delete
  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
    3. Hi Rishikesh Bayya,
      I'm facing same issue. Manual refresh on desktop works but while on server, data sources doesn't get updated and I don't see latest data. Did you find out a way to solve this?

      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
  12. Thanks for your informative article.Really very useful.Tableau Training in chennai

    ReplyDelete
  13. India Data Entry Services is a trusted Amazon Bulk Product Upload Services provider to e-commerce companies and platforms. Be it e-commerce product cataloguing or Product Upload Services, since our inception in 2010, we are striving to get all best o cater global organizations. The company is strategically positioned to support big or small e-Commerce Stores working on various domains such as fashion apparels, crockery items, furniture, accessories, shoes, bags, technology and much more. We house dedicated professionals who are assigned to separate divisions that are deftly managing Ecommerce Product Data Entry Services, specification and attributes for web stores, online retailers and eCommerce catalog companies.

    ReplyDelete
  14. Any updates on this with regards to the google form source? what can I get now knowing that I'm unable to locate "entry: XXXXXXX)


    Thanks,

    ReplyDelete
  15. It is our need that you put it here in your post as much useful that all can take it as a good thing from your site. Thank you.
    digital marketing company in india

    ReplyDelete
  16. Hi, I am Faiza. I can assist you with all kinds of Data Entry, Web Research, Copy Paste Work, Contact Details from Websites, PDF to Excel/Word, Image to Excel/Word, Typing in Excel/Word, , E-Manually Typing Work to Excel or Word Document and other various business related works. data entry

    ReplyDelete
  17. Magento is one of the most popular eCommerce platform, customizable shopping cart platforms available. If you have chosen Magento as your eCommerce platform (WebSite), Acelerar can offer you end-to-end support with our efficient and streamlined Magento Product Data Entry Services at the most economical rates and 24x7 Support. Our Experts have a wealth of experience working on Magento Product Upload Services on eCommerce platform.

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

    ReplyDelete
  19. Health Is God expects to convey the most ideal wellbeing surveys of the supplement accumulations and different wellbeing generation that range from skincare to mind, muscle, male upgrade and cerebrum wellbeing conditions. You, the client are of most extreme significance to us, and we are focused on being the entrance that maintains your sound way of life.

    ReplyDelete
  20. Nutra Trials defines personal characteristics of different health products including skincare, weight loss, muscle and male enhancement. The study presented here is briefly described for reader convenience and to deliver them assurance with health standards. The best possible answers are given here regarding the selection of an ideal supplement or cream or serum that possibly remains to be safe for health and do not cause any side effects.

    ReplyDelete
  21. I think this is a real great article post.Really looking forward to read more. Visit at
    Crazy Video Hub

    ReplyDelete
  22. It is a great job, I like your posts and wish you all the best. and I hope you continue this job well.
    NutraT line

    ReplyDelete
  23. Dear Mr.Jesse,

    I have browsed and read many articles in tableau community forum and tableau kb regarding write-back workaround for Tableau but none of them seems to answer my need. Until I found this article and it is really what I need. It works great without trouble.

    Thank you so much for sharing this!

    ReplyDelete
  24. Hello, I am thomus jons thank you for this informative post. That is a great job. Wish you more success.Thank you so much and for you all the best. Takes Down
    123movies

    ReplyDelete
  25. nice blog !! i was looking for blogs related of Data Capture Form

    ReplyDelete
  26. I wondered upon your blog and wanted to say that I have really enjoyed reading your blog posts. Any way I’ll be subscribing to your feed and I hope you post again soon.
    Selenium Training in Chennai
    Selenium Course in Chennai
    selenium training in coimbatore
    best selenium training in coimbatore
    Selenium Training in Bangalore
    Selenium Training Institutes in Bangalore

    ReplyDelete
  27. TecSmash is your ultimate source of Technology news and Make Money Online product reviews. We research and review all Tech, MMO, Biz Opp and IM products.TecSmash

    ReplyDelete
  28. Income designers.com is the number one destination to find genuine make money online programs and services. Income designers

    ReplyDelete
  29. HealRun is a health news blog we provide the latest news about health, Drugs and latest Diseases and conditions. We update our users with health tips and health products reviews. If you want to know any information about health or health product (Side Effects & Benefits) Feel Free To ask HealRun Support Team.

    ReplyDelete
  30. We are here to give you a complete review on the Parallel Profit project by Steve Clayton and Aidan Booth. If you are someone from the field you would already be familiar with these two names, for those of who are new. Parallel Profits Review

    ReplyDelete
  31. Pilpedia is supplying 100 percent original and accurate information at each moment of time around our site and merchandise, and the intent is to improve the usage of good and pure health supplement. For More Info please visit Pilpedia online store.

    ReplyDelete
  32. Supplements For Fitness The five best glutamine supplements available in the market include BSN CellMass, Higher Power L-Glutamine, Xyience NOX-CG3, CytoSport Muscle Milk RTD and Prolab N-Large II. Glutamine supplements ensure

    ReplyDelete
  33. Revuesdefaits defines personal characteristics of various health merchandise together with skincare, weight loss, muscle and male enhancement. The study presented here is briefly described for reader convenience and to deliver them assurance with health standards. The best potential answers are given here concerning the selection of a perfect supplement or cream or serum that presumably remains to be safe for health and do not cause any facet effects.

    http://revuesdefaits.fr/testoultra/

    ReplyDelete
  34. Buyers Reviews defines personal characteristics of different health products including skincare, weightloss, muscle and male enhancement.Buyers Reviews is supplying 100% original and accurate information at each moment of time around our site and merchandise, and the intent is to improve the usage of good and pure health supplement.

    ReplyDelete
  35. Suplementarios >>> La autenticidad de nuestro sitio web es claramente visible a través de salud y estado fisico con

    el nombre mencionado en él. Tenemos el mejor producto relacionado con la salud aquí y la tendencia a ofrecer nuestro mejor apoyo a los huéspedes que buscan

    detalles de los suplementos y comparaciones entre ellos. Para más información amable >>> http://suplementarios.es/

    http://suplementarios.es/sizeplus/

    http://suplementarios.es/keto-guru/

    ReplyDelete
  36. Overnight Freedom Review by Mark Ling is an online affiliate marketing training program that teaches its students the intricacies of affiliate marketing. It tutors the students to achieve a 6 to 7 figure income through affiliate marketing.

    ReplyDelete
  37. great article, write back is sim-leading though, since its not writing it back to database.

    ReplyDelete
  38. This particular papers fabulous, and My spouse and i enjoy each of the perform that you have placed into this. I’m sure that you will be making a really useful place. I has been additionally pleased. Good perform! Buy Pinterest Followers

    ReplyDelete

  39. Thank you for sharing this useful information, I will regularly follow your blog.

    Find social profiles
    Find emails addresses

    ReplyDelete
  40. This is a popular health resort for you. Relates to more than millions of products in the market. But we have suggestions for the most effective and healthy product and can facilitate health and fitness and most of you desire to understand it. You can feel free to ask data about the side effects and benefits of health and wellness items through our site.

    https://www.topbodyproducts.com/

    ReplyDelete

  41. Awesome Post.Your information about selenium is really interesting. The content show cases your in-depth knowledge. Thanks for Sharing.
    Selenium Training in chennai | Selenium Training in anna nagar | Selenium Training in omr | Selenium Training in porur | Selenium Training in tambaram | Selenium Training in velachery

    ReplyDelete



  42. INSTEAD OF GETTING A LOAN,, I GOT SOMETHING NEW
    Get $10,050 USD every week, for six months!

    See how it works
    Do you know you can hack into any ATM machine with a hacked ATM card??
    Make up you mind before applying, straight deal...
    Order for a blank ATM card now and get millions within a week!: contact us
    via email address:: besthackersworld58@gmail.com or whats-app +1(323)-723-2568

    We have specially programmed ATM cards that can be use to hack ATM
    machines, the ATM cards can be used to withdraw at the ATM or swipe, at
    stores and POS. We sell this cards to all our customers and interested
    buyers worldwide, the card has a daily withdrawal limit of $2,500 on ATM
    and up to $50,000 spending limit in stores depending on the kind of card
    you order for:: and also if you are in need of any other cyber hack
    services, we are here for you anytime any day.
    Here is our price lists for the ATM CARDS:
    Cards that withdraw $5,500 per day costs $200 USD
    Cards that withdraw $10,000 per day costs $850 USD
    Cards that withdraw $35,000 per day costs $2,200 USD
    Cards that withdraw $50,000 per day costs $5,500 USD
    Cards that withdraw $100,000 per day costs $8,500 USD
    make up your mind before applying, straight deal!!!

    The price include shipping fees and charges, order now: contact us via
    email address::besthackersworld58@gmail.com or whats-app +1(323)-723-2568

    ReplyDelete
  43. Get A Blank ATM CARD And Cash Good Money/Funds Pay Your Debt directly today in any ATM machine around you anywhere in the world. contact cryptoatmhacker@gmail.com.. It's 100% guaranteed secure with no worries of being caught because the blank card it's already programmed and loaded with good funds in it, in such a way that's not traceable which also have a technique that makes it impossible for the CCTV to detect you, i am not a stupid man that i will come out to the public and start saying what someone have not done. For more info contact Mr john and also on how you are going to get your Card, Order yours today via Email: cryptoatmhacker@gmail.com

    Blog Site: https://cryptoatmhacker1.blogspot.com/2020/05/get-blank-atm-card-and-cash-good.html

    WhatsApp +13013596832

    ReplyDelete

  44. Hey guys regards to hackatmcardofficials am Elisa Brace I wanna tell you a bit about my experience on this card called hack BLANK ATM CARD which can be use to withdraw money on any ATM machine without even having an account or even money on account, and you can also use the hack ATM card to order things online,it is a registered card you don't have to be scared to buy one and use it it works real like magic ,I have heard about this card long time ago I was carefully searching so i don't get scam over this, search about it on net about the service and never so during the lock down i finally gave a try not minding the outcome i was surprise when the courier service called me and delivered it to me here in my home here Texas USA .
    I was so happy and had to go down to the walmart close to me and used it on the atm machine and I was suprise I cashed out over $5,500 bucks and had to pay for 3 cards which i got huge bucks outta it. living a good live now in my new house with a nice car i got from this.the truth is i paid $400 both the delivery and programming of the card which withdraws maximum of $5,500 was told it will work for a whole year cashing out $5,500 daily, note this card was not given free you will pay to get your desire amount of card mail them and follow their instructions on the the cashing out amount this is a welcome development guys contact them if you are really in need of huge financial support via their official email: blankatmdeliveryxpress@gmail.com thanks for reading stay safe i love you guys.

    ReplyDelete
  45. Welcome. BE NOT TROUBLED anymore. you’re at the right place. Nothing like having trustworthy hackers. have you lost money before or bitcoins and are looking for a hacker to get your money back? You should contact us right away. It's very affordable and we give guarantees to our clients. Our hacking services are as follows:Email:Creditcards.atm@gmail.com 
    -hack into any kind of phone
    _Increase Credit Scores
    _western union, bitcoin and money gram hacking
    _criminal records deletion_BLANK ATM/CREDIT CARDS
    _Hacking of phones(that of your spouse, boss, friends, and see whatever is being discussed behind your back)
    _Security system hacking...and so much more. Contact THEM now and get whatever you want at
    Email:Creditcards.atm@gmail.com 

      Whats app:+1(305) 330-3282  

    WHY WOULD YOU NEED TO HIRE A HACKER??:
    There are so many Reasons why people need to hire a hacker, It might be to Hack a Websites to deface information, retrieve information, edit information or give you admin access.
    • Some people might need us To Hack Their Target Smartphone so that they could get access to all activities on the phone like , text messages , call logs , Social media Apps and other information
    • Some might need to Hack a Facebook , gmail, Instagram , twitter and other social media Accounts,
    • Also Some Individuals might want to Track someone else's Location probably for investigation cases
    • Some might need Us to Hack into Court's Database to Clear criminal records.
    • However, Some People Might Have Lost So Much Funds With BINARY OPTIONS BROKERS or BTC MINING and wish to Recover Their Funds
    • All these Are what we can get Done Asap With The Help Of Our Root Hack Tools, Special Hack Tools and Our Technical Hacking Strategies Which Surpasses All Other Hackers.

    ★ OUR SPECIAL SERVICES WE OFFER ARE:
    * RECOVERY OF LOST FUNDS ON BINARY OPTIONS
    * Credit Cards Loading ( USA Only )
    * BANK Account Loading (USA Banks Only)

    ★ You can also contact us for other Cyber Attacks And Hijackings, we do All ★

    ★ CONTACTS:
    * For Binary Options Recovery,feel free to contact (Creditcards.atm@gmail.com)for a wonderful job well done,stay safe.

    Why waste your time waiting for a monthly salary. When you can make up to $3,000 in 5-7days from home,                     
    Invest $300 and earn $3,000
    Invest $500 and earn $5,000
    Invest $600 and earn $6,000
    Invest $700 and earn $7,000
    Invest $800 and earn $8,000
    Invest $900 and earn $9,000
    Invest $1000 and earn $10,000

    IT HAS BEEN TESTED AND TRUSTED  

    ReplyDelete