Tableau is a powerful visualisation tool. However, it doesn’t offer the capability to create tables. Google Sheets can bridge the gap and even go further by combining data collection, translation, and web scraping with Tableau dashboards. In the following sections, we will explore how Google Sheets can enhance your Tableau experience by translating multiple languages, fetching data from the web, and creating date tables using advanced Google Apps Script.
Connecting Google Drive to Tableau (since version 2023.1, no longer directly to Google Sheets due to the deprecated connector) allows you to use Google Sheets for more flexible data management.
When using Google Forms for a survey, you can gather responses in a Google Sheet, analyse data, and visualise results using Tableau. This feature is helpful for organisations like fishing clubs that want to display stats on their website without coding.
Google Sheets doesn’t stop there. It translates text into multiple languages using the GOOGLETRANSLATE() function, making it easy to localise survey questions and answers or other content for multilingual Tableau dashboards.
Here's an example where "Hello World" is translated into several languages:
EN | FR | ES | DE | IT | PT | ZH |
Hello World | Bonjour le monde | Hola Mundo | Hallo Welt | Ciao Mondo | Olá Mundo | 你好,世界 |
Hello again, World | Bonjour encore, le monde | Hola de nuevo, Mundo | Hallo nochmal, Welt | Ciao di nuovo, Mondo | Olá novamente, Mundo | 再见,世界 |
To create this translation table, use the following formula in Google Sheets:
=GOOGLETRANSLATE($A2, $A$1, B$1)
Explanation:
This approach allows you to translate survey questions, responses, or text data dynamically. By feeding these translations into Tableau, you can use parameter actions to create dashboards that adjust the display language based on user preferences, providing a localised and tailored user experience.
Another powerful feature of Google Sheets is its ability to gather data from websites using the IMPORTHTML() function. This function is handy for pulling external data and enriching your Tableau visualisations.
Suppose you want to gather population data from Wikipedia. Here’s how you can do it:
=IMPORTHTML("https://en.wikipedia.org/wiki/List_of_countries_and_dependencies_by_population", "table", 1)
Explanation:
Google Sheets also allows advanced functionality through Google Apps Script. One practical use case is generating a date dimension table, which can be essential when plotting different types of dates (e.g., project milestones, deadlines, or fact dates) on the same timeline.
When managing projects, you should compare actual dates against cut-off dates or plot multiple dates like start, milestone, and end dates on a single timeline. A dedicated date table allows you to independently map these dates without relying on the date structure of your fact tables.
Here’s an example of what a date table could help with:
You can create a custom date table in Google Sheets using Apps Script, which uses JavaScript under the hood. Below is a simplified version of the process, and at the end of the post, you find the functional script:
Important Note: The Apps Script has a time limit for execution. If the script stops due to time limits, note where it left off and adjust the start date for the next run. You can combine the results by copying the data into a new sheet or using the UNION function in Tableau to merge them.
Do you have comments in a readable form that Google Sheets can import? Why not use the free extension "Sentiment Analysis for Google Sheets" to calculate the sentiment of the column containing the comments? You will find the extension in Google's Marketplace. Install it, reload the sheet, mark the column, and select the extension from the menu. Select Start, and you decide which algorithm best fits your use case. Four are available: Movie Reviews, Product Reviews, Finance News and Twitter Messages.
Integrating Google Sheets with Tableau can enhance your visualisations by incorporating translated text, data obtained through web scraping, and even advanced date tables. Whether your goal is to translate survey analysis, gather recent web data, or create timelines, Google Sheets provides robust and flexible tools to elevate Tableau dashboards, transforming them into dynamic, multilingual, and data-rich visualisations.
------------ APP Script for a custom date table -------------
function createDateTable() {
// Define start and end dates
var startDate = new Date(2020, 0, 1); // January is 0 in JavaScript
var endDate = new Date(2030, 11, 31); // December is 11 in JavaScript
// Calculate the number of days between the start and end date
var dayCount = (endDate - startDate) / (1000 * 60 * 60 * 24) + 1;
// Get the active sheet
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
// Set up header row
var headers = ["Date", "Year", "Month", "Month Name", "Quarter", "Week Number", "Day", "Day of Week", "Day Name", "Is Weekend", "Start of Week", "End of Week", "Start of Month", "End of Month", "Start of Quarter", "End of Quarter", "Start of Year", "End of Year", "European Date", "DateKey"];
sheet.appendRow(headers);
// Generate date table
for (var i = 0; i < dayCount; i++) {
var currentDate = new Date(startDate);
currentDate.setDate(startDate.getDate() + i);
var year = currentDate.getFullYear();
var month = currentDate.getMonth() + 1; // January is 0 in JavaScript
var monthName = currentDate.toLocaleString('default', { month: 'long' });
var quarter = Math.floor((currentDate.getMonth() + 3) / 3);
var weekNumber = getWeekNumber(currentDate);
var day = currentDate.getDate();
var dayOfWeek = currentDate.getDay();
var dayName = currentDate.toLocaleString('default', { weekday: 'long' });
var isWeekend = dayOfWeek == 0 || dayOfWeek == 6;
var startOfWeek = new Date(currentDate);
startOfWeek.setDate(currentDate.getDate() - dayOfWeek + 1); // Monday as start of the week
var endOfWeek = new Date(startOfWeek);
endOfWeek.setDate(startOfWeek.getDate() + 6);
var startOfMonth = new Date(year, currentDate.getMonth(), 1);
var endOfMonth = new Date(year, currentDate.getMonth() + 1, 0);
var startOfQuarter = new Date(year, (quarter - 1) * 3, 1);
var endOfQuarter = new Date(year, quarter * 3, 0);
var startOfYear = new Date(year, 0, 1);
var endOfYear = new Date(year, 11, 31);
var europeanDate = Utilities.formatDate(currentDate, Session.getScriptTimeZone(), 'dd/MM/yyyy');
var dateKey = year * 10000 + month * 100 + day;
var row = [currentDate, year, month, monthName, quarter, weekNumber, day, dayOfWeek, dayName, isWeekend, startOfWeek, endOfWeek, startOfMonth, endOfMonth, startOfQuarter, endOfQuarter, startOfYear, endOfYear, europeanDate, dateKey];
sheet.appendRow(row);
}
}
function getWeekNumber(date) {
var start = new Date(date.getFullYear(), 0, 1);
var days = Math.floor((date - start) / (24 * 60 * 60 * 1000));
var weekNumber = Math.ceil((days + start.getDay() + 1) / 7);
return weekNumber;
}
We use three kinds of cookies on our websites: required, functional, and advertising. You can choose whether functional and advertising cookies apply. Click on the different cookie categories to find out more about each category and to change the default settings.
Privacy Statement
Required cookies are necessary for basic website functionality. Some examples include: session cookies needed to transmit the website, authentication cookies, and security cookies.
Functional cookies enhance functions, performance, and services on the website. Some examples include: cookies used to analyze site traffic, cookies used for market research, and cookies used to display advertising that is not directed to a particular individual.
Advertising cookies track activity across websites in order to understand a viewer’s interests, and direct them specific marketing. Some examples include: cookies used for remarketing, or interest-based advertising.