Use data from Google Drive in static websites

Sometimes you work with a client that just have some simple content that needs to be updated; like a news feed/some list/table-data, maybe already created in a Google Spreadsheet. What if you could use that data on a Jekyll website? Well, of course you can. Let’s do this.

The Google Drive API

First, head over to The IAM-admin in Google Developers API Console. The API Console lets you discover and use Google APIs, such as Google Drive, Maps and YouTube…

Create a new project

Enable the Google Drive API

Create credentials

Click “Create credentials” to get started.

Select what kind of kind of credentials you need, we need to select the Google Drive API from node.js.

Create a service account

• Select an account name, with the Owner role.
• Select a name, whatever you like.
• Choose the JSON Key Type, we will be using this in your Jekyll site.

The JSON file will be downloaded to your computer. Place this in the root of your Jekyll project.

Remember to not version control this file.

The service account ID

Important Note: Your project now has a service account. It also has an unique email address. Write this down, we will use it later.

The email address looks like this:

XXXXXX@ascendant-hub-YYYYYYY.iam.gserviceaccount.com

Create a Google Spreedsheet

Go to Google Drive and create a new Spreedsheet. Or if you already have a spreadsheet with some content, open that up.

Add content

Share the spreadsheet with your IAM user.

Remember the email address you wrote down before? Yep, now is time to use it. Share the spreadsheet with this email address.

Also, of course share the spreadsheet to everyone else that is going to manage the content. 👨‍ 👩‍

Go get that data in to our website

Next up, we will create our script to fetch the data from our Spreadsheet.

I am using Jekyll in this example, but you can use any static site generator of choice, or together with a JavaScript framework like React, Vue.js etc. Just save data as JSON instead of YAML.

Setup

We are going to write a Node.js script for connecting to your spreadsheet, and saving data as markdown. First things first we need to install the google spreadsheet module, and some helpers.

npm i -D google-spreadsheet async babel-cli babel-preset-es2015 babel-preset-stage-0

Create a .babelrc file

{
  "presets": ["es2015", "stage-0"]
}

Create the script

First, import libraries

import GoogleSpreadsheet from "google-spreadsheet";
import async from "async";
import fs from "fs";

First, create a reference to our document and our sheet.

const doc = new GoogleSpreadsheet(
	// Using our example spreadsheet: https://docs.google.com/spreadsheets/d/1o_NWrDsVNSVU1jViexKQi08aI6h76fXrMIcvbT9faoM/edit#gid=0
  "1o_NWrDsVNSVU1jViexKQi08aI6h76fXrMIcvbT9faoM"
);
let sheet;

Then we want to do this:

1. Connect to our spreadsheet
2. Get data
3. Save as YAML in a Jekyll data file (you could also save as regular posts)

async.series([function setAuth(step) {
  const creds = require("./newproject-20015697d7f6.json");
  doc.useServiceAccountAuth(creds, step);
}, function getInfoAndWorksheets(step) {
  doc.getInfo((err, info) => {
    console.log(
      "Loaded spreadsheet: " + info.title + " by " + info.author.email
    );
    // Using the first sheet
    sheet = info.worksheets[0];
    console.log(
      "sheet 1: " + sheet.title + " " + sheet.rowCount + "x" + sheet.colCount
    );
    step();
  });
}, function getStuff(step) {
  sheet.getRows(
    {
      offset: 1,
      limit: 20,
      orderby: "col1"
    }, function(err, rows) {
      console.log("Read " + rows.length + " rows");
      // Clean posts.yml
      fs.truncate("./_data/posts.yml", 0, () => {
      });
      // Save rows as items in the YAML file
      for (let row of rows) {
        fs.appendFile(
          "./_data/posts.yml",
          "- date: " +
          row.date +
          "\n\x20\x20" +
          "title: " +
          row.title +
          "\n\x20\x20" +
          "content: " +
          row.content +
          "\n\n",
          err => {
          }
        );
      }
    }
  );
}
]);

Create a NPM script for running our script

"scripts": {
  "getdata": "babel-node getdata.js"
},

And then:

npm run getdata

Render data in a template

Now we have a file with all our data in _data/posts.yml.

We can now render the posts in a template:

<ul>
    {% for post in site.data.posts %}
    <li>
        <h2>{{ post.title }}</h2>
        <time datetime="{{ post.date | date_to_xmlschema }}">{{ post.date | date: "%B %d, %Y" }}</time>
        <p>{{ post.content }}</p>
    </li>
    {% endfor %}
</ul>

Note: Of course if you need custom urls, just save in _posts instead and add the corresponding YAML variables, like permalink, date etc.

Great success!