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

Previously

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

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

SOP of quarterly update

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();
}

A_01: Update sheet Transactions by sheet Dividend

function bot_A_01_dividends_to_transactions(){
  copyRowsToTransactions();
}

function copyRowsToTransactions() {
  var lastDividendPeriod = getLastDividendPeriod(); // Function to get the "Last Dividend Period"
  var sourceSheetName = "04_Dividends";
  var targetSheetName = "03_Transactions";
  var columnName = "Dividend Period";
  var columnValue = lastDividendPeriod;

  var lists = [
    ["ID_Account", "ID_Account"],
    ["Dividend Amount", "Transaction Amount"],
    ["End Date", "Transaction Date"],
    ["Dividend Period", "Dividend Period"],
    ["Auto", "Auto"]
  ];

  copyRowsToTarget(sourceSheetName, targetSheetName, columnName, columnValue, lists);
}

A_02: Complete sheet Transaction

Note: the function generateIDs() is different from generateID(). It is batch function.

function bot_A_02_transactions(){
  generateIDs("03_Transactions", "ID_Transaction", "T_");
}

function generateIDs(sheetName, idColumnName, prefix) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);

  if (!sheet) {
    Logger.log("Sheet not found");
    return;
  }

  var lastRow = sheet.getLastRow();
  var currentDate = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "yyyyMMdd");
  var currentTime = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "yyyy-MM-dd HH:mm:ss");

  // Get the column indices
  var headerValues = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
  var idColumnIndex = headerValues.indexOf(idColumnName) + 1;
  var operatingTimeColumnIndex = headerValues.indexOf("Operating Time") + 1;

  if (idColumnIndex === 0) {
    Logger.log("Column not found: " + idColumnName);
    return;
  }

  if (operatingTimeColumnIndex === 0) {
    Logger.log("Column not found: Operating Time");
    return;
  }

  // Retrieve existing IDs
  var existingIds = sheet.getRange(2, idColumnIndex, lastRow - 1, 1).getValues().flat();

  // Find the last used postfix in IDs
  var lastPostfix = 0;
  existingIds.forEach(function(id) {
    var parts = id.split("_");
    if (parts.length === 3 && parts[1] === currentDate) {
      var currentPostfix = parseInt(parts[2]);
      if (currentPostfix > lastPostfix) {
        lastPostfix = currentPostfix;
      }
    }
  });

  // Iterate through each row and assign IDs and update operating time where necessary
  for (var i = 0; i < existingIds.length; i++) {
    if (!existingIds[i]) {
      lastPostfix++;  // Increment the postfix for each new ID
      var newId = prefix + currentDate + "_" + ("00000" + lastPostfix).slice(-5);
      sheet.getRange(i + 2, idColumnIndex).setValue(newId);  // Set new ID
      sheet.getRange(i + 2, operatingTimeColumnIndex).setValue(currentTime);  // Set operating time
    }
  }
}

A_03: Update sheet Transaction by sheet Account

function bot_A_03_accounts_to_transactions(){
  var lists = [
    ["ID_Investor", "ID_Investor"],
    ["Type", "Type"],
    ["Last Name", "Last Name"],
    ["First Name", "First Name"],
    ["Company Name", "Company Name"]
  ];  
  updateValuesInRowsIfEmpty("02_Accounts", "03_Transactions", "ID_Account", lists);
}

