Mobile app: Google Sheet as data BACKUP (not database)

This is my 6th write up on dev.to site. Simple solution for data backup in Google Sheet.

Problem to solve

I have developed many mobile apps for my clients. The typical usage of an app is by one person or a few people (family, small team) and all data is created on mobile phone. It means app has form and user fills data on phone. Data is stored in localstorage on phone as an array of JSON objects.

My apps are typically running on phones without active data connection (really: no internet) and there is NOT necessary for data to be realtime.

Since all data is stored on phone's internal memory (localstorage), it is not save. Problems may arise and data demage or data loss may occur.

That's why I decided to find simple solution for data backup. As the title of this article says, I use Google Sheet with Apps Script.

The solution

Important note: data created on app is not extensive; say a few kB (< 20 kB).

As my goal is to backup data - and not create database - I use very simple solution with Google Sheet included Apps Script. I create new sheet for app, fill titles of four columns A, B, C, D as timestamp, date and time, note and data backup. The main idea is simple: data is stored as string represents array of data in one cell inside Google Sheet. So, there is no structure, all data from one backup process is stored inside one cell (column D). It means implementation of backup process is simple and general.

User first fills note for the backup process - it is useful later when data is restored from backup - and sends data to Google Sheet (mobile app sends secret key together with data to backup - it is protection/auth and name of sheet inside Google Sheet doc). Then doPost function is called on Google Apps Script (implemented inside Google Sheet):

function doPost(request){

  var doc = SpreadsheetApp.getActive();

  var time = new Date();
  var timestamp = time.getTime();
  var data = JSON.parse(request.postData.contents);
  var app = data.app;
  var note = data.note;
  var backup = JSON.stringify(data.backup);

  var sheet = doc.getSheetByName(app);

  var output = null;

  if (sheet && app.toLowerCase() == "...SECRET KEY HERE..."){
    output = saveData(sheet, timestamp, time, note, backup);
  };

  if (!sheet) {
    output = 100;
  };

  var objReturn = {
    "timestamp": timestamp,
    "code": output,
  };

  return ContentService.createTextOutput(JSON.stringify(objReturn)).setMimeType(ContentService.MimeType.JSON);

};

Function returns timestamp (not necessary) and status/error code. The data backup is processed by function saveData:

function saveData(sheet, timestamp, time, note, backup){

  var output;

  try {
    var lock = LockService.getPublicLock();

    lock.waitLock(30000);

    sheet.appendRow([timestamp, time, note, backup]);

    lock.releaseLock();

    output = 0;

  } catch (error) {
    output = 101;
  }

  return output;

};

Function tries to append new row to sheet and returns status code 0 (success) or 101 (error code). OK, I know there are to columns with almost same information (columns A, B - timestamp, data and time). Thats for quick overview of me. It is better to see 16. 7. 2023 19:15 (europian format) than timestamp 1689527719019 :-)

As the limit of one cell in Google Sheet is 50 000 chars, there is (in my case!) enough space to store all data in one cell. And it is all. Very simple solution for data backup.

Hope this help you...