How to use google sheet data to generate analytics?

How can you generate analytics from Google Sheets? Use in-built functions or treat it as a database and connect with a data analytics tool.

Posted by Anubhav De on 2022-04-23
How to use google sheet data to generate analytics?

Despite all modern BI tools and platforms, spreadsheets continue to have their own dedicated community. Having said that, do you think spreadsheets are good for your analytics? Let's evaluate the limitations and possibilities of data analysis on Google sheets.

Google sheets is one of the most powerful cloud spreadsheets for analytics. It has multiple data preparations & formatting features, data analysis tools, and visualization options that can help organizations use Google Sheets to generate analytics.

Analytical functions available on Google Sheets

  • Filter and Sort – You can add data sorting and filtering to each column.
  • Google Sheets Functions and Formulas – Functions and formulas are at the heart of spreadsheet data analysis. You will find basic summarization functions like summation and average to advanced ones like correlation coefficients and standard deviations.
  • Google Sheets Database Functions – A specific set of functions (like DGET, DSUM, DCOUNTA, DCOUNT, etc.) that help you treat a data range on the spreadsheet as a database.
  • Google Sheets Query Functions – You can write SQL-like queries to analyze your data on Google Sheets. You get advanced data manipulation functions on Google Sheets like Aggregation functions, Scalar functions, and arithmetic operators. The query functions run Google Visualization API Query Language Query. Check out the Query Language Docs.
  • Pivot tables – Insert Google Sheets Pivot tables on your selected range of cells to drill down on your data in Google Sheets.

Google Sheet Raw Data

Say the above table represents the Raw data for the sales of a fast food joint for a month. And, now we want to drill down on how to see the sales for each product, for each date—using pivot tables. The following table represents the drill down using the pivot table.

Pivot table example

  • Google Sheets Correlation Coefficient function – Use the CORREL function to find the Pearson's correlation coefficient on Google Sheets. Use this function to establish a relation between the data in two different columns.

Once again, the following spreadsheet will represent the raw data for the sales of a fast food joint for a month.

Google Sheet Raw Data

Now say we want to find the Pearson’s correlation coefficient to find the relation between quantities sold and total sales. A relation closer to -1 would suggest that the higher the quantities sold, the lesser the total sales. A relation closer to 1 would suggest that the higher the quantities sold, the higher the total sales. And a relation closer to 0 would suggest the data in both columns don’t share any significant relation.

Google Sheets Correlation Coefficient function

Google Sheets Correlation Coefficient function

  • Google Sheets Scatterplots – Visualize the relationships between the data in two different columns, by plotting them in scatter plot Charts. Observe patterns with trendlines to make data-driven decisions from Google Sheets. You can also calculate the correlation coefficient directly using the scatter chart.

Let's use a scatter plot on google sheets to visualize sales for each product, on each date. Of course, first, you will need to drill down using a pivot table like showcased earlier—and then insert a Scatter plot.

Google Sheets Scatterplots

Now, when using the Scatter plot to find the correlation coefficient, you can see the result found by using the scatter plot, matches the result found earlier using the CORREL function.

Google Sheets Scatterplots

  • Logical test, IF, and IFS functions – Design your spreadsheet with advanced analytical functions. Write True/False logical tests and complex Google Sheets IF functions. You can also use the IFS function to combine multiple IF functions into one function.

Google Sheets IF functions

Google Sheets IF functions

  • Vlookup – Google sheets Vlookup can quickly find out a particular data from a long-range of data. This is particularly helpful when you are building a subset of data from the parent data set, to make a specific analysis.

Google sheets Vlookup

  • Macro – Google Sheets Macro helps you prepare and format your data instantly. With the Macro recording, you can record your data formatting steps. And the recorded series of steps can be automated on another unformatted set of data.

The above-mentioned features don't even scratch the surface of everything related to analytics on Google sheets. Clearly, when it comes to data analysis, Google Sheets is a giant in the industry with an ocean of features and functions. So do you think you can handle your organization's analytics with Google sheets? You will be able to make a decision once you have gone through the limitations of Google sheets.


Benefits of using Google Sheets as a database.

  • Spreadsheets are familiar. Business owners have a long practice of managing their data on spreadsheets. Thus, owners can manage their own data.
  • It's convenient, lightweight, and friendly to non-tech professionals.
  • It saves the hassle of managing a relational database system like PostgreSQL, MySQL, and such.

Keeping these benefits in mind, almost every modern data analysis platform in existence has considered building connections to Google Sheets, and other Cloud Spreadsheets like Airtable.

Benefits of using Google Sheets as a database


Why in-built features of Google Sheets aren’t enough?

Google Sheets is not a BI tool, to begin with. It's a cloud spreadsheet with analytics and database-like functionalities. So, if you are looking for core business intelligence software solutions, Google Sheets will disappoint you in certain areas.

Limitation 1: Lack of BI features

In 2022, the responsibilities of a data team don't just end with data analysis. There are also the tasks of data monitoring, tracking, sharing, and embedding. Alas! These are not areas where Google Sheets excels. Not, in a convenient way at least.

You can get third-party Add-ons or use multiple complicated in-built functions & features to get some of the modern requirements served. However, that would be considered inefficient, especially when there are better solutions.

Limitation 2: Weak data-integrity

Google Sheets are Cloud spreadsheets so it does protect your data against any technical glitch occurring on your premise. However, if you delete a sheet by mistake—it’ll be lost forever. Thus, Google Sheets are not as strong as databases in protecting your data integrity.

Limitation 3: Limited Storage

