How to make Charts using Data from Google Sheets in JavaScript ?
Last Updated :
25 Jul, 2024
This article will discuss how to create different charts using Data from a Google Sheet.
To export data from a Google Sheet, we will use the SheetDB API. It converts data stored in a Google Spreadsheet into a JSON format API. After that, we will use Fetch API to pull that JSON data into our file. Once we have stored the data, we will use the Chart.js library to plot different types of Charts.
Step 1: Convert Google sheet Data into JSON Data. Create a new Google Sheet and insert some numerical data.
Save that file, then go to File -> Share -> Share with others. Change the general access to "Anyone on the internet with the link can view" and copy the Google Sheet link.
We will use Sheet DB API to get data from this sheet. Create a new free API from here. Enter your google sheet link in the input box shown below.
After the API is created, you will get an API link in the following format.
https://sheetdb.io/api/v1/[Your_API_URL]
On visiting that link, you can see that the Google Sheet Data is now converted into JSON format.
Example:
[{"Month":"January","Cost Price":"2200","Selling Price":"2400"},
{"Month":"February","Cost Price":"3290","Selling Price":"3890"},
{"Month":"March","Cost Price":"1834","Selling Price":"2000"},
{"Month":"April","Cost Price":"3453","Selling Price":"3204"},
{"Month":"May","Cost Price":"2445","Selling Price":"2500"},
{"Month":"June","Cost Price":"2456","Selling Price":"2655"},
{"Month":"July","Cost Price":"1245","Selling Price":"1294"},
{"Month":"August","Cost Price":"2578","Selling Price":"2504"},
{"Month":"September","Cost Price":"4534","Selling Price":"4605"},
{"Month":"October","Cost Price":"4111","Selling Price":"4005"},
{"Month":"November","Cost Price":"3461","Selling Price":"3351"},
{"Month":"December","Cost Price":"4621","Selling Price":"4679"}]
Step 2: Fetch the JSON data: Create a new HTML file and declare JS arrays according to the number of columns in the sheet. Use the fetch method from the Fetch API to get the JSON data from API URL that we created in the previous step. Then iterate over data from all the columns and add it to the respective arrays.
JavaScript
var apiUrl = 'https://sheetdb.io/api/v1/[Your_API_URL]';
var months=[]
var cost=[];
var selling=[];
fetch(apiUrl).then(response => {
return response.json();
}).then(data => {
for(let i =0; i <data.length;i++){
months.push(data[i]['Month']);
cost.push(parseInt(data[i]['Cost Price']));
selling.push(parseInt(data[i]['Selling Price']));
}
}).catch(err => {
console.log(err);
});
Step 3: Create charts using this data: To create different charts from this data, we will use the Chart.js library. Create nan HTML canvas, select the chart type and pass the data you want to visualize.
Example:
JavaScript
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Document</title>
<link rel="stylesheet" href=
"https://cdn.jsdelivr.net/npm/[email protected]/dist/css/bootstrap.min.css"
integrity=
"sha384-Gn5384xqQ1aoWXA+058RXPxPg6fy4IWvTNh0E263XmFcJlSAwiGgFAW/dAiS6JXm"
rossorigin="anonymous">
<script src=
"https://code.jquery.com/jquery-3.2.1.slim.min.js"
integrity=
"sha384-KJ3o2DKtIkvYIK3UENzmM7KCkRr/rE9/Qpg6aAZGJwFDMVNA/GpGFF93hXpG5KkN"
crossorigin="anonymous">
</script>
<script src=
"https://cdn.jsdelivr.net/npm/[email protected]/dist/js/bootstrap.min.js"
integrity=
"sha384-JZR6Spejh4U02d8jOt6vLEHfe/JQGiRRSQQxSfFWpi1MquVdAyjUar5+76PVCmYl"
crossorigin="anonymous">
</script>
<script src=
"https://cdnjs.cloudflare.com/ajax/libs/Chart.js/2.9.4/Chart.js">
</script>
</head>
<body>
<center>
<h3>Charts using Google Sheet Data</h3>
<button class="btn btn-outline-success"
onclick="plotcharts()">
Plot Charts
</button>
</center>
<div class="row" style="width:100%">
<div class="col-md-6">
<canvas id="barchart"></canvas>
</div>
<div class="col-md-6">
<canvas id="linechart"></canvas>
</div>
</div>
</body>
<script type="text/javascript">
function plotcharts(){
var apiUrl = 'https://sheetdb.io/api/v1/ouyqi1r5eoa17';
var months=[]
var cost=[];
var selling=[];
fetch(apiUrl).then(response => {
return response.json();
}).then(data => {
for(let i =0; i <data.length;i++){
months.push(data[i]['Month']);
cost.push(parseInt(data[i]['Cost Price']));
selling.push(parseInt(data[i]['Selling Price']));
}
//For Bar chart
dataset=addData('Cost Price', cost, 'green', 'black');
drawchart(dataset, months, 'bar');
//For Line chart
dataset=addData('Selling Price', selling, 'transparent', 'green');
drawchart(dataset, months, 'line');
}).catch(err => {
console.log(err);
});
}
function addData(title, Data, bgcolor, bordercolor){
return [{
label: title,
data: Data,
backgroundColor: bgcolor,
borderColor: bordercolor,
borderWidth: 1.5
}];
}
function drawchart(dataset, Labels, type){
const ctx = document.getElementById(type+'chart').getContext('2d');
const myChart = new Chart(ctx, {
type: type,
data: {
labels: Labels,
datasets: dataset
},
options: {
scales: {
yAxes: [{
ticks: {
beginAtZero: true
}
}]
}
}
});
}
</script>
</html>
Output:
Similar Reads
Create charts from Google Sheets using Google Apps Script
Google Apps Script is a potent tool that allows you to automate various tasks within Google’s ecosystem. It supports modern JavaScript and can be used to create custom menus, functions, macros, and even web apps for Google Sheets.This article will guide you through the process of creating a chart fr
7 min read
Show Data on Google Pie Chart using Python Flask
In this article, we are going to build a mini project by using a Flask using Python and that will demonstrate to us the use of a google pie chart. A single web page on which input will be taken from users e.g an activity for how long they are performing. After clicking on submit it will generate a p
5 min read
How to Create Charts or Graph in Google Sheets
Google Sheets is a powerful tool for organizing and analyzing data. Creating charts and graphs in Google Sheets allows you to convey information more effectively, whether you’re tracking sales, monitoring trends, or presenting data to others. In this article, explore the steps to create various type
6 min read
Create a chart from JSON data using Fetch GET request (Fetch API) in JavaScript
In this article, we are going to create a simple chart by fetching JSON Data using Fetch() method of Fetch API. The Fetch API allows us to conduct HTTP requests in a simpler way. The fetch() method: The fetch method fetches a resource thereby returning a promise which is fulfilled once the response
3 min read
How to Make a Chart in Google Docs
Creating a chart in Google Docs is an easy process that enhances your documents with clear, visual data representations. This article will provide step-by-step instructions on how to insert and customize charts in Google Docs, helping you present your information in an engaging and easily understand
6 min read
How to Change Chart Colors in Google Sheets
Visualizing your data with charts in Google Sheets is an effective way to communicate information clearly. However, by default, the colors of the chart might not always align with your preferred aesthetic or data categorization. Changing the colors of your charts in Google Sheets can not only improv
6 min read
What Is Google Sheets and How to use it?
Google Sheets designed as part of Google Workspace (formerly G Suite), Google Sheets works seamlessly online, enabling users to manage data, perform calculations, and visualize information through charts and graphs. It is a popular alternative to Microsoft Excel, offering accessibility across device
5 min read
How to Create a Chart from Multiple Sheets in Excel
Creating a chart from multiple sheets in Excel is a powerful way to consolidate data and visualize it in a meaningful way. Whether you're working with different datasets on separate sheets or need to compare data across multiple tabs, knowing how to create a chart from multiple sheets in Excel can s
5 min read
How to Add Text Inside the Doughnut Chart Using Chart.js ?
In Chart.JS, we have a doughnut chart to represent the data in slice forms for a more visual and attractive appearance. In the Doughnut Chart, we can place a custom text inside the doughnut chart to make the chart more informative and also help the readers understand the purpose or data doughnut cha
4 min read
How to Automate Google Sheets with Python?
In this article, we will discuss how to Automate Google Sheets with Python. Pygsheets is a simple python library that can be used to automate Google Sheets through the Google Sheets API. An example use of this library would be to automate the plotting of graphs based on some data in CSV files that w
4 min read