How to turn Google Sheets into Your Owner Database with AppSheet?

Photo by Caspar Camille Rubin on Unsplash

Maybe you know that Google Sheets can be used as a lightweight database. With GoogleSheets you can store data in a structured way just like most database management systems and access your data online.
Although Google Sheets has limitations such as the number of cells you can use (up to 10 million cells) and some other restrictions, it’s still a good option that you can get for free.

Google sheets are not the complete solution for the database management of your business. But, it works wonders for small businesses and projects to handle their business financial operations efficiently.
There are some use cases you can use Google Sheets as a Database: A contact form on your landing page, a collection of chatbot questions/answers, …

Generally, you have to enable Google Sheets API to turn Google Sheets into a database. Then you can use googleapis library to access Google Sheets. In this way, the simple action CRUD(Create, Read, Update, Delete) becomes a little complex because you have to work with cell notation syntax.
In this story, I will show you “How to turn Google Sheets into Your Owner Database with AppSheet?” Shall we see how simple it is?

# Table of content

1. Define the table
2. Turn Google Sheets into a database with AppSheet
  - Create AppSheet application
  - Update table column defination
  - Enable AppSheet Api
  - Access the table by using the AppSheet API
3. Bonus: `appsheet-client-api` - AppSheet Client API library for Node.js

1. Define the table

We have to create a Google Sheets spreadsheet that includes your table. You can take a look at my other story to see how to define a good table.

In this story, I will use Contacts table to demonstrate.

Contacts table

• Database name: MediumExample
• Table name: Contacts
• Table columns: ID, Name, Email, Message, CreatedAt

2. Turn Google Sheets into a database with AppSheet

- Create an AppSheet application

I think creating an AppSheet application from Google sheet menu is the easiest way.

Create an app as an extension

So, let’s custom your application.

- Update table column definition

In the AppSheet application console, maybe the table is already added, if not, just add your table from Google sheets source.

Add new table from google sheet source

Next, I will update Contacts table columns definition.

Table column definitions

• ID is a Text and its value is generated by UNIQUEID()
• Name is a Text
• Email is an Email
• Message is a LongText
• CreatedAt is DateTime and its value is generated by NOW()

Remember to click the Save button after you change something.

- Enable AppSheet Api

In this step, we want to get App Id and Application Access Keys .

By default, AppSheet api is disabled. Just enable it.

Enable Appsheets API

That’s all, now you can access your table by calling APIs.

- Access the table by using the AppSheet API

You can use the API to make the following changes to your app.

• Add table records
• Delete table records
• Read a table record
• Update table records

Reference document: Google Support Center, How to invoke the API?

For example:

To add a new contact (you can add multiple records at once).

$ curl --location --request POST 'https://api.appsheet.com/api/v2/apps/your_app_id/tables/Contacts/Action' \
--header 'ApplicationAccessKey: your_app_key' \
--header 'Content-Type: application/json' \
--data-raw '{
    "Action": "Add",
    "Properties": {
        "Locale": "ja-JP"
    },
    "Rows": [
        {
            "name": "foo",
            "email": "foo@example.com",
            "message": "Message from foo"
        }
    ]
}'

Then you can receive a row in the spreadsheet

A new contact

And following api call will return all messages from foo .

$ curl --location --request POST 'https://api.appsheet.com/api/v2/apps/your_app_id/tables/Contacts/Action' \
--header 'ApplicationAccessKey: your_app_key' \
--header 'Content-Type: application/json' \
--data-raw '{
    "Action": "Find",
    "Properties": {
        "Locale": "ja-JP",
        "Selector": "Filter(Contacts, [Name]=foo)"
    },
    "Rows": [
    ]
}'

# [
#   {"_RowNumber":"2","ID":"de109934","Name":"foo","Email":"foo@example.com","Message":"Message from foo","CreatedAt":"11/16/2022 10:13:46"},
#   {"_RowNumber":"3","ID":"b1de2866","Name":"foo","Email":"foo@example.com","Message":"Message from foo 2","CreatedAt":"11/16/2022 10:23:48"}
# ]

If you are working with Node.js, I already created a library to work with AppSheet API

3. appsheet-client-api— AppSheet Client API library for Node.js

Features

• Simplified AppSheet API usage
• Easy to build query string by Condition Expression and Query Expression

You can easily add a new record with this syntax.

const accounts: IAccount[] = [
  { email: 'foo@example.com', name: 'Foo' },
];

const createdRows = await client.add<IAccount>('Accounts', accounts);
//    ^? const createdRows: IAccount[]

For more details: appsheet-client-api