function updateValuesInRowsIfEmpty(sourceSheetName, targetSheetName, keyColumnName, lists) {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sourceSheet = spreadsheet.getSheetByName(sourceSheetName);
  var targetSheet = spreadsheet.getSheetByName(targetSheetName);

  if (!sourceSheet || !targetSheet) {
    Logger.log("Source or target sheet not found");
    return;
  }

  var sourceData = sourceSheet.getDataRange().getValues();
  var targetData = targetSheet.getDataRange().getValues();

  var keyColumnIndexSource = sourceData[0].indexOf(keyColumnName);

  if (keyColumnIndexSource === -1) {
    Logger.log("Key column not found in the source sheet");
    return;
  }

  var idInvestorIndex = targetData[0].indexOf("ID_Investor");
  var idAccountIndex = targetData[0].indexOf("ID_Account");

  // Get the actual values of ID_Account from the source sheet, cleaned and normalized
  var sourceRowKeys = sourceData.slice(1).map(row => normalizeKey(row[keyColumnIndexSource]));

  // Locate the columns dynamically in the target sheet
  var targetColumns = lists.map(pair => {
    return {
      sourceColumn: sourceData[0].indexOf(pair[0]),
      targetColumn: targetData[0].indexOf(pair[1])
    };
  });

  // Iterate through each row in the target sheet
  for (var targetRowIndex = 2; targetRowIndex <= targetSheet.getLastRow(); targetRowIndex++) {
    // Check conditions: ID_Investor is empty and ID_Account is not empty
    if (!targetData[targetRowIndex - 1][idInvestorIndex] && targetData[targetRowIndex - 1][idAccountIndex]) {
      var targetKey = normalizeKey(targetData[targetRowIndex - 1][idAccountIndex]);

      // Find the corresponding row in the source sheet
      var sourceRowIndex = sourceRowKeys.indexOf(targetKey);

      if (sourceRowIndex !== -1) {
        targetColumns.forEach(columnPair => {
          var sourceColumnIndex = columnPair.sourceColumn;
          var targetColumnIndex = columnPair.targetColumn;

          if (sourceColumnIndex !== -1 && targetColumnIndex !== -1) {
            // Update the value in the target sheet
            targetSheet.getRange(targetRowIndex, targetColumnIndex + 1).setValue(sourceData[sourceRowIndex + 1][sourceColumnIndex]);
          } else {
            Logger.log("Column not found in either source or target sheet");
          }
        });
      } else {
        Logger.log("Row not found in the source sheet based on the key");
      }
    }
  }

  Logger.log("Values updated successfully in rows where ID_Investor is empty and ID_Account is not empty.");
}

// Helper function to normalize key values
function normalizeKey(key) {
  return key.toString().toLowerCase().replace(/\s+/g, ''); // Convert to lowercase and remove whitespaces
}

B_01: Update sheet Balance by sheet Transaction

function bot_B_01_transactions_to_balances(){
  updateBalancesForCurrentQuarter();
}

function updateBalancesForCurrentQuarter() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

  // Step 1: Determine current and last quarter
  var currentQuarter = getCurrentDividendPeriod();
  var lastQuarter = getLastQuarter(currentQuarter);

  // Step 2: Find rows in "05_Balances" for last quarter
  var balancesSheet = spreadsheet.getSheetByName("05_Balances");
  var balancesData = balancesSheet.getDataRange().getValues();
  var balancesHeaders = balancesData[0];
  var lastQuarterBalances = findRowsByColumnValue(balancesData, balancesHeaders, "Dividend Period", lastQuarter);

  // Step 3: Get "ID_Account" and "Balance at Start" for each account
  var startBalances = {};
  lastQuarterBalances.forEach(function (row) {
    var idAccount = row[balancesHeaders.indexOf("ID_Account")];
    var balanceAtStart = row[balancesHeaders.indexOf("Balance at Start")];
    // Validate and format "Balance at Start"
    balanceAtStart = (typeof balanceAtStart === 'number' && !isNaN(balanceAtStart)) ? parseFloat(balanceAtStart.toFixed(2)) : 0.00;
    startBalances[idAccount] = balanceAtStart;
  });

  // Step 4: Find rows in "03_Transactions" for last quarter
  var transactionsSheet = spreadsheet.getSheetByName("03_Transactions");
  var transactionsData = transactionsSheet.getDataRange().getValues();
  var transactionsHeaders = transactionsData[0];
  var lastQuarterTransactions = findRowsByColumnValue(transactionsData, transactionsHeaders, "Dividend Period", lastQuarter);

  // Step 5: Aggregate "Transaction Amount" by "ID_Account"
  var balanceChange = {};
  lastQuarterTransactions.forEach(function (row) {
    var idAccount = row[transactionsHeaders.indexOf("ID_Account")];
    var transactionAmount = row[transactionsHeaders.indexOf("Transaction Amount")];
    balanceChange[idAccount] = (balanceChange[idAccount] || 0) + transactionAmount;
  });

  // Step 6: Combine start balances and balance changes for all unique ID_Account values
  var allAccounts = new Set([...Object.keys(startBalances), ...Object.keys(balanceChange)]);
  var currentQuarterBalances = Array.from(allAccounts).map(function (idAccount) {
    var balanceAtStart = startBalances[idAccount] || 0;
    var balanceChangeAmount = balanceChange[idAccount] || 0;
    return [idAccount, balanceAtStart + balanceChangeAmount, currentQuarter];
  });

  // Step 7: Update or add rows to "05_Balances" for current quarter (if not already present)
  currentQuarterBalances.forEach(function (row) {
    var idAccountIndex = balancesHeaders.indexOf("ID_Account");
    var balanceAtStartIndex = balancesHeaders.indexOf("Balance at Start");
    var dividendPeriodIndex = balancesHeaders.indexOf("Dividend Period");

    // Convert balance to a number and format it to two decimal places
    var balanceAtStart = parseFloat(row[1]);
    balanceAtStart = isNaN(balanceAtStart) ? 0.00 : parseFloat(balanceAtStart.toFixed(2));

    var newRow = [];
    newRow[idAccountIndex] = row[0];
    newRow[balanceAtStartIndex] = balanceAtStart;
    newRow[dividendPeriodIndex] = row[2];

    // Check if there is already a record for the current ID_Account in the current quarter
    var existingRow = lastQuarterBalances.find(b => b[idAccountIndex] === row[0] && b[dividendPeriodIndex] === row[2]);

    if (!existingRow) {
      balancesSheet.appendRow(newRow);
    } else {
      // Log a message if a record already exists for the current ID_Account in the current quarter
      Logger.log("Record already exists for ID_Account: " + row[0] + " in Dividend Period: " + row[2]);
    }
  });

  Logger.log("Balances updated successfully for the current quarter");
}

