This is a simple step-by-step tutorial of how you can get data from Google Sheets on Google Drive with only a AJAX call.
So, I googled around for like a full day and a half for “google sheets api”, “use google sheets as database” and similar things. But they all give examples of more complex solutions that is more fitted when using frameworks like Angular & React. That was not what I was looking for. I wanted the most simple way to alter a website through a google sheets document.
I wanted something as easy as:
And when I put my request into those words, I finally found out what my google searches was missing... AJAX! I never once thought about adding AJAX into my searches.
So, I cmd+t (a new tab) and type “google sheets api ajax call” into the address bar.
And sure enough, there it is. The first link I see. Right on the holy site, our church, Stackoverflow, with the title “google spreadsheets - ajax call (get and post)”.
This is what made the penny drop.
<!DOCTYPE html>
<html>
<head>
<title>Google Spreadsheet as Data with AJAX</title>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.1.3/css/bootstrap.min.css">
</head>
<body>
<div id=”data_goes_here”>
<!-- Perfect place to put the data -->
</div>
<script src="https://code.jquery.com/jquery-3.4.1.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.14.7/umd/popper.min.js"></script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.min.js"></script>
<script src="script.js"></script>
</body>
</html>
var url = 'https://docs.google.com/spreadsheets/d/e/<DOCUMENTID>/pub?output=csv’;
// <DOCUMENTID> is the id of the spreadsheet that you want to get data from
$.ajax({
url: url,
success: function (data) {
// this is where you can do what you want with the data
// for example, make buttons
$('#data_goes_here').html(''); // clear the
var item = data.split("\n");
$.each(item, function (i, ioo) {
var btn_array = ioo.split(",");
var button = '<a class="btn btn-primary" href="' + btn_array[1] + '">' + btn_array[0] + '';
$('#data_goes_here').append(button);
});
},
error: function (err) {
console.log(err.status);
}
});