How to Prototype an API Quickly With Firebase Through Excel Data Import


Iterative prototypes can turn your concepts into working ideas faster than trying to get everything right on paper, only to find that it doesn’t quite translate as well onto the screen for whatever reason.

As a developer, your task is to turn ideas into reality — not fiddle with Photoshop all day.

Whether it’s for a client, your boss, project manager, to test an idea, or prototyping can be a good way to get your app off the ground.

For some of us, the natural flow is to start off with the front end and deal with the data later. But your app can’t really do much without working data.

You need to be able to mock data models and generate some sort of working API for it. For all we know, the shape of the data can change and you need a prototype-friendly back end that’s quick to boot up and efficient towards pivots.

How to Fake Data With Real Data

It turns out you can use Google Sheets as part of faux back end.

Yes. That’s right. Good ole’ Google Sheets.

And you can do it with the help of Firebase. Here’s the quick recipe on how to execute this magic.

The ingredients

  • x1 Google Sheet with some data.
  • x1 Google account for Firebase free tier.

The method

Step 1. Set up for Firebase project

If you’re unfamiliar with Firebase, there’s a free tier you can use at no cost. Google won’t even ask you for your credit card number so you can rest assured.

Step 2. Create your database

Look at your navigation panel. Track down Develop. Click on Database. Now click on the Create database button.

It’ll talk about permissions and all that stuff. Make sure you change your read and write permissions to true. You want to be able to do things with it or else it’ll be just a read-only kind of database.

Smash the publish button.

Once everything is all good and dandy, you’ll be given a database URL. Take note of it. You’re going to need it for later.

It’s good to note that these security rules are still important and you shouldn’t leave them open without additional access logic.

Keeping read and write permissions as completely open just makes life easier in the dev phase. Not so much (or safe) for production spaces.

Step 3. Make your spreadsheet

You need the following minimum requirements to make things work:

  • An ID key.
  • Some data.

The sheet represents how your data will be translated into JSON for the API to send through to your app. The first row acts as your key. The subsequent rows are your line values.

Here’s a gist of what it should look like:

The beauty of this is that you just have to deal with what you want your data to look like. Think of it as the final queried result from your SQL, MongoDB, DynamoDB, or whatever DB you’re using.

Nested data trick

Flat data can get pretty one-dimensional. Nesting data is a matter of attaching __ (←that’s a double underscore by the way) and the nested key name.

For example, this:

Will generate something like this:

      "timeZone":"gmt+13 2020",
         "coordinates":"90 glactic latitude",
         "name":"Milky Way"

(The example contains the first row only to get a general idea. In reality, it’ll give back all the rows.)

Step 4. Edit some scripts

So, you’ve got your data. Now you want to magically turn it into an API. Still inside Google Sheets, hit the menu and look for Tools. Select <> Script editor.

This will take you to a code editor console with the file open. Replace everything in the editor with the part in this GitGub gist.

At the top of the script are two of the following:


You can find this in the URL of your actual spreadsheet. The structure goes something like this:


Remember that URL I told you earlier will come in handy later? Now is later.

Give your project a name and it should autosave the script.

Look over to your menu and hit View. It’ll give you a bunch of options. Select Show manifest file.

Replace the contents with the stuff inside appscript.json with this appsscript.json. Ctrl+S it to save.

Now all that’s left is to run the script. In the menu, hit the following: Run -> Run function -> initialize.

There will be a few prompts and authorization screens pop up. Review the permissions, accept the popup, and viola! Your Firebase database now has the data from your Google Sheets.

Since Firebase comes with pre-configured REST APIs, here‘s the official documentation for access points and how to use them. To keep this post short and sweet, I’m not going to go over them here.

If you want to update your data quickly and in bulk, you can do so via the spreadsheet and run the script again.

And that’s it.

Front-End Framework Spotlight: Angular

There are more than a dozen prototyping tools that you can use. However, as developers, we’re in the business of creating code — so why do something twice in two different spaces when you can do it once?

Prototyping with Angular isn’t always the first thing that comes to mind but it is possible and a highly efficient way to do it too.

This is probably going to sound like a plug because I’m an Angular dev by experience — but just to be clear, I’m not sponsored by them in any way.

Other front ends will get their time to shine too in the future — just not in this post.

So, why Angular?

Reusable code

If your system is already in Angular, creating a stand-alone Angular app prototype can just be a matter of plugging in the relevant code.

This can come in handy because it keeps your prototyped code separated from the main repo until the feature is validated and user-test accepted.

If it’s a brand new Angular app, the framework offers a range of visual patterns that you can use to implement the required user experience. The CLI also makes templating code easier and faster with standardized patterns.

Good component library

Angular Material is probably one of the best component libraries for Angular. The visual design is based on Google Material design and implements the principles as UI components so you don’t have to do it.

When it comes to code, there are three parts to the process:

  1. Creating services to connect, fetch, and send data.
  2. Doing something with that data and figuring out how to correctly display it on the front end.
  3. Making the UI pretty.

The last part can be time-consuming and sometimes frustrating, especially when you have to manually deal with cross-device designs. Angular Material sorts this out for you and makes your prototyping process faster.

Micro front end capabilities

Angular Elements is something that’s often hidden in the recesses of more advanced tutorials. No one really talks about it in the beginning circles.

What Angular Elements do is let you create stand-alone components that you can drop into any existing project. This means that if only certain parts of your prototype are required, you only need to export that particular feature.

This allows for micro front ends — an architectural method that’s often used to deal with legacy code that may be using multiple frameworks and libraries in different versions.

Or maybe the boss just wants a set of new features but doesn’t have time for you to recode three years’ worth of dev work that may be outdated due to updates and incompatibility.

How to Host Your Prototype for Live Environment Testing

So, you’ve got a working app on your localhost and you want to test it in a live environment.

Firebase has free hosting and comes with a free subdomain. You can set up your own domain later. The point is to get this app of yours up and running asap.

Build your Angular app

There isn’t much to building your Angular app.

Just run ng build --prod in the CLI and you’ll get a dist folder.


For this part, you’ll need firebase-tools.

You can get this via the CLI, using the following command:

npm install -g firebase-tools

Once you’ve got it, you’ll need to login:

firebase login

When you’re in, initialize your Firebase project on your Angular project’s root folder. Use the following command:

firebase init

It’ll ask you a few questions to configure your setup. Here are the questions and answers you should use:

  • Firebase CLI features → Hosting.
  • Database rules file → Just use the default.
  • Public directory → dist.
  • Configure as single-page app → yes.
  • Overwrite index.html → no.

Now for the actual deployment part, use the following command:

firebase deploy

And we’re done!

You can open your newly-deployed site straight from the command line using the following:

firebase open

And select Hosting: Deployed Site.

To see the deployment in the Firebase console, go to Hosting and you’ll get a screen that gives you additional information.

Final Words

Prototyping in Angular isn’t that hard. You can also use the deployment method in other libraries and frameworks like React and Vue, except perhaps the build part.

But the actual deployment process is fairly standard and simple.

But when and where would you use this?

It helps when you’re working in cross-functional teams where, lets say, commercial gives you an excel spreadsheet (something they understand) and you need to prototype something quickly. What this essentially does is import data into your database in the format you need it to without needing to write the scripts. If you already have the spreadsheet, then it cuts down about 3/4 of this tutorial in a way and then you just have to deal with your front end.