Using Google Sheets as main storage for service

From time to time there is need to create some nano services, that will have really small amount of data and/or active users.

For such services one of best ever solutions will be simple things like sqlite or even plain json file.

But what if we need to give a way to modify data for internal users, should we build an admin pages for that?

Here is where Google Sheets may become helpful.

Lets pretend we have an two sheets like this:

Sample sheet

Benefits are:

users are already familiar with Google Sheets
we may add some data protection rules
we may protect sheets and ranges from editing
there is an rest api

To keep things simple I am going to use service account credentials and nodejs samples taken from here

Note: there is nothing fancy, just want to have something ready to be copy pasted

import express from 'express'
import { GoogleAuth } from 'google-auth-library'
import { google } from 'googleapis'
import { readFileSync } from 'fs'

// Sample sheet: https://docs.google.com/spreadsheets/d/19sdWNuvlixPtEW3w5KRCosVm0iltpdbTm-o9FET1q-s
const spreadsheetId = '19sdWNuvlixPtEW3w5KRCosVm0iltpdbTm-o9FET1q-s'
// Sample table for products list
const range = 'products!A:C'
// Unformatted will return raw value like numeric 600, formatted will return formatted string values like "$600" instead
const valueRenderOption = 'UNFORMATTED_VALUE'

// Instance of GoogleSheets API client
const { client_email, private_key } = JSON.parse(readFileSync('/Users/mac/Library/Mobile Documents/com~apple~CloudDocs/Certs/alexandrm.json', 'utf-8'))
const auth = new GoogleAuth({ scopes: ['https://www.googleapis.com/auth/spreadsheets'], credentials: { client_email, private_key } })
const service = google.sheets({ version: 'v4', auth })

// Usual express stuff
const app = express()
app.use(express.json())

/**
 * Retrieve products list
 *
 * Request:
 * curl http://localhost:3000/products
 *
 * Response:
 * [
 *   ["sku","name","price"],
 *   ["iphone15","iPhone 15",600],
 *   ["iphone15pro","iPhone 15 Pro",900],
 *   ["macbook-pro-14","MacBook Pro 14",2700]
 * ]
 */
app.get('/products', async (_, res) => {
  const result = await service.spreadsheets.values.get({ spreadsheetId, range, valueRenderOption })
  return res.json(result.data.values)
})

/**
 * Retrieve product by sku
 *
 * Remember - we are not talking here about bazillion records and/or performance
 * this approach should be enough and can be always cached.
 * Also think of this - if there is really quadrillion of products - how it will be handled by GoogleSheet?
 *
 * Request:
 * curl http://localhost:3000/products/iphone15
 *
 * Response:
 * ["iphone15","iPhone 15",600]
 */
app.get('/products/:sku', async (req, res) => {
  const result = await service.spreadsheets.values.get({
    spreadsheetId,
    range,
    valueRenderOption: 'UNFORMATTED_VALUE',
  })
  return res.json(result.data.values.find((row) => row[0] === req.params.sku))
})

/**
 * Create product
 *
 * Request:
 * curl -X POST http://localhost:3000/products -H "Content-Type: application/json" -d '{"sku":"watch4","name":"Watch 4","price":400}'
 *
 * Response:
 * {"spreadsheetId":"19sdWNuvlixPtEW3w5KRCosVm0iltpdbTm-o9FET1q-s","tableRange":"products!A1:C4","updates":{"spreadsheetId":"19sdWNuvlixPtEW3w5KRCosVm0iltpdbTm-o9FET1q-s","updatedRange":"products!A5:C5","updatedRows":1,"updatedColumns":3,"updatedCells":3}}
 */
app.post('/products', async (req, res) => {
  // Note: pretend we did all validation here
  const { sku, name, price } = req.body
  const result = await service.spreadsheets.values.append({
    spreadsheetId,
    range,
    valueInputOption: 'USER_ENTERED',
    requestBody: { values: [[sku, name, price]] },
  })
  // Technically we should return 201 here, but for curiosity lets see whats returned by GoogleSheets API
  return res.json(result.data)
})

/**
 * Update product
 *
 * Request:
 * curl -X PUT http://localhost:3000/products/iphone15 -H "Content-Type: application/json" -d '{"sku":"iphone15","name":"iPhone 15","price":700}'
 *
 * Response:
 * {"spreadsheetId":"19sdWNuvlixPtEW3w5KRCosVm0iltpdbTm-o9FET1q-s","updatedRange":"products!A2:C2","updatedRows":1,"updatedColumns":3,"updatedCells":3}
 */
app.put('/products/:sku', async (req, res) => {
  // Note: pretend we did all validation here
  const { sku, name, price } = req.body

  // Lets receive products first, to find index of product we want to update
  // once again - keep in mind we are not talking about performance here
  const products = await service.spreadsheets.values.get({
    spreadsheetId,
    range,
    valueRenderOption: 'UNFORMATTED_VALUE',
  })
  const index = products.data.values.findIndex((row) => row[0] === req.params.sku)

  const result = await service.spreadsheets.values.update({
    spreadsheetId,
    range: 'products!A' + (index + 1) + ':C' + (index + 1),
    valueInputOption: 'USER_ENTERED',
    requestBody: { values: [[sku, name, price]] },
  })
  return res.json(result.data)
})

/**
 * Delete product
 *
 * Request:
 * curl -X DELETE http://localhost:3000/products/iphone15
 *
 * Response:
 * {"spreadsheetId":"19sdWNuvlixPtEW3w5KRCosVm0iltpdbTm-o9FET1q-s","replies":[{}]}
 */
app.delete('/products/:sku', async (req, res) => {
  const products = await service.spreadsheets.values.get({
    spreadsheetId,
    range,
    valueRenderOption: 'UNFORMATTED_VALUE',
  })
  const index = products.data.values.findIndex((row) => row[0] === req.params.sku)

  const result = await service.spreadsheets.batchUpdate({
    spreadsheetId,
    requestBody: {
      requests: [
        {
          deleteDimension: {
            range: {
              sheetId: 0,
              dimension: 'ROWS',
              startIndex: index,
              endIndex: index + 1,
            },
          },
        },
      ],
    },
  })
  return res.json(result.data)
})

app.listen(3000)

Notes to keep in mind:

it is just an demo and is not about performance
for those complaining - think of how Google will handle sheet with quadrillion of records
by intent things keeped simple and copy pasted

Links:

Just in case here is crafted PHP sample

<?php
require_once 'vendor/autoload.php';

$client = new Google\Client();
// $client->setAuthConfig('/Users/mac/Library/Mobile Documents/com~apple~CloudDocs/Certs/alexandrm.json');
putenv('GOOGLE_APPLICATION_CREDENTIALS=/Users/mac/Library/Mobile Documents/com~apple~CloudDocs/Certs/alexandrm.json');
$client->useApplicationDefaultCredentials();
$client->addScope('https://www.googleapis.com/auth/spreadsheets');

$service = new Google\Service\Sheets($client);
$result = $service->spreadsheets_values->get('19sdWNuvlixPtEW3w5KRCosVm0iltpdbTm-o9FET1q-s', 'products!A:C', ['valueRenderOption'=>'UNFORMATTED_VALUE']);

echo json_encode($result->getValues());