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 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= integrity= "sha384-Gn5384xqQ1aoWXA+058RXPxPg6fy4IWvTNh0E263XmFcJlSAwiGgFAW/dAiS6JXm" rossorigin="anonymous"> <script src= integrity= "sha384-KJ3o2DKtIkvYIK3UENzmM7KCkRr/rE9/Qpg6aAZGJwFDMVNA/GpGFF93hXpG5KkN" crossorigin="anonymous"> </script> <script src= integrity= "sha384-JZR6Spejh4U02d8jOt6vLEHfe/JQGiRRSQQxSfFWpi1MquVdAyjUar5+76PVCmYl" crossorigin="anonymous"> </script> <script src= </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 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:
