[Tech] How I build a system for Friday Lunch — Lunchie

Build an iOS app powered by Firestore and Google Spreadsheet


If you are interested in why I start this project, I explained it here.



I realised that my project might not be the best example of using a NoSQL database because of its small scale but since Firestore is quite new to me, I decided to give it a try and learn something new on the way.

What I have learned

It’s schemaless and horizontally scalable 😎: Schemaless means I can always change the rule of the database by adding/removing fields and it will not affect the exisiting objects. Each object under the same collection can have slightly different fields. The impact of this is that developer needs to “code defensively”. The horizontal scalability enables the database to scale easily within seconds on cloud without hassle.

Collection and Document concepts: Firestore is slightly different from Firebase Realtime database. Collection is a collection of documents and Document contains fields (String, Maps(Key & values), Boolean ..etc) and subcollections. Query is shallow by default, meaning you won’t get the subcollections.
Note: You have delete subcollections layer by layer too(meh).

// Query path
// but I prefer to use this shortcut format

It’s fast in data query but you might have duplicate data 😱: In NoSQL database, to avoid calling multiple tables, you are advised to have snippet in your current table but you have to sync to avoid outdated data. Why are we doing this? It’s always believe the demand of READ > WRITE, it’s part of the trade off.

It has snapshot listener to update mobile at realtime: A callback you provide creates a document snapshot immediately with the current contents of the single document. Then, each time the contents change, another call updates the document snapshot.

* Attaches a listener for QuerySnapshot events.
* @param listener The listener to attach.
* @return A FIRListenerRegistration that can be used to remove this listener.
// example use:
document.reference.collection("employees").addSnapshotListener{ (snapshot, error) in
// whenever there's changes on Firestore on Employees,
// it notifies

How to install

The setup was pretty straightforward to me and their explanation videos on Youtube were amazing.

Version 1


Simple: I just need to create a date and it contains the food options and employees who made the orders.

Updated data: There’s no need to sync the data between different models and data in the fields are the source of truth.


Extensibility: Hard to extend functionality of Employee. If I want to analyse the behaviour of the employee(predict his/her next food choices), I will need to query many times on different dates to get all the information needed. Same goes with Food as well.

Version 2 (Pending)


Employees collection has all employees options history. I wanted to collect employees data in a long run so that I can analyse their food options. For food collection, it stores food properties (isVege, isHalal, isVegan). Order collection is similar to our first version but it will only store the snippet of food and employee data as this is to enable the mobile app can retrieve all the data at one go.

Cloud Function: I have to use to Cloud Function to update food and employee data whenever user changes something in Food and Employees collection. As usual, Firebase provides a fantastic tutorials on Cloud Function to guide me on it. This is my first time playing with typescript hence I’m still figuring things out. (This will be in my part 2.)

// When there's a new entry in Employees collection, I will sync it up to my Lunch/Orders collectionexport const onFoodOrderCreate = functions.database
.onCreate(async (snapshot, context) => {
<Sync Order collection here>
// same goes for onUpdate

Google App Script

Firestore Integration

In the Google online script editor, select the Resources menu item and choose Libraries.... In the Add a library input box, enter 1VUSl4b1r1eoNcRWotZM3e87ygkxvXltOgyDZhixqncz9lQ3MjfT1iKFw and click Add. Choose the most recent version.
(Thanks Grahamearley, nice profile picture btw)

Creating a service account

The details of setting up service account can be seen in Grahamearley’s library README or you can also do it via command line

function Firestore() {  
var key = "-----BEGIN PRIVATE KEY-----\n<your service account private key>\n-----END PRIVATE KEY-----\n";

var email = "<your project>.iam.gserviceaccount.com";
var projectID = "<project ID in Firebase>";
var firestore = FirestoreApp.getFirestore(email, key, projectID);
return firestore;

Some useful functions

Google App Script is quite expressive and I just need to go through their documentation whenever I need to do certain functionalities for Spreadsheet implementation.

var spreadsheet = SpreadsheetApp.getActive();
var menuItems = [
{name: '1. Create New Sheet', functionName: 'showPrompt'},
{name: '2. Update current Lunch', functionName: 'updateLunch'},
{name: 'Reset Sheet', functionName: 'resetSheet'}
spreadsheet.addMenu('Lunchie', menuItems);
// GET SHEET WITH NAME <sheetName>
var newSheet = spreadsheet.getSheetByName(<sheetName>);
var headers = ['Food', 'Who ordered it']; newSheet.getRange('A1:B1').setValues([headers])

Deletion of a collection does not include its subcollection: You have to delete its subcollections before you delete the collection. If you have larger collections, you may want to delete the documents in smaller batches to avoid out-of-memory errors.

// A custom function that delete EMPLOYEES and FOOD collections's documentsfunction deleteLunch(){  
var sheet = SpreadsheetApp.getActiveSheet();
var sheetName = sheet.getName();
var foodPath = BaseURL() + sheetName + "/food/";
var employeePath = BaseURL() + sheetName + "/employees/";

// Let's clear employees docs
var employeeDocs = Firestore().getDocuments(employeePath);
for (var i = 0; i < employeeDocs.length; i++) {
var path = employeeDocs[i].fields["path"];
// Let's clear food docs
var foodDocs = Firestore().getDocuments(foodPath);
for (var i = 0; i < foodDocs.length; i++) {
var path = foodDocs[i].fields["path"];

How to debug

To view your logs, go to your script editor and selectView > Logs(⌘+Enter).

You can run specific function instantly to debug

If you do not wish to refresh your spreadsheet to trigger your newly edited function, you can trigger it from script editor by selecting the function at the top bar to run.


If you want to see how your app triggers the function in realtime, Select View > Stackdriver logs > Apps Script Dashboard to your project.

Version 1 Demo

Version 1: (Left) Google spreadsheet which will be used by our office manager. (Right) Spreadsheet creates Fields in Firestore which will reflect on iOS app.


Limited changes from Spreadsheet: As you can see from the high-level architecture above, the spreadsheet notifies Firestore on changes but not the other way. For example, my office manager cannot edit food name(food name is being used as ID) and can only rely for engineers to go on Firebase console.

Amy Cheong is a software engineer who is also interested in keeping herself busy with personal projects. Here is her Twitter, LinkedIn and personal website.

Current: Product Manager at Workmate • Always Software Engineer.