Architecture using AWS Lambda with Google Spreadsheets

AWS Lambda Function: a Java example with Google Sheets API

In the present article we will show how to use AWS Lambda Functions written in Java and glued with a Google Spreadsheet file thanks to Google Sheets API (v4). At the bottom you can find the GitHub repository with the code.

Why AWS Lambda?

AWS Lambda is a cheap option to have in your backend. According to Amazon, during your free-tier year you could hit 1 million times your function and it would be for free; following hits as well as hits after the first year would cost $0.0000002 per request. A Lambda function is easy to test and fast to build.

What is the Cold Start in AWS Lambda?

AWS Lambda is a great service as long as you are aware of Cold Starts. The main benefit of Lambda functions is that you will pay much lesser than having a dedicated EC2 instance, but the main disadvantage are Cold Starts (link, and another link).

A Cold Start happens in AWS Lambda when the function does not receive, for an extended period of time, any request, so that AWS has to initialize it taking a few seconds. This, nowadays, is an app killer… unless like us, you do not depend on the response.

This project is highly based on a real project, so as requirements we knew in advance that the user would not care nor know if the data was sent correctly to backend.

Why Google Sheets API v4?

Google Sheets lets people create, edit and share spreadsheets. Easy to use, Microsoft’s Excel look-like, was a firm candidate for our ‘database’. We wanted to minimize as much as possible costs for such small needs.

We were confident the size of users would be under 100 per day in a best case scenario, meaning that nearly one request per user, so there would not be high risks of concurrents calls to the database spreadsheet. Besides, there is no daily limit of requests to Google Sheets API.

Use case: storing customers’ origins to expand the business

A small company, weaver and distributor of wedding dresses, believed in the power of data. Thus, as a core strategy, the company wanted to show their catalogue on an iPad, having a lemonade, being fresh and sitting on a comfortable sofa. The requisit? To fill in the iPad the city of origin and wedding date.

Having the city of origin and the wedding date, they could wisely decide where to expand. The project was fun, and we were glad to so easily help a small company, so why not?

Our Lambda: the Java code explained

Our AWS Lambda functionWrite was called CustomerDataToSheet. The function is small, but the main logic holds here:

   public Customer handleRequest(Customer customer, Context context) throws IOException, GeneralSecurityException {
        ValueRange customerInCells = transformInputCustomerIntoSheetCells(customer);
        logCustomerInfoInAWSLambdaContext(context, customer);
        return customer;

The Lambda function is called by our AWS API Gateway (to be discussed on other article) with an input defined by us, a Customer object, and a Context that belongs to the Lambda’s execution context.

Once the function is running, the first thing it does it is to initializeSheetService or, in other words, to instantiate a class that is connected to Google Sheets API and therefore our database spreadsheet. 

For doing requests to Google Sheets API you need:

(1) to have your Spreadsheet shared to the web, and 

(2) have a Service Account as a Google Developer so your request uses these credentials.

Otherwise, using OAuth 2.0 you will need to manually give permissions to your program, so in the case of Lambda it will just not work.

Later on, it transforms the input Customer into a range of values (cells in a row). So if our customer has two fields, namely weddingDate and cityOfOrigin, it will be transform into a list of strings with those values stringfied, so they can be inserted into the spreadsheet.

Finally, we send the value to Google Sheet API, do some basic login and return the customer so we know that everything seemed to work fine.

The code of the Lambda function can be found in the repository: (to be updated).

AWS Lambda and Google Sheets API: a nice try

We recommend this combination for small business where money plays a key role, as well as there is no need for delivering content to users in a fast manner or, as in this case, at all.

Share this post

Share on facebook
Share on google
Share on twitter
Share on linkedin
Share on print
Share on email


Oh, no. What have we done?