Write Back to Google Sheets from Tableau

Are you ready to build some Google Sheets write-back directly from Tableau in just a few easy steps?

google_forms_7

Well then, read on…
___________________________________________________________________________

Tech savvy? Skip to the end and here is the URL that you are looking for.

https://docs.google.com/forms/d/e/1FAIpQLScvBRmtsC4kjAa5z9mxwp_Q4Kv4RlIH_xORQs-L9ZSIes85LQ/formresponse?entry.1899794355=123&entry.1464131260=Sample+annotation&submit=Submit

Start with a Google Form pre-popluated link.  Use the ‘formresponse’ protocol and add ‘&submit=Submit‘ to the end of the URL.

 

If that makes no sense, then keep reading for the easy step-by-step instructions.

To get started we need both a Google Sheet created and a Google Form. The easiest way I found to get started was to begin with a new sheet.

 

google_forms_1

 

Once you have the sheet, use the ‘Tools > Create a form’ menu to add a new form.

 

google_forms_2

 

Name your new form and create your core questions. Make sure to set the data entry type, and because we are going to pass the values through the URL you don’t need options like ‘multiple choice.’ If you were going to record user driven notes/annotations, make sure to capture some sort of unique ID and/or date to match it to your other data.

 

google_forms_3

 

Now we want to get the link to populate values with a URL. Click on the three dots at the top right to ‘Get pre-filled link.’

google_forms_4

 

The pre-filled link will want to know what your default values are. This isn’t a big deal, because we will be replacing those with variables.

 

google_forms_5

 

Here is what the data will look like, and the data that we will be connecting to for sourcing our Tableau Dashboard.

 

google_forms_6

 

That link will come up with the Unique ID of your Google form and the IDs for each of your fields.  Here is the link from my example: 

https://docs.google.com/forms/d/e/1FAIpQLScvBRmtsC4kjAa5z9mxwp_Q4Kv4RlIH_xORQs-L9ZSIes85LQ/viewform?entry.1899794355=123&entry.1464131260=Sample+annotation

 

From here I can determine the following.

 

Google Form ID is:  1FAIpQLScvBRmtsC4kjAa5z9mxwp_Q4Kv4RlIH_xORQs-L9ZSIes85LQ

Field 1 is:  1899794355

Field 2 is:  1464131260

Additionally, I can see that spaces are indicated by a Plus sign.

After calling up this link, I can press submit a few times to get some data flowing into my spreadsheet.  If I wanted to pass data dynamically from my Tableau workbook, I can build a dynamic URL that will load up this form in either a web page object or a new window.

 

But wait, there’s more!

 

If I have already obtained all of the information that I needed for my form (consider that you could add parameters as well to collect additional information), why does my user need to click submit?

 

With a bit more research, I found that you can alter the URL to submit automatically.

 

You change the form type from viewform to formresponse and add &submit=Submit to the end of the URL string.

 

https://docs.google.com/forms/d/e/1FAIpQLScvBRmtsC4kjAa5z9mxwp_Q4Kv4RlIH_xORQs-L9ZSIes85LQ/formresponse?entry.1899794355=123&entry.1464131260=Sample+annotation&submit=Submit

 

If you target this to a web page object that is either hidden in the dashboard, or just loaded off the screen, the user will never be prompted and you will automatically store the feedback.  My only issue from a User Experience perspective is that this has no confirmation that the form has been changed or accepted.

 google_forms_7

I’m sure with some additional steps in Tableau that we could refine this experience.

 

If you would like to try this out and download a workbook with a working example, please check out my new color palette generator which allows users to rate the palettes stored in the dashboard.  I look forward to seeing how others think that we could leverage this technique for dynamic write-back on Tableau Public.

http://tabsoft.co/2g0GxRh

 

google_forms_8