Get the opportunity to grow your influence by giving your products or services prime exposure with Performance Magazine.

If you are interested in advertising with Performance Magazine, leave your address below.

Advertise with us
Free Webinar

Posts Tagged ‘Google Sheets’

How To Establish Self-service Bi System Using Google Sheets and Datawrapper


Image Source: Oleg Magni | Pexels

To learn more about implementing a self-service business intelligence system, read the author’s previous article: “Embracing Data Visualization: What Is a Self-service BI System?”


Google Sheets needs no further introduction, but let me make a reminder that it builds on arguably one of the simplest cloud-based storage solutions, Google Drive. That will help us build a basic data warehouse that will be connected to the visualizations, as explained later in the steps. Datawrapper is also a famous online-based data visualization tool that is commonly used by news organizations, but also can be used by any business, as we will see in the how-to section of the article.

How-to time!

The how-to section will be divided into two parts; the first on how to establish the system, and the second on how to use it afterwards. These two will be followed by a final part that includes additional tips to keep the system sustainable and in best shape. So, let’s get started!

Part 1: Establishing the system

Step 1: Create your Google Drive data warehouse
  • If you are not using Google Drive for file storage at your organization, you can easily do so by creating an account.
On Google Drive > Click: Go to Drive > Click: Use another account > Click: Create account > Follow instructions  

GIF 1: Creating a Google Drive account | Source: Author

  • Create folders to categorize and build a hierarchy for the data files that you’ll add later. For instance, you can create a folder for each division in your company and then subfolders for each team within the divisions.
On Home Page > Click: New > Click: Folder  

GIF 2: Adding a folder on Google Drive | Source: Author

  • In each folder, upload the relevant data files.
Inside the desired folder > Click: New > Click: File Upload  

GIF 3: Adding a file on Google Drive | Source: Author

OR   Drag the desired files/folders from your computer > Drop it in Google Drive
  • Give suitable access permissions to your team members to relevant folders.
Right Click on desired folder > From dropdown menu, Click: Share > Enter colleagues email addresses > Click: Done  

GIF 4: Sharing Google Drive folder with colleagues | Source: Author


Step 2: Prepare datasets for Google Drive

As we will be connecting the datasets you’ve just uploaded to Datawrapper, we need to structure them in the way that the visualization tool can best read and interpret them. Here are a few things to make sure of:
  • There must be only one header row.
  • Do not merge cells.
  • Eliminate thousand separators as they will be automatically inserted on Datawrapper.
  • In the columns of values, do not mix letters with digits (You can add prefixes and suffixes later on Datawrapper, if you need to).
  • Inside the tables, do not leave any blank cells:
    • If the value is unknown/non applicable, put down a dash “-”.
    • If the value is zero, write a zero digit “0”.
  • Do not write any notes, source… etc. below the table in the spreadsheet. That can be added later on Datawrapper.

Step 3: Initiate your visualizations catalogue

  • Create a Datawrapper account.
On Datawrapper > Click: Login > Click: Create a new account > Follow instructions  

GIF 5: Creating a Datawrapper account | Source: Author

  • Start creating one type of visualization for each type of data you have. The visualizations you’ll create will serve as templates for your colleagues when they start using the system. 
On Datawrapper Dashboard > Click: Create New > Click: Chart  

GIF 6: Creating a chart on Datawrapper | Source: Author

  • Connect the spreadsheet from your Google Sheets with the chart
First: Get a shareable link for the sheet | Go to your spreadsheet on Google Sheets > Click: Share > Click: Change to anyone with the link > Click: Copy link  

GIF 7: Getting a shareable link from Google Drive file | Source: Author

  Second: Paste the link in Datawrapper chart | Go to Upload Data page on Datawrapper > Click: Connect Google Sheet > Paste the earlier copied link in the text box  

GIF 8: Linking Datawrapper to Google Sheets| Source: Author

  Hint: You may need advice from a data visualization expert on what visualization type to choose for what type of data to achieve what purpose. Here are a few general guides for the most common types of data:
  • For timeseries data with one or two values, choose a line graph.
  • For timeseries data with more than two values, choose an area graph.
  • For a simple comparison, like comparing values over a few months, choose grouped column chart.
  • For a little bit more complex comparison, choose a single column chart with a filter above.
  • Publish your chart.
