How to Convert Google Sheets to a Lightweight and Self-running Database with Google Apps Script (1)

It would be a huge waste if you treat Google Sheets as an online Excel. After all, it is naturally compatible with code means that it is perfect suitable for self-running project and database.

If you don’t need to deal with giant data, Google Sheets is a wise choice for you the set up the database.

In this blog, I will use an Investor Management Database as an example to tell you what Google Sheets can do.

Imaging you are a small investment company, and you manage nearly 100 investors. They can invest money at anytime and the dividend is quarterly. After they receive the dividend, they can reinvest or cash withdraw. If they reinvest, then it becomes compound interest.

Of course, you can use Excel to do all these work. But it is tedious and easy get wrong. Automatically process will be a wise choice. Thanks to Google Apps Script, you don’t need to set up environment or download some software. All can be done at Google Sheets.

Logic Flow

The first step is to build a logic flow of the automatically process. Long story short, there are two scenarios:

1, at the end of each quarter, calculate the dividend and balance, then begin the dividend for the next quarter.

2, in everyday operation, after input the new investment, calculate the new dividend.

Database normalization

After analysis the case, we create 5 sheets (as ‘table’ in RelationalDatabase): 01_Investors, 02_Accounts, 03_Transactions, 04_Dividends, 05_Balances.

Create triggers

One key features of Google Sheets is “triggers” which plays critical role in process automation.

In our case, we will create 2 triggers:

1, if there is a new transaction, automatically update the data to sheet Dividend and calculate the dividend.

2, at the end of each quarter, automatically calculate the dividend for each account and begin record investment for new period.

There are two ways to create triggers: setting and code.

Setting

1. Go to Apps Script first, and click Triggers

2. Click Add Trigger button:

3. Select corresponding Function and ‘On change’:

Coding

Or you can using code to set up a trigger:

// Set up the trigger manually in the Apps Script editor
function createOnChangeTrigger() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    ScriptApp.newTrigger('trigger_app_transaction')
             .forSpreadsheet(ss)
             .onChange()
             .create();
}

//////////////////////////////////////////////////////////////////////
// DETERMINE RUN SOP OR NOT
function trigger_app_transaction() {
    var transactionsSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("03_Transactions");
    
    var lastRow = transactionsSheet.getLastRow();
    var lastRowData = transactionsSheet.getRange(lastRow, 1, 1, transactionsSheet.getLastColumn()).getValues()[0];

    // Get column indices
    var headerRow = transactionsSheet.getRange(1, 1, 1, transactionsSheet.getLastColumn()).getValues()[0];
    var autoColumnIndex = headerRow.indexOf("Auto") + 1;

    // Check if 'Auto' column is not 'Auto'
    if (lastRowData[autoColumnIndex - 1] !== 'Auto') {
        app_transaction_sop();
    }
}

The above code is to set up a trigger that if there is a mannully update in the sheet transaction, then automatically run the function.

function createQuarterlyTriggers() {
    var currentYear = 2024;
    var endYear = currentYear + 0; // Next 1 year

    for (var year = currentYear; year <= endYear; year++) {
        // Quarters start on the first day of January, April, July, October
        var quarterStartMonths = [0, 3, 6, 9]; // 0 for January, 3 for April, and so on

        quarterStartMonths.forEach(function(month) {
            var firstDayOfQuarter = new Date(year, month, 1, 0, 0, 0); // 1st day of the quarter at 00:00:00
            ScriptApp.newTrigger('bot_quarterly_sop')
                .timeBased()
                .at(firstDayOfQuarter)
                .create();
        });
    }
}

The above code is to run the function each quarter in the next year.

function createTriggers() {
    createQuarterlyTriggers();
    createOnChangeTrigger();
}

Then we need a function to combines these two triggers. So next time, we only need to run one function to activate the two triggers.

SOP function

Each automation function contains a lot of steps. So we decomposed into many functions:

//////////////////////////////////////////////////////////////////////
// SOP
function app_transaction_sop() {
  app_A_01_transactions();
  app_B_01_transactions_to_dividends();
  app_B_02_accounts_to_dividends();
  app_B_03_settings_to_dividends();
  app_B_04_dividends();
}


function bot_quarterly_sop() {
  bot_A_01_dividends_to_transactions();
  bot_A_02_transactions();
  bot_A_03_accounts_to_transactions();

  bot_B_01_transactions_to_balances();
  bot_B_02_accounts_to_balances();
  bot_B_03_balances();

  bot_C_01_balances_to_dividends();
  bot_C_02_accounts_to_dividends();
  bot_C_03_settings_to_dividends();
  bot_C_04_dividends();
}

So we can fix the problem one by one.

To be continued:

How to Convert Google Sheets to a Lightweight and Self-running Database with Google Apps Script (2)

How to Convert Google Sheets to a Lightweight and Self-running Database with Google Apps Script (3)