// Helper function to find rows in data based on a column value
function findRowsByColumnValue(data, headers, columnName, columnValue) {
  var columnIndex = headers.indexOf(columnName);
  return data.filter(function (row, index) {
    return index > 0 && row[columnIndex] === columnValue;
  });
}

B_02: Update sheet Balance by sheet Account

function bot_B_02_accounts_to_balances(){
  var lists = [
    ["ID_Investor", "ID_Investor"],
    ["Type", "Type"],
    ["Last Name", "Last Name"],
    ["First Name", "First Name"],
    ["Company Name", "Company Name"]
  ];  
  updateValuesInRowsIfEmpty("02_Accounts", "05_Balances", "ID_Account", lists);  
}

B_03: Complete sheet Balance

function bot_B_03_balances(){
  generateIDs("05_Balances", "ID_Balance", "B_");
}

C_01: Update sheet Dividend by sheet Balance

function bot_C_01_balances_to_dividends(){
  copyBalancesToDividends();
}

function copyBalancesToDividends() {
  try {

    var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

    // Step 1: Determine the current quarter
    var currentQuarter = getCurrentDividendPeriod();

    // Step 2: Find rows in "05_Balances" for the current quarter
    var balancesSheet = spreadsheet.getSheetByName("05_Balances");
    var balancesData = balancesSheet.getDataRange().getValues();
    var balancesHeaders = balancesData[0];
    var currentQuarterBalances = findRowsByColumnValue(balancesData, balancesHeaders, "Dividend Period", currentQuarter);

    // Step 3: Get column indices for "05_Balances"
    var idBalanceIndex = balancesHeaders.indexOf("ID_Balance");
    var idAccountIndexBalances = balancesHeaders.indexOf("ID_Account");
    var balanceAtStartIndex = balancesHeaders.indexOf("Balance at Start");
    var dividendPeriodIndexBalances = balancesHeaders.indexOf("Dividend Period");

    // Step 4: Get column indices for "04_Dividends"
    var dividendsSheet = spreadsheet.getSheetByName("04_Dividends");
    var dividendsData = dividendsSheet.getDataRange().getValues();
    var dividendsHeaders = dividendsData[0];
    var idBalanceIndexDividends = dividendsHeaders.indexOf("ID_Balance");
    var idAccountIndexDividends = dividendsHeaders.indexOf("ID_Account");
    var transactionAmountIndex = dividendsHeaders.indexOf("Transaction Amount");
    var dividendPeriodIndexDividends = dividendsHeaders.indexOf("Dividend Period");

    // Step 5: Copy relevant columns to "04_Dividends"
    currentQuarterBalances.forEach(function (row) {
      // Check if the row already exists in "04_Dividends"
      var existingRow = findRowsByColumnValue(dividendsData, dividendsHeaders, "ID_Balance", row[idBalanceIndex]);

      if (!existingRow || existingRow.length === 0) {
        // Add new row to "04_Dividends"
        var newRow = [];
        newRow[idBalanceIndexDividends] = row[idBalanceIndex];
        newRow[idAccountIndexDividends] = row[idAccountIndexBalances];
        newRow[transactionAmountIndex] = row[balanceAtStartIndex];
        newRow[dividendPeriodIndexDividends] = row[dividendPeriodIndexBalances];
        dividendsSheet.appendRow(newRow);
      }
    });

    Logger.log("Balances copied to Dividends successfully for the current quarter");
  } catch (e) {
    Logger.log("Error in copyBalancesToDividends: " + e.toString());
  }
}

