Serverless Function to Sync Data from a Database to Google Spreadsheet

Serverless Function to Sync Data from a Database to Google Spreadsheet

Build a Lambda function that pulls data from Postgres DB to Google Spreadsheet.

Most business users are adept at analyzing facts on spreadsheets. Give them a ton of data points in an Excel sheet or Google Spreadsheet, they will be able to formulate Pivot tables, VLOOKUP functions and generate fancy visualizations to make the data palatable and ready for interpretation. Most startups depend on a folder of such spreadsheets to draw their weekly/monthly metrics before moving on to sophisticated Analytics tools.

But, the source of that data is mostly residing in some form of RDBMS or NoSQL database. We depend on various Data Warehousing solutions to import this data and run analysis reports on top of them. What if we could import that data seamlessly to spreadsheets on a continuous basis and remove the manual error-prone human intervention there?

This blog post walks through a Lambda function that given the source, pulls the data in JSON format and dumps it in a Google Spreadsheet. The function can be scheduled to poll every hour or as needed to ensure the data is as latest as demanded by business. The source does not always have to be a database system, anything that we can interface over wire and get the response in JSON will work.

Technology Choices

  1. We will be writing the serverless function in Node.js since the core of the functionality is JSON wrangling
  2. The standard googleapis npm package to interface with Google Spreadsheets
  3. In this example, we will use Postgres as the source of truth, but the setup should literally work for any database and with some tweaking any source of JSON. The node-postgres package will serve us well to talk to PostgreSQL.

The final setup will look as captured in the diagram below:

Bootstrapping the project

I have setup a starter kit that you can clone and use as baseline for kick-starting the project. Clone the repo and set it up using the following commands:

$ git clone [email protected]:rcdexta/lambda-google-sheets.git
$ cd lambda-google-sheets
$ npm install

Note: Make sure you have already Node.js 8.10 or higher to support all the constructs in the code### Setting up the Google Sheets API

We must setup the Google OAuth token to let our script write to Google Spreadsheets. When you are logged into a google account, navigate to the Google Sheets API Console page and click on the Enable the Google Sheets API button. This will open up a modal where you can choose a new project name. The next screen will let you download a file called credentials.json. Download and save it in the lambda-google-sheets folder.

To test if the sheets api works fine, open a new google sheet under the same account that you used to create the token and copy the spreadsheetId from the URL. So if the URL is of the following form, everything after /d/ before next slash is the spreadsheet identifier.

Note: Make sure you have already Node.js 8.10 or higher to support all the constructs in the code
Refer and replace the spreadsheetUrl and spreadsheetId in config.json file in the codebase. Note, that the first sheet name in the workbook is assumed as Sheet1

Now, run node googleapi.js and you will be asked to a copy a URL and open it on the browser. Do it and you will download a file called token.json at the end of OAuth confirmation and if you check the spreadsheet you will see the following contents on the file:

Connecting to Database

Checkout the contents of pgClient.js file. You can either pass the database connection params as environment variables (which is needed when deploying to AWS lambda) and fallback to literals for testing in local database.

pgClient.js

const { Client } = require('pg')

const pg = {
  init: async function() {
    const client = new Client({
      user: process.env['DB_USER'] || 'postgres',
      host: process.env['DB_HOST'] || 'localhost',
      database: process.env['DB_NAME'] || 'customers',
      password: process.env['DB_PASSWORD'] || '',
      port: 5432
    })

    await client.connect();
    return client;
  }
}

module.exports = pg

The script assumes that you have a Postgres server running on port 5432 on your local machine and has a database called customers

Let’s check the configuration in config.json

config.json

{
  "name": "Customers",
  "description": "All customer related details sync",
  "spreadsheetUrl": "https://docs.google.com/spreadsheets/d/1d4QXXx8qFRhcajpVcEbwVM8TlEXAvrclWpLGmqvw-Pc/edit#gid=0",
  "spreadsheetId": "1d4QXXx8qFRhcajpVcEbwVM8TlEXAvrclWpLGmqvw-Pc",
  "tables": [
    {
      "tableName": "customer_details",
      "sheetName": "Sheet1"
    }
  ]
}

As discussed before, we have already copied the Spreadsheet URL to the config, Notice, the tables array. You can specify the tables that you would like to import and the corresponding sheet name.

Testing the lambda function

We can invoke the lambda function locally to make sure everything is working fine. Run the following command

$ SLS_DEBUG=* serverless invoke local --function lambda_sheets

The SLS_DEBUG param will print additional debugging information to the console in case something goes wrong. If all is good, you see something like this below where the script prints the number of rows imported.

Now, go check the spreadsheet and you should see all the data from the customer_details table in Sheet1 So, this means every time the lambda function is invoked, it will clear the contents of the sheet and replace it with the data from the table in the Postgres database.

