Building a functional backend with minimal coding

In my previous post, I discussed how service-oriented startups like e-commerce, food-tech, etc. fall into the over-engineering trap, slowing down their go-to-market. It is often deleterious to invest a lot of time and effort building products before achieving the all important Product Market Fit (PMF).

There are several tools available today which can be hacked together to quickly build prototypes which can help validate the idea and refine the product. One of my favourites is Google Apps.

Setting up a database

Think of the most intuitive database you have ever used. I bet you would find it tough to find one simpler to understand and use than a spreadsheet. Every tech entrepreneur worth his salt has at least used MS Excel or Google Sheets. Think of all the things you can achieve easily with an online spreadsheet:

1. Simple data entry, updates and deletes (all CRUD operations)
2. Linking information across multiple data sets through VLOOKUP & HLOOKUP (Table joins)
3. Data validation and conditional formatting to handle erroneous data (DB Constraints)
4. Basic access control by limiting access to different users (ACL)
5. Versioning and data recovery

It takes minutes to set up a online spreadsheet DB backed by the power of the cloud.

Let’s build a mock startup app from scratch

To see how you can build a fully functioning backend application, let’s take an example of a mock startup. Let’s say you are aggregating intra-city courier service providers to offer lighting fast door-to-door delivery. Let’s call it ‘Zap Send’. Here’s how you would go about setting it up (I’m going to take some shortcuts for the sake of brevity)

Zap Send — our mock intra-city courier startup

Step 1: Figure out your core value proposition

While I will not spend time on this topic, I cannot over-emphasize the importance of not skipping this very important step. If you don’t know what is the value of your product/service to your user, go back to the drawing board. For Zap Send, the core value proposition would be ‘Delivery anywhere in the city in 2 hours’

Step 2: Break down the process into steps

The steps for fulfilling one transaction on Zap Send (ZS)would be:

1. Customer places the order
2. ZS reviews and accepts the order (there could be some constraints around pickup and delivery areas)
3. ZS forward the order to one of its courier partners (CP)
4. CP picks up the order
5. CP delivers the order
6. Order fulfilled

NOTE: At any point of time, the order may get cancelled for several predictable and unpredictable reasons.

These steps are the building blocks for your workflow. When you are first designing the system, try not to overthink a lot of edge cases and build contingencies. It’s better to let internal and external factors stress and break the system and quickly build solutions around specific high-impact problems.

Step 3: Design your state engine

Once you have a workflow in place, it’s pretty simple to design a state engine. You simply have to assign states to your transaction at various stages of the workflow for tracking. Generally, I prefer to assign statuses which indicate the action to be performed. So, the statuses would be:

1. Confirmation Pending
2. Pickup Pending
3. Delivery Pending
4. Fulfilled
5. Cancelled

Step 4: Create a form to receive orders

This is not a necessary step. Say you are receiving all your orders over phone, you may choose to directly make an entry into a spreadsheet instead of creating a form. However, I prefer using a form for creating transactions even if it’s not customer facing. The main reasons are security (you may not want to allow lot of people access to your full spreadsheet) and integrity (other transaction data is unlikely to be affected while logging a new one).

I have created a mock form for logging orders into the system. You can view the sample Zap Send Order Creation Form for reference. This form can be easily embedded into your website or any other potential source of order.

Step 5: Set up your notification engine

Now that orders can be created, the next step would be to implement a simple notification engine so different actions can trigger communications to relevant users.

The first notification would be triggered every time someone places an order. This can be achieved directly inside Google Forms designer.

Enabling email notifications for new order creation

This will trigger a generic alert email to the form creator every time the form is submitted. You can also get additional functionality like getting all the form data in email, notification rules with a nifty plugin like this. Once you have set up the form, you will have a spreadsheet automatically set up in the backend.

The next step would be sending notifications to the courier partner. You can use a mail merge plugin like this one from ctrlq.org. I particularly like this plugin because of its simplicity in creating and managing multiple templates.

Step 6: Implement state engine

A state engine is pretty straightforward. Add a column called Status to the form responses sheet. This column will maintain the current status of the order.

Basic state engine using dropdown for statuses

As your process evolves, you can quickly add or modify these statuses to ensure the right tracking of your orders.

Step 7: Maintaining an audit trace (Optional)

This step, though optional, can prove to be very useful, especially for customer support. To create an audit trace it is useful for first assign a unique identifier to each order. You can do this by assigning serial numbers to your order or you can auto-assign a random id. I generally use the timestamp field to create this random id. You can use a simple formula below:

=SUBSTITUTE(RIGHT(A2,8),”:”,””)

NOTE: The above formula extracts the 8 characters from the right and substitutes “:” in the timestamp with a null character.

Once you have this, you can create a sheet to maintain logs. You can name it ‘Order Logs’. After the sheet is ready, go to the Tools option in the Sheets menu and click on Script Editor. Use a simple piece of code below to track changes in order status and create a log in the Order Logs sheet

function onEdit(eventInfo) { //This event gets called any time a field gets edited

 //Column number of the status column (Will vary based on the form design)
 var statusColumnNumber = 10;
 var orderIdColumnNumber = 9;
 
 //Create the log only if status has changed. You can create different types of logs based on other changes as well
 if (eventInfo.range.getColumn() == statusColumnNumber){ 
 //Get order id of the updated
 var formResponsesSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(“Form responses 1”);
 var row = eventInfo.range.getRow();
 var range = formResponsesSheet.getRange(row, 1, 1, statusColumnNumber).getValues();
 var orderId = range[0][orderIdColumnNumber-1];
 createAuditLog(orderId,eventInfo.oldValue,eventInfo.value);
 }
}
function createAuditLog(orderId, oldStatus, newStatus){
 //Choose the sheet in which logs are to be entered
 var orderLogsSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(“Order Logs”);
 var lastRow = orderLogsSheet.getLastRow();
 //Create a new row for the log
 orderLogsSheet.insertRowsAfter(lastRow, 1);
 
 //Capture the time of edit
 var timestamp = new Date();
 
 //Create the log
 var log = [[orderId,oldStatus,newStatus,timestamp]];
 
 orderLogsSheet.getRange(lastRow + 1, 1, 1, 4).setValues(log);
}

You will get logs in the Order Logs sheet like this

Sample logs for tracking history of the order

You can add more details to these logs if required. This is your fully functional backend complete with an audit trace of your transactions. It will literally take a day to set up and can help you launch very quickly to validate your idea.

Hope you liked post. I would love to get your feedback and suggestions on future posts. In the next post, I will discuss extending the notification functionality with SMS integration. This will enable you to send automated SMSes from Google Sheets.