Nov 18, 2017

Update: A more comprehensive PHP CRUD API in just one file which can be used with React or Angular SPAs.

I wrote one of my earlier posts about an Angular Flat File CMS and wanted to follow through on the ideas of that post. At the time, I did not realize how powerful Google Sheets can be, and the fact that it can actually perform GET and POST AJAX commands directly from the spreadsheet. Then it dawned on me after using Sheets as a database backup that it could also serve as a super simple CMS method to enable non technical people to edit and update site content on the fly.

We will take a spreadsheet like this:

And have it automatically update content on a simple Angular 4 App with minimal styling:

This can be extended to however many rows or columns you want and any type of content, i.e. even image href addresses could be stored in the spreadsheet to represent images that should appear in certain locations.

Overview

We will be creating a very basic Content Management System in which a person can use a Google Sheet to dynamically update static content on an Angular or React style website with generic components that consume JSON data.

  1. Writing files with PHP
  2. Google Sheets
    1. Google Scripts (JavaScript)
      1. Rows and columns to JSON data
      2. POST JSON data to PHP file
      3. Simple Button
  3. Angular 4 JSON consumption
    1. Menu example

Full Code

The full code for this project can be found on GitHub.

Writing files with PHP

The entire premise of this CMS is that we need to write to .json files which will be consumed by the Angular 4 app we create later. In order to do this, I will use a very simple approach: PHP. With PHP we will require a special key as a GET variable and then accept PHP content as the file contents of a specific json file. What you should keep in mind here is you can extend this to add an additional GET variable for the file prefix (i.e. menu for a file called menu.json) in order to write multiple tabs from one Google Sheet to multiple website pages! For now, we will write to just one predefined file:

<?php
if($_GET['key']=='rQLFqUYa8LnwcIHqR47R68tg3bnFWEMfELLOvl') {
  file_put_contents('menu.json', file_get_contents('php://input'));
  echo "success";
} else {
  echo "Unauthorized";
  echo $_GET['key'];
}
?>

It is ridiculously easy to write to a JSON file all of the POST data which in this case will be JSON that is written from our Google Sheet. I defined a random key that we will use in our Google Sheet to ensure we are authorized. This isn’t the actual key I used, the one I used was actually longer than this and I recommend using a random key generator.

Google Sheets

Go ahead and navigate to Google Drive or Google Sheets directly and make a new Spreadsheet. I put in some random fake menu items just to have something to work with for this fun experiment. You can add many more items than I have below, and the intent is you could have multiple tabs represent multiple files, but for this article we will do just one file to get you going.

Google Scripts (JavaScript)

Now that we have the data in our Google Sheet, it is extremely easy to convert it to JSON and send it to our PHP file writer. Navigate to Tools -> Script Editor which will bring you into Google Scripts which runs on what is essentially JavaScript but with custom objects for Sheets and AJAX. Trust me, coding in JavaScript is way more enjoyable than VBA and this will be a breeze.

Rows and Columns to JSON

The first step in sending the spreadsheet data to our PHP file is converting it to a format that Angular can parse: JSON. Below you can find my Google Scripts code to do this:

function DatatoJSON() {
  var _json = [];
  var ss=SpreadsheetApp.getActiveSpreadsheet();
  var sheet=ss.getSheets()[0];
  var data = sheet.getDataRange().getValues();
  //Logger.log(data);
  for(var r = 1; r<data.length;r++) {
    var element = {}
    for(var c = 0; c<data[0].length;c++) {
      element[data[0][c]] = data[r][c];
    }
    _json.push(element);
  }
  Logger.log(JSON.stringify(_json));
  return JSON.stringify(_json);
}

Here we take the first row and assign it as the keys for each element in a list of JSON objects, with the values being equal to the column values per row. I uncommented the first Logger.log line above and ran the code. Going to View -> Log shows us the following output:

You can see how the list of lists was converted into a list of JSON objects. Good to go!

POST JSON Data to PHP File

This part isn’t so hard either, but it is different than how you would do it with traditional JavaScript. Google Scripts has a special function, see the simple implementation below:

function sendData() {
  var query = DatatoJSON();
    var url = 'http://acostanza.com/php-cms/assets/writeMenu.php?key=rQLFqUYa8LnwcIH0TI83fO4g3bnFWEMfELLOvl';
  var options = {
   'method' : 'post',
   'payload' : query
 };
    var response = UrlFetchApp.fetch(url,options);
  Logger.log(response);

  if(response == "success") {
     SpreadsheetApp.getUi().alert('Successfully updated content.');
  } else {
    SpreadsheetApp.getUi().alert('Failed to update content.');
  }
}

In addition to writing to my PHP file that was detailed at the beginning of the article, this small snippet also checks the response and provides an alert to the user with the results (success or failure). Let’s see what an alert looks like:

Simple Button

To make this Google Sheet really user friendly, let’s hook up a button to our function. All you need to do is Insert -> Drawing and you can draw a box and type the button content you want. Once that is complete, accept and insert the button into the spreadsheet.

You can then click the upper right 3 circles and “Attach Script”. Then type the name of the script in:

Now that we have a button, you can see the final result below:

Clicking the button runs the script and navigating to menu.json provides us with the JSON just as we hoped!

Angular 4 JSON Consumption

Now let’s just do a really quick and dirty consumption of the JSON from Angular 4. I’m not going to make it beautiful, but just a quick example of how easy it can be to consume the JSON content. The hard part is honestly more in the styling and design of it. You can see the result below:

This is just our menu represented in HTML and CSS after being consumed by the Angular app from the JSON file. Making any change to the Google Sheet will automatically reflect in our page (after a refresh). The main code is below:

data.service.ts

import { Injectable } from '@angular/core'
import { HttpClient, HttpHeaders  } from '@angular/common/http';
import {Observable} from 'rxjs/Observable';
import 'rxjs/add/observable/of';
import 'rxjs/add/operator/map';
@Injectable()
export class DataService {
  constructor(private http: HttpClient) {};
  getData(): Observable<any[]> {
    let token = localStorage.getItem('token')
    return this.http.get('assets/menu.json')
      .map(response => {
        return response;
      });
  }
}

A very simple and generic service to grab any data from a JSON file.

app.component.ts

import { Component, OnInit } from '@angular/core';
import {DataService} from './data.service';
@Component({
  selector: 'app-root',
  templateUrl: './app.component.html',
  styleUrls: ['./app.component.css'],
  providers: [DataService]
})
export class AppComponent implements OnInit {
    name:string[] = [];
    desc:string[] = [];
    price:string[] = [];
    constructor(private ds: DataService) { }
    ngOnInit() {
      this.ds.getData().subscribe(res => {

        console.log(res);
        for(let g=0;g<res.length;g++) {
          this.name[g] = res[g]["Name"];
          this.desc[g] = res[g]["Description"];
          this.price[g] = res[g]["Price"];
        }
      });
    }
}

And finally app.component.html:

<div class="container">
<div *ngFor="let n of name; let i=index" class="row">
  <div class="col">{{n}}</div>
  <div class="col">{{desc[i]}}</div>
  <div class="col">${{price[i]}}</div>
</div>
</div>

Now you can see how little code it really takes to make a full blown CMS that can be consumed by Angular 4!