C_02: Update sheet Dividend by sheet Account

function bot_C_02_accounts_to_dividends(){
  var lists = [
    ["ID_Investor", "ID_Investor"],
    ["Type", "Type"],
    ["Last Name", "Last Name"],
    ["First Name", "First Name"],
    ["Company Name", "Company Name"],
    ["Reinvest/Cash Dividend", "Reinvest/Cash Dividend"],
    ["Residency Status in Canada", "Residency Status in Canada"]
  ];  
  updateValuesInRowsIfEmpty("02_Accounts", "04_Dividends", "ID_Account", lists);  
}

C_03: Update sheet Dividend by sheet Setting

function bot_C_03_settings_to_dividends(){
  updateDividendsRowsWithSettings();
}

function updateDividendsRowsWithSettings() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var dividendsSheet = spreadsheet.getSheetByName("04_Dividends");
  var settingsSheet = spreadsheet.getSheetByName("99_Settings");

  if (!dividendsSheet || !settingsSheet) {
    Logger.log("Dividends or Settings sheet not found");
    return;
  }

  var currentQuarter = getCurrentDividendPeriod();
  var dividendsData = dividendsSheet.getDataRange().getValues();
  var dividendsHeaders = dividendsData[0];

  var dividendPeriodColumnIndex = dividendsHeaders.indexOf("Dividend Period");
  var dividendRateColumnIndex = dividendsHeaders.indexOf("Dividend Rate");
  var taxRateColumnIndex = dividendsHeaders.indexOf("Tax Rate");
  var residencyStatusColumnIndex = dividendsHeaders.indexOf("Residency Status in Canada");

  // Get "Dividend Rate" value from "99_Settings"
  var dividendRateRow = findRowByName(settingsSheet, "Dividend Rate");
  var dividendRateValue = getValueFromSettings(settingsSheet, dividendRateRow, "Value");
    // Validate dividend rate value
  if (typeof dividendRateValue !== 'number' || isNaN(dividendRateValue) || dividendRateValue < 0 || dividendRateValue > 100) {
    dividendRateValue = 0;
  }

  // Iterate through all rows in "04_Dividends"
  for (var rowIndex = 1; rowIndex < dividendsData.length; rowIndex++) {
    var currentRow = dividendsData[rowIndex];
    // Process only rows where "Dividend Period" is the current quarter
    if (currentRow[dividendPeriodColumnIndex] === currentQuarter) {

      // Update "Dividend Rate" if column exists
      if (dividendRateColumnIndex !== -1) {
        dividendsSheet.getRange(rowIndex + 1, dividendRateColumnIndex + 1).setValue(dividendRateValue);
      }

      var residencyStatusValue = currentRow[residencyStatusColumnIndex];
      // Set "Tax Rate" as 0 if "Residency Status in Canada" is empty
      if (!residencyStatusValue) {
        dividendsSheet.getRange(rowIndex + 1, taxRateColumnIndex + 1).setValue(0);
      } else {
        // Update "Tax Rate" based on "Residency Status in Canada"
        var taxRateRow = findRowByName(settingsSheet, residencyStatusValue);
        var taxRateValue = taxRateRow !== -1 ? getValueFromSettings(settingsSheet, taxRateRow, "Value") : 0;
        // Validate tax rate value: set to 0 if < 0 or > 100
        if (typeof taxRateValue !== 'number' || isNaN(taxRateValue) || taxRateValue < 0 || taxRateValue > 100) {
          taxRateValue = 0;
        }
        dividendsSheet.getRange(rowIndex + 1, taxRateColumnIndex + 1).setValue(taxRateValue);
      }
    }
  }

  Logger.log("Dividends updated with values from Settings for the current quarter");
}