Storage limitation is a major disadvantage of using Cloud Spreadsheets for data analysis; particularly for big businesses that deal with larger volumes of data. Google Sheets is limited to storing 5 million records.

Limitation 4: Lack of database features

Although Google Sheets has a few query functions and database functions that help treat Google Sheets as a database—but it’s limited to that. If you want to perform data analysis the traditional database systems provide a few important analytical features that are missing on Google Sheets. Functions like query, search, Join, and Consistency are missing on Google Sheets.

Limitation 5: The learning curve of Google Sheets.

Google Sheets has an extremely steep learning curve. Depending on your capabilities and abilities, it can take from months to even years to master Google Sheets features. If you want to treat this cloud spreadsheet as your primary analytics solution, you must be equipped with analyst-level knowledge and skills in the domain.

In Google sheets, often, you will have to employ multiple steps, functions, and formulas to achieve a specific insight. Additionally, the interface and operation of the features aren't optimized for convenience. Users will often need to spend a lot of time exploring and pondering, even before getting started.

Limitations of Google Sheets


The Solution: Connecting Google Sheets with a BI tool.

In 2022, the most preferred way of using Google Sheets is either treating it as a database or connecting it to a BI platform for all analytical requirements. You can also perform your basic analytics in Google Sheets and then connect it to a BI tool for further visualizations, dashboarding, and other modern data needs.

Using Draxlr for Analytics on Google sheets data

Draxlr is a No-Code Business Intelligence Platform that provides data analysis, data visualization, dashboard building, data monitoring, team collaborations, and analytics embedding & sharing.

Draxlr has data connections to SQL database systems, Heroku, Airtable, and Google Sheets.

You simply sign up on Draxlr and connect your account with your Google Sheets data. The platform will automatically import your Google Sheets data, and make it available for analysis, visualization, monitoring, sharing, and embedding.

Signup now to make better business decisions with Draxlr!

Draxlr

  1. Analyzing data in Draxlr

Draxlr is a completely visual platform where you interact with your data, explore and analyze, without writing a single line of code. Draxlr particularly emphasizes bringing data accessibility and literacy to non-tech professionals–so, data-driven decisions can be made even without a dedicated data team.

  1. The No-Code query builder

The No-Code query builder is the central force that drives convenience on the platform. The query builder stacks five data query tools – Filter, Sort, Join, Summarize, and Group. Combining these tools you will be able to build complex queries on your Google Sheets data.

  1. Save your queries

Draxlr, as a platform of convenience, helps you avoid repetitive work. On Draxlr, every important Query you ever build can be saved and reused later. Build on existing queries to either reuse or update & evolve old queries.

  1. Export data

Just like you can export your Google Sheets data to your local drive, in CSV files, the analysis you build on Draxlr can also be exported to CSV files.

  1. Build interactive visualizations

Plot your query results into line graphs, bar charts, and pie charts. The visualizations will bring clarity to your comprehension of the insights. The data visualizations are overlaid with interactive tools like selection, zooming, and panning. These tools will help you scrutinize minutely information-dense visuals.

  1. Write SQL queries

If you have professionals skilled at writing SQL queries, then put that to good use with Draxlr's SQL query-editor. On Draxlr switch between the no-code query builder and the formatting-enabled SQL editor to analyze your data.

  1. Download charts and Graphs

Similar to the Google Sheets downloading of Graphs and Charts, on Draxlr too, you can limitlessly download the data visualizations in PNG files.

  1. Tell data stories with dashboards

An important role of a data professional is to be able to narrate data stories to Summarize different business issues (or outcomes.) On Draxlr you can build dashboards with your queries and saved queries. The dashboard will give you the data overview and help you monitor your business. And since you will need to address multiple business functions and issues, you can maintain multiple dashboards on Draxlr to keep all your data narratives well organized and categorized.

  1. Sync with changes to original data

You can manually refresh each dashboard analytic to sync with its original data (be it in Google Sheets or a database system.) You can also set up the analytics to automatically refresh at periodic intervals and sync with the data source.

  1. Receive data change alerts

Setup the Draxlr dashboard analytics to send you email and Slack Channel notifications, if data changes are noted upon refresh. This feature of Draxlr generates the benefit of automatic data monitoring.

  1. Data sharing and embedding

Flexible Data delivery is an important modern BI requirement. All your analytics on the Draxlr dashboards and even entire dashboards can be shared, easily, with just a simply shareable URL and a one-time PIN. The very same analytics can also be embedded in your business websites and applications with an embeddable URL or a chunk of code for embedding.

  1. Collaborate with teammates

When a business grows, data diversity and volume will grow along with it. Consequently, it will require multiple professionals from your core team to collaborate on the same set of data to run multiple analytics. This is where Draxlr gives a major advantage over other BI tools. Whilst most platforms charge an additional cost per user invite, Draxlr allows you to invite unlimited users to your account without any additional charges. You will also retain complete governance over the data access of your invited users.

Setup a call with our experts to have your data consultant - for free. We'll discuss your use cases and how Draxlr can be helpful in your business.


Draxlr can truly help you make the most out of your Google Sheets data. If you prefer to perform data analysis on Google Sheets, you can do so–while letting Draxlr take care of the other modern BI requirements that are not served on Google Sheets.

Or, just let Google Sheets act as your database, while connecting it with Draxlr, you allow the modern BI tool to serve your organization's complete BI needs. Connect Draxlr with Google Sheets and make data-driven decisions that yield positive business outcomes.


- Anubhav

Let’s get you started

We use cookies to personalize your experience. Do you allow usage of cookies.