Disclaimer: this is not a new technique but I wasn’t able to find where I learn it from so I decided to document it here.
One of my first practical implementations of Tableau at work was to create a dashboard that allowed collect data, yes COLLECT data and store it in a Google Sheet file. So I found a tutorial online and the end result was pretty cool.
Here are the step-by-step so that you can also create an app in Tableau:
1 | Go to your Google Drive account and create a Google Form with the fields (columns) of data you want to collect. Here is one example:
2 | On the top right of the Google Form screen there is a SEND button. Next there is an ellipse button which contains an option to “Get a pre-filled link”. Click on it:
3 | Google will then show you the form and you need to fill it up in order to get the link. The link will look like this:
The red section: to transform the link into one that will fill the form (instead of view the form) you will need to replace the red section with : formResponse?ifq&usp=pp_url
The blue fields: these are the field IDs in the forms. DO NOT TOUCH these.
The orange data: these is the data that you will need to pass by using Paramaters via URL action.
4 | Still in Google Forms, go back to the Editor screen (the one your used to create the form) and click “Responses” tab. There you will find a tiny Google Sheet icon that allows you to create a spreadsheet in Google Drive to store the data:
And here it is:
Note that Google adds a Timestamp automatically to record the date/time when the form entry was created.
5 | Now we can close the browser and have fun in Tableau (Desktop or Public versions). Open Tableau and connect to the Google Sheet you just created in Google Drive. It will look like this:
6 | Go to Sheet 1 and we now need to create the Parameters that will allow users to fill up data we want to store in Google Sheets:
Note: I am only using string parameters but you can use any type available in Tableau. But when using other types, such as date/time, ensure that the target field in your Google Form is also a date/time.
7 | Right-click your parameters and select “Show parameter control””
8 | Create a Calculated field and name it “Button”. Inside of it you can write the label you want to see in your button, for example “SUBMIT”
Note: at this moment you will get stuck because your dataset is empty. So go back to Google Form and create one record just to be able to build your form in Tableau.
9 | Refresh your dataset in Tableau, then drag the Button field into Text shelf. Add rows and columns dividers to format your button and you may end up with something like this:
10 | Add a new Dashboard to your workbook and drag the Button sheet into it. This will bring your parameters to the dashboard:
11 | Build your form by giving it a nice title, description, position your fields in the screen and add the Button:
12 | To make the Submit button work, you need to create a Dashboard action (menu Dashboard > Actions..) and pick “Go to URL…” action. Then you will use the link from step 3, but now replacing the viewform with formResponse and also add your parameters in the URL:
Note: Make sure your URL action runs on SELECT.
13 | You should be able now to fill up the form in Tableau Desktop and click “Submit” to store the data in the Google Sheet:
14 | You can go back to Tableau Desktop/Public, refresh the Google Sheet dataset and have the same information displayed in Tableau:
15 | Finally you can upload your Tableau App to Tableau Server / Online / Public. In case you are using Tableau Public you can request Tableau to refresh the data for you automatically from Google Sheets. The automatic update will happen once a day:
16 | One final trick. You can request Tableau Public to update the data from Google Sheet on demand. For that you need to be logged in Tableau Public and have your Dashboard open. Then at the bottom right you will find this “Request Update” button:
I hope this is helpful and shows another way you can use Tableau for gathering and analyzing data, all in the same workbook!