C_04: Complete sheet Dividend

function bot_C_04_dividends(){
  updateDividendsRows();
  generateIDs("04_Dividends", "ID_Dividend", "D_");
}

function updateDividendsRows() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var dividendsSheet = spreadsheet.getSheetByName("04_Dividends");

  if (!dividendsSheet) {
    Logger.log("Dividends sheet not found");
    return;
  }

  var dividendsData = dividendsSheet.getDataRange().getValues();
  var dividendsHeaders = dividendsData[0];
  var dividendAmountIndex = dividendsHeaders.indexOf("Dividend Amount");
  var idBalanceIndex = dividendsHeaders.indexOf("ID_Balance");
  var dividendPeriodIndex = dividendsHeaders.indexOf("Dividend Period");
  var startDateIndex = dividendsHeaders.indexOf("Start Date");
  var endDateIndex = dividendsHeaders.indexOf("End Date");
  var daysValidIndex = dividendsHeaders.indexOf("Days of Valid");
  var daysDividendPeriodIndex = dividendsHeaders.indexOf("Days of Dividend Period");
  var autoIndex = dividendsHeaders.indexOf("Auto");
  var transactionAmountIndex = dividendsHeaders.indexOf("Transaction Amount");
  var dividendRateIndex = dividendsHeaders.indexOf("Dividend Rate");
  var taxRateIndex = dividendsHeaders.indexOf("Tax Rate");

  var rowsToUpdate = [];

  // Step 1: Find rows with empty "Dividend Amount"
  dividendsData.forEach(function (row, rowIndex) {
    if (row[dividendAmountIndex] === "" && row[idBalanceIndex] !== "") {
      rowsToUpdate.push(rowIndex + 1); // Adding 1 to convert from 0-based to 1-based index
    }
  });

  // Step 2: Update rows
  rowsToUpdate.forEach(function (rowIndex) {
    var transactionAmount = dividendsSheet.getRange(rowIndex, transactionAmountIndex + 1).getValue();
    var dividendRate = dividendsSheet.getRange(rowIndex, dividendRateIndex + 1).getValue();
    var taxRate = dividendsSheet.getRange(rowIndex, taxRateIndex + 1).getValue();

    // Calculate Dividend Amount
    var dividendAmount = transactionAmount * dividendRate * (1 - taxRate);

    // Update columns
    dividendsSheet.getRange(rowIndex, dividendAmountIndex + 1).setValue(dividendAmount);

    var dividendPeriod = dividendsSheet.getRange(rowIndex, dividendPeriodIndex + 1).getValue();
    var startDate = getStartDateOfQuarter(dividendPeriod);
    var endDate = getEndDateOfQuarter(dividendPeriod);
    var daysValid = getDaysBetween(startDate, endDate);
    var daysDividendPeriod = getDaysInQuarter(dividendPeriod);

    // Update additional columns
    dividendsSheet.getRange(rowIndex, startDateIndex + 1).setValue(dateToString(startDate));
    dividendsSheet.getRange(rowIndex, endDateIndex + 1).setValue(dateToString(endDate));
    dividendsSheet.getRange(rowIndex, daysValidIndex + 1).setValue(daysValid);
    dividendsSheet.getRange(rowIndex, daysDividendPeriodIndex + 1).setValue(daysDividendPeriod);
    dividendsSheet.getRange(rowIndex, autoIndex + 1).setValue("Auto");
  });

  // Iterate through all rows in "04_Dividends"
  for (var rowIndex = 1; rowIndex < dividendsData.length; rowIndex++) {
    var currentRow = dividendsData[rowIndex];
    // Check if the "Auto" column is empty and update it
    if (autoIndex !== -1 && !currentRow[autoIndex]) {
      dividendsSheet.getRange(rowIndex + 1, autoIndex + 1).setValue("Auto");
    }
  }

  Logger.log("Dividends rows updated successfully.");
}

END