Deploying the Lambda function

The Serverless config shared with you assumes that you are deploying this function to AWS Lambda. Let’s walk-through the steps to deploy the function to production.

Before we deploy, the function let’s discuss additional parameters in serverless.yml file that are relevant. Other parts of the yml file have been skipped for brevity.

serverless.yml

functions:
  lambda_sheets:
    handler: handler.runs
    description: Lambda function to pull data from Postgres and dump to Google Spreadsheet
    timeout: 900
    events:
    - schedule:
        rate: rate(1 hour)
        enabled: true

The timeout param (in milliseconds) is the maximum execution time and the scheduler will ensure this function is called every 1 hour.

To deploy the function to AWS, make sure aws_access_key_id, aws_secret_access_key and region are passed as environment vars to deploy command or configured in ~/.aws/config

$ serverless deploy

The above command will create a new AWS Lambda function and invoke it every 1 hour. Make sure the database config is populated as environment variables as needed from lambda console.

The complete source code is available here for your reference. Thanks for reading, and please feel free to comment and ask me anything! Cheers 👍

Learn more

Ultimate SQL and DataBase Concepts

The Complete Database Design Fundamentals Toolkit

The Complete Database Design & Modeling Beginners Tutorial

Introduction to Oracle Database Backup and Security

Java Database Connection: JDBC and MySQL

Serverless PHP on App Engine and Cloud Firestore with Firevel (serverless Laravel framework)

Serverless PHP on App Engine and Cloud Firestore with Firevel (serverless Laravel framework)

In this article, you'll learn how to build apps using Firevel - a serverless Laravel framework.

In recent decades the PHP community has gone through some major changes. First, we saw billion-dollar platforms like Facebook adopting PHP as a primary language. Then tools like the Laravel framework quickly rose in popularity as it enabled the community to quickly build modern applications. Finally, PHP 7 gave the language a bright future with performance improvements and features like type hinting.

Today, developing applications in PHP has never been simpler, but taking advantage of modern serverless services remained exclusive to languages like JavaScript or Python. Things are about to change yet again for the PHP community as offerings like Google App Engine and Firestore make running completely serverless PHP apps possible.

Benefits of Serverless

Serverless apps are usually simple to set up, cheap (or free) with low traffic, and can scale up to handle high loads extremely quickly. Another benefit is that you also don’t need to worry about server administration as these new services provide fully managed infrastructure. The main reason I started to work on a serverless PHP solution was because of the time I was wasting bootstrapping every new microservice. In addition, once the service was set up it would continue to cost money from machines idling (for example staging, or services used only during office hours).

The Components

Database

After analyzing a wide spectrum of database solutions from different providers and running some benchmark tests I found that Google BigQuery is the best serverless solution for data warehousing and Firestore is the best NoSQL database for production queries.

In both cases, the key benefits are simplicity and performance.

Backend

PHP 7’s release in Google App Engine Standard Environment was the first puzzle piece that allowed the serverless project to start. Google App Engine scales down to zero and it’s eligible for GCP’s free tier. Another bonus, it’s integrated with Google Firestore so you don’t need to set up credentials to access your database.

Firevel

I wanted to make sure that I could use Laravel in this project so I developed several packages (together named Firevel) that makes the framework Google App Engine friendly. Some examples of these changes are disabling writes outside of the tmp directory, allowing a Google proxy, and setting up a Stackdriver log channel.

The packages included with Firevel are:

By default, all exceptions appear in your App Engine Application Errors, and session data is stored inside a collection in Firestore called ‘sessions’.

If you use the Firestore Cache driver you can use all the Laravel Cache features you are used to. The cache is stored in a Firestore collection named cache.

>>> Cache::set('foo', 'bar');
=> true>>> Cache::get('foo');
=> "bar"

Firestore Client can be used for direct Firestore calls and is accessible by Firestore facade. Authentication is handled by App Engine behind the scenes.

Firestore::collection('cities')

    ->document('LA')

    ->set(['name' => 'Los Angeles', 'state' => 'CA']);

Eloquent on FirestoreIf you ever worked with Laravel before you can probably agree that code is very extension friendly. There are plenty of ways to build custom drivers, extensions, plugins etc. However, I don’t think I can say the same about the heart of Laravel — the Eloquent ORM. Eloquent is one of the main reasons Laravel is so successful. It’s a beautiful and simple ActiveRecord implementation designed to work with SQL databases, but it wasn’t designed with NoSQL in mind. So I built Firequent which is the Eloquent replacement for Firevel and it’s currently in beta. I managed to reach general functionality and support for methods like find(), create(), make(), where() and limit(), but it’s not yet ready to work with relationships or any advanced queries.

You can create a Firequent model simply by extending Firevel\Firequent\Model. In the Firestore NoSQL world, you don't need database schemas so instead of creating migrations, you can use mass assignments.

