Creating a complete web app on Google Apps Script using Google Sheet as database

prasanthmj — Tue, 23 Apr 2019 11:19:23 +0000

Google’s Apps Script should be of interest to all JavaScript developers.
Apps Script is good for automation. You can directly access Google’s many services, such as Google Sheets, Gmail, Google Docs, Google Calendar, and more. See some Google Apps Script starter tutorials here.

With a little bit of imagination, you can create very exciting Apps and add-ons using Google Apps Script.

The first limitation of Apps Script is that it uses an older version of Javascript. It does not support ES6 and its features. Not a big problem. We can use babel and cross compile to a compatible version. The next limitation is that there is no packaging/module setup. You can't directly use npm modules in the Apps Script environment.

Thankfully, there is a command line tool called clasp . The clasp tool makes it easy to upload your Apps Script code.

So in the first part of this project, let us attempt to make use of ES6 and npm modules in Apps Script. We will use Webpack+babel to create a bundle from our source. Then we will upload the bundle using the clasp tool.

See the code for this first part here:
https://github.com/gsmart-in/AppsCurryStep2

Webpack to create the bundle

The most interesting part would be the webpack configuration here:
https://github.com/gsmart-in/AppsCurryStep2/blob/master/webpack.gas.js

In the server folder, you can see two files: api.js and lib.js

api.js is the one that is directly exposed to Apps Script. This file can contain Apps Script compatible code only and is directly uploaded. This file will contain global functions only because that is what Apps Script will understand.

lib.js is our bundle entry point. We can use ES6 and npm modules in lib.js and any of its dependencies. In summary the call structure will be:

api.js -> lib.js -> your custom modules.

Coming back to our webpack configuration

output: 
  {
    filename: '[name].bundle.js',
    path: path.resolve(__dirname, 'dist'),
    libraryTarget: 'var',
    library: 'AppLib'
  }

The library target is set to 'var' and library name 'AppLib' so the exported identies in the library(lib.js) can be accessed like this in api.js:

function doGet() 
{
    var output = 'Today is '+AppLib.getTodaysDateLongForm()+"\n\n";

    return ContentService.createTextOutput(output);
}

In the modules section of webpack configuration , we have the rules for babel cross compilation.

Also, we use the Copy plugin to simply copy the api.js file to the output folder (from the output folder we upload the generated code to Apps Script using the clasp tool)

Using npm modules

In lib.js we can use ES6 or any advanced features you want. It can include other modules as well.


import * as moment from 'moment';
import _ from 'lodash';

function getTodaysDateLongForm()
{
    return moment().format('LLLL');
}

In order to try this app, clone the project and follow the instructions in the readme page.

Read the detailed article here: Using ES6 and npm modules in Google Apps Script.

Creating a Rich UI for the Apps Script project

Let us build a “Request Management” App. The app will be a Single Page App. You will be able to create new requests (for Laptops, phones, etc) and an admin can view, approve or reject those requests. In the back-end, the app will add the requests to a Google Sheet. So Google Sheet acts as a database.

Here is the complete project:
https://github.com/gsmart-in/AppsCurryApp

See a quick demo of the app here:

The code is divided into two sections: “client” and “server”. The client folder contains the Vuejs, SCSS and the index.html template We combine all these together to create the bundle that is the index.html file that gets uploaded.

The server folder contains the code that runs in Apps Script. The code in ES6 gets compiled to Apps Script compatible code and gets uploaded.

The server folder also contains unit tests for the server side code

You have to bundle and inline all of CSS and Javascript to a single HTML file!

One limitation of Google Apps Script is that it has only one function to show your page. There is no quick way to link your javascript/css files just like you would do in a normal website. So you have to bundle all JS/CSS and inline it.

So we use HtmlWebpackInlineSourcePlugin webpack plugin to inline the generated CSS and JS in to index.html

Here is the webpack configuration:

const path = require('path');
const merge = require('webpack-merge');
const common = require('./webpack.common.js');
var webpack = require('webpack');
const HtmlWebpackPlugin = require('html-webpack-plugin');
const HtmlWebpackInlineSourcePlugin = require('html-webpack-inline-source-plugin');


module.exports = merge(common, {
    mode: 'production',
  output: {
     filename: '[name].bundle.js',
     path: path.resolve(__dirname, '../deploy/gas')
    },
    plugins: [
     new webpack.ProvidePlugin({
          $: 'jquery',
          jQuery: 'jquery',
          'window.jQuery': 'jquery'
     }),
     new HtmlWebpackPlugin({
          template: 'client/src/index.html',
        inlineSource: '.(js|css)$'
     }),
     new HtmlWebpackInlineSourcePlugin()
   ]
});

Conclusion

The purpose of this project was to see how far we can extend the limitations of Apps Script to make it a platform for building complete web apps. Although you can’t build production grade or even limited user base apps on this platform yet, this throws some light on the potentials of such a platform. Imagine being able to create apps, push it and never need to worry about the scaling issues. Imagine a platform that comes ready with user access management built-in. Maybe, one day, this example will help create such a better, scalable universal app platform.

Video Demo of the App

See the complete demo of the app(cloning the project and creating an instance )

DEV Community: prasanthmj