How to make an AJAX call to get data from Google Sheets

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.


The Story

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:

  • creating an html document
  • with a script tag (or js-file)
  • an ajax call to get the contents of a google sheets document on the website

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.

what you need and already should know:

what to do

Create a spreadsheet and publish it

Create a new spreadsheet.
...
Add some data.
...
Publish on the web. (VERY IMPORTANT)
...
Select what part of the document you want to publish.
...
Select what type of data you want.
...

Create a html-document

just like this Show data
<!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>

Create a js-file or script tag

the javascript code
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);
  }
});