<?phpnamespace App;use Firevel\Firequent\Model;class Post extends Model

{

    /**

     * The attributes that are mass assignable.

     *

     * @var array

     */

    public $fillable = ['name', 'description'];}

You also assign attributes on the fly directly inside the model:

$post = Post::make(['name' => 'Foo', 'description' => 'Bar']);

$post->public = true;

$post->save();

Data is available instantly in your Firestore Dashboard.

Why this is awesome?

The coolest part of Firevel is the simple setup. If you have the gcloud CLI installed on your machine, you can set up an entire app with composer create-project firevel/firevel and then deploy it with a gcloud app deploy command. Using this you can build a scalable web app in mere minutes. It’s also a great tool for building microservices with integrated Firebase Auth and the simple deployment lets you build a new microservice without all the painful infrastructure setup.

Challenges

If your code is limited to a simple CRUD application and your index queries are just filtering by values you might build your app and not even notice any difference from the standard Laravel experience. But with more advanced use cases you can’t simply replace MySQL with NoSQL unless you update your approach to code structure.

Firestore offers us great scalability, flexibility, and simplicity, but everything comes with a cost so keep in mind that:

  • It’s great for heavy reads but not so good for heavy updates. You need to keep that in your mind while working with caching. At the time of writing this article, you should not update an individual document more than once per second.
  • Search queries are limited. The most noticeable limitations are the inability to use “or” operators, and searching that’s limited to “query by full match” (no “row LIKE %”). If you need full-text search I recommend Algolia (compatible with Firevel/Laravel Scout). To learn more about Cloud Firestore querying, I recommend watching this video.
Conclusion

Firevel is a 100% serverless framework. It currently has limitations and requires more production case studies before it becomes mature. But I’ve found it to be a joy to work with and can save you a lot of time.

To me — the possibility of running apps at scale, entirely in PHP, without expensive to manage servers, is like finding a unicorn.

Thanks for reading

If you liked this post, share it with all of your programming buddies!

Follow me on Facebook | Twitter

Learn More about

PHP with Laravel for beginners - Become a Master in Laravel

Projects in Laravel: Learn Laravel Building 10 Projects

Laravel for RESTful: Build Your RESTful API with Laravel

Fullstack Web Development With Laravel and Vue.js

Laravel 5.8 Ajax CRUD tutorial using Datatable JS

Laravel 5.8 Tutorial from Scratch for Beginners

Build RESTful API In Laravel 5.8 Example

Login with Google in Laravel 5.8 App using Socialite Package

Laravel PHP Framework Tutorial - Full Course for Beginners (2019)


Database Management Tutorial - Database Design and Management

Database Management Tutorial - Database Design and Management

Learn how to design and manage database with ERD, database generation and reversal with Visual Paradigm.

Learn how to design and manage database with ERD, database generation and reversal with Visual Paradigm.

Database Design and Management is tailor-made for software development teams who need to develop application or software system that require data persistence. All lectures come with clear voice-over as well as demonstrations to walk you through different tools step by step. We are sure you can master database design and management skills with ease and learn most key features in Visual Paradigm after attending the lectures.

The lectures cover a wide range of contents. Firstly, you will start by an introduction of database design, with description of ERD, entity and column. After gaining a better knowledge about ERD, you can then jump to keys, relationships, database view, triggers and stored procedures. This course also covers database management skills like the generation of database from ERD, database patching and reverse engineering of ERD. Developers may want to study the lecture about ORM for system development with database. Last but not least, the training introduces some best practices in database design and management, as well as the usage of data specification.

Once you have completed this online training, you will know how to design database productively and effectively.

What you'll learn

  • Design database with ERD
  • Maintain database productively and effectively
  • Use Visual Paradigm in database design
  • Use Visual Paradigm in database management

Vue.js and Serverless - Going Serverless with Vue.js

With the rise of serverless architectures, developers have the power to do more with less. Rather than spending time and energy deploying and managing a server, they can now focus on the business logic driving their applications. Serverless functions moreover allow for compartmentalizing your application so your logic is sequestered in a function and can scale automagically without any worry or fuss. What’re more, you pay only for what you use!

In this video, we’ll examine strategies for building applications in VueJS with a serverless mindset. Specifically, we’ll walk through real life use cases of how we can bank on serverless functions to do the work of making API calls and communicating with third party services.

Thanks for reading

If you liked this post, share it with all of your programming buddies!

Follow us on Facebook | Twitter

Further reading about Vue.js and Serverless

Running TensorFlow on AWS Lambda using Serverless

Easily Deploy a Serverless Node App with ZEIT Now

Vue.js Tutorial: Zero to Sixty

VueJS is dead - long live VueJS

React vs Angular vs Vue.js by Example