Go to Publish & Embed > Click: Publish Now  

GIF 9: Publishing a Datawrapper chart | Source: Author

  • Create the previous steps to create one chart for each type of data as explained above.
Congratulations! You’ve successfully established your SSBI system with Google Sheets and Datawrapper!

Part 2: Using the system

Use 1: Creating new visualizations Users in your team will be able to create new visualizations by following the same previous steps that you followed in Part 1, except:
  • Instead of creating a new visualization from scratch, they can duplicate an already formatted existing one, and just change the data source.
First: Duplicate an existing chart | On Datawrapper dashboard > Click: Archive > Choose the closest chart representing similar data > Click on the chart > Click: Duplicate Second: Change data source | Copy and paste the new data source in the duplicated chart’s Upload Data page
  • Republish the chart to get the changes in the new chart reflected.
Go to Publish & Embed page > Click: Republish  

GIF 10: Duplicating a Datawrapper chart | Source: Author


Use 2: Editing or updating existing visualizations

Users can also edit or update an existing visualization
  • If editing or updating the data itself is what is needed, you’ll need to edit or update the source data spreadsheet, and then go to the connected chart on Datawrapper to republish it, so it reflects the new changes in the data source.
Go to Google Sheets > Edit/Update the dataset > Go to the connected chart on Datawrapper > Go to Publish & Embed page > Click: Republish  

GIF 11: Updating a Datawrapper chart | Source: Author

  • If editing or updating the chart itself is what is needed, you can head directly to the chart, make your changes and republish as in the previous steps.

Use 3: Exporting and embedding visualizations

Now, once the visualization is created, it can be exported in both interactive and static forms, to be embedded in web pages, pdf reports… etc.
  • To get an interactive version of your chart, you’ll need to get its HTML code and paste it into the editor of your web page.
First: Get the code | Choose the desired chart > Go to Publish & Embed > Under Embed code for your visualization > Click: Copy Second: Paste the code in your web page | Go to the backend of your web page > In the text editor > Click: Text (Name and form below may differ) > Paste the code > Save the page > Should Appear on your front end  

GIF 12: Embedding an interactive Datawrapper chart | Source: Author

  • To get a static version of your chart, you’ll just need to export it as an image, and then you can use it as any other image.
Choose the desired chart > Go to Publish & Embed > Under Export or duplicate visualization > Click: PNG  

GIF 13: Embedding a Datawrapper chart as an image | Source: Author

  Congratulations! The SSBI system is now up and running!

Additional tips

For this system to keep working efficiently and with as little flaws as possible, here are a few tips of good practice:
  1. Make your data folders and files naming systematic and understandable. Then, name the connected chart with the same name of the spreadsheet. That will enable easier reaching for users in the future, using the search function on both tools.
  2. You can let your team use the same account you created for Datawrapper, but that of course is not advisable. Instead, you can create teams within your account, and invite relevant members through email, assigning specific access authorities (in the same fashion of giving access on Google Drive). For more on this, you can see Datawrapper Academy articles on how to create a team and how to invite others.
  3. Create a shared guide document with your team containing detailed steps, color codes and all standards for all the uses above (You are also welcome to embed a link to this article as well!).
  4. As an appendix to that guide, add a part for the technical issues that faced your team when working with the system, and document how you solved them. Keep that part updated with every new issue coming up.
  5. Assign yourself or one of your team members the responsibility of doing regular checkups on datasheets and newly created or updated charts, to make sure you are avoiding human errors and random technical ones as well.
  6. Depending on your needs, you might consider paid plans of Google Drive and Datawrapper. Click here and here respectively to see what they have to offer.
That’s it! Thanks for bearing up with us all the way down to this point! Now before you go, we have one more thing to say. If you would like to discover new knowledge and the practical application of best practices used in analyzing statistical data, sign up for The KPI Institute’s Data Analysis Certification.


The KPI Institute’s 2024 Agenda is now available! |  The latest updates from The KPI Institute |  Thriving testimonials from our clients |