Previously

SOP after Transaction added

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

A_01: Complete data in sheet Transaction

function app_A_01_transactions(){
  generateId("03_Transactions", "ID_Transaction", "T_");
  updateQuarterInLastRow("03_Transactions", "Transaction Date", "Dividend Period");
}

function generateId(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 existing IDs in the sheet
  var idColumnIndex = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0].indexOf(idColumnName) + 1;
  var operatingTimeColumnIndex = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0].indexOf('Operating Time') + 1;

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

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

  // Get the postfix of the second last row's ID
  var secondLastId = existingIds[existingIds.length - 2] || "";
  var secondLastIdParts = secondLastId.split("_");
  var lastPostfix = 1;

  if (secondLastIdParts.length === 3 && secondLastIdParts[1] === currentDate) {
    // Increment the postfix if the date is the same
    lastPostfix = parseInt(secondLastIdParts[2]) + 1;
  }

  // Generate a new ID for the last row if it's empty
  var lastRowId = existingIds[existingIds.length - 1];
  if (!lastRowId) {
    var newId = prefix + currentDate + "_" + ("00000" + lastPostfix).slice(-5);
    sheet.getRange(lastRow, idColumnIndex).setValue(newId);
    sheet.getRange(lastRow, operatingTimeColumnIndex).setValue(currentTime);
  }
}

function updateQuarterInLastRow(sheetName, dateColumnName, dividendPeriodColumnName) {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getSheetByName(sheetName);

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

  var lastRow = sheet.getLastRow();
  var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
  
  var dateColumnIndex = headers.indexOf(dateColumnName) + 1;
  var dividendPeriodColumnIndex = headers.indexOf(dividendPeriodColumnName) + 1;

  if (dateColumnIndex === 0 || dividendPeriodColumnIndex === 0) {
    Logger.log("Column not found");
    return;
  }

  var lastRowDateValue = sheet.getRange(lastRow, dateColumnIndex).getValue();

  // Extract year and month from the date string
  var year = lastRowDateValue.slice(0, 4);
  var month = lastRowDateValue.slice(5, 7);

  // Calculate the quarter based on the month
  var quarter = Math.ceil(month / 3);

  // Generate the quarter format: YYYYQX
  var quarterFormat = year + "Q" + quarter;

  // Update "Dividend Period" column in the last row
  sheet.getRange(lastRow, dividendPeriodColumnIndex).setValue(quarterFormat);

  Logger.log("Quarter for last row: " + quarterFormat);
}

B_01: Update sheet Dividend by sheet Transaction

function app_B_01_transactions_to_dividends() {
  copy_transactions_to_dividends();
}

function copy_transactions_to_dividends() {
  var lock = LockService.getScriptLock();
  try {
    lock.waitLock(10000);  // Wait for up to 10 seconds for other processes to finish.
    var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
    var transactionsSheet = spreadsheet.getSheetByName("03_Transactions");
    var dividendsSheet = spreadsheet.getSheetByName("04_Dividends");

    var transactionsData = transactionsSheet.getDataRange().getValues();
    var lastRowTransactions = transactionsData.length;
    var lastRowValues = transactionsData[lastRowTransactions - 1]; // Get the last row values
    var dividendsHeaders = dividendsSheet.getDataRange().getValues()[0];

    // Column indices in the Transactions sheet
    var autoColumnIndex = transactionsData[0].indexOf("Auto");
    var periodColumnIndex = transactionsData[0].indexOf("Dividend Period");
    var idAccountIndex = transactionsData[0].indexOf("ID_Account");
    var transactionAmountIndex = transactionsData[0].indexOf("Transaction Amount");
    var transactionDateIndex = transactionsData[0].indexOf("Transaction Date");
    var idTransactionIndex = transactionsData[0].indexOf("ID_Transaction");

    // Column indices in the Dividends sheet
    var dividendsIdAccountIndex = dividendsHeaders.indexOf("ID_Account");
    var dividendsAmountIndex = dividendsHeaders.indexOf("Transaction Amount");
    var dividendsStartDateIndex = dividendsHeaders.indexOf("Start Date");
    var dividendsPeriodIndex = dividendsHeaders.indexOf("Dividend Period");
    var dividendsIdTransactionIndex = dividendsHeaders.indexOf("ID_Transaction");

    if (autoColumnIndex !== -1) {
      // Check if the value in the "Auto" column is not equal to "Auto"
      if (lastRowValues[autoColumnIndex] !== "Auto") {
        var idTransactionValue = lastRowValues[idTransactionIndex];

        // Check if ID_Transaction already exists in 04_Dividends
        var existingIds = dividendsSheet.getRange(2, dividendsIdTransactionIndex + 1, dividendsSheet.getLastRow() - 1, 1).getValues().flat();
        
        if (existingIds.indexOf(idTransactionValue) === -1) {
          // Prepare the row to be added to the dividends sheet
          var dividendsRow = new Array(dividendsHeaders.length).fill(""); // Create a row with empty strings
          dividendsRow[dividendsIdAccountIndex] = lastRowValues[idAccountIndex];
          dividendsRow[dividendsAmountIndex] = lastRowValues[transactionAmountIndex];
          dividendsRow[dividendsStartDateIndex] = lastRowValues[transactionDateIndex];
          dividendsRow[dividendsPeriodIndex] = lastRowValues[periodColumnIndex];
          dividendsRow[dividendsIdTransactionIndex] = idTransactionValue;

          // Add a new row to the dividends sheet
          dividendsSheet.appendRow(dividendsRow);
        } else {
          Logger.log("ID_Transaction already exists in 04_Dividends. Skipping.");
        }
      }
    }

  } catch (e) {
    Logger.log("Error: " + e);
  } finally {
    lock.releaseLock();
  }
}

B_02: Update sheet Dividend by sheet Account

function app_B_02_accounts_to_dividends(){
  var lists = [    ["Reinvest/Cash Dividend", "Reinvest/Cash Dividend"],
    ["Residency Status in Canada", "Residency Status in Canada"],
    ["Type", "Type"],
    ["Last Name", "Last Name"],
    ["First Name", "First Name"],
    ["Company Name", "Company Name"],
    ["ID_Investor", "ID_Investor"],
  ];
  updateValuesInRowsIfEmpty("02_Accounts", "04_Dividends", "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_03: Update sheet Dividends by sheet Settings

function app_B_03_settings_to_dividends() {
  copy_Settings_to_dividends();
}

function copy_Settings_to_dividends() {
  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 dividendsData = dividendsSheet.getDataRange().getValues();
  var dividendsHeaders = dividendsData[0];
  var lastRowValues = dividendsData[dividendsData.length - 1];

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

  // Get the Dividend Rate value from "99_Settings"
  var dividendRateRow = findRowByName(settingsSheet, "Dividend Rate");
  var dividendRateValue = getValueFromSettings(settingsSheet, dividendRateRow, "Value");

  // Update the Dividend Rate in the last row of "04_Dividends"
  if (dividendRateColumnIndex !== -1 && dividendRateValue !== undefined) {
    dividendsSheet.getRange(dividendsData.length, dividendRateColumnIndex + 1).setValue(dividendRateValue);
  }

  // Get the Residency Status in Canada value from the last row of "04_Dividends"
  var residencyStatusValue = lastRowValues[residencyStatusColumnIndex];

  if (!residencyStatusValue){
    dividendsSheet.getRange(dividendsData.length, taxRateColumnIndex + 1).setValue(0);
  } else {
    var taxRateRow = findRowByName(settingsSheet, residencyStatusValue);
    var taxRateValue = getValueFromSettings(settingsSheet, taxRateRow, "Value");
    dividendsSheet.getRange(dividendsData.length, taxRateColumnIndex + 1).setValue(taxRateValue);
  }

  Logger.log("Dividends updated with values from Settings");
}

// Helper function to find a row in "99_Settings" by name
function findRowByName(sheet, name) {
  var settingsData = sheet.getDataRange().getValues();
  var nameColumnIndex = settingsData[0].indexOf("Global Parameter");
  var rowIndex = settingsData.findIndex(row => row[nameColumnIndex] === name);

  // If no match is found, search for "Others"
  if (rowIndex === -1) {
    rowIndex = settingsData.findIndex(row => row[nameColumnIndex] === "Others");
  }
  return rowIndex;
}


// Helper function to get value from "99_Settings" based on row and column names
function getValueFromSettings(sheet, row, columnName) {
  var settingsData = sheet.getDataRange().getValues();
  var columnNames = settingsData[0];
  var columnIndex = columnNames.indexOf(columnName);

  // Check if the row and columnIndex are valid
  if (row !== -1 && columnIndex !== -1 && settingsData[row] && settingsData[row][columnIndex] !== undefined) {
    return settingsData[row][columnIndex];
  }

  // Return undefined if the value is not found
  return undefined;
}

B_04: Complete sheet Dividends

function app_B_04_dividends(){
  updateDividendPeriodInfo();
  processDividends();
  generateId("04_Dividends", "ID_Dividend", "D_");
}

function updateDividendPeriodInfo() {
  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 lastRowValues = dividendsData[dividendsData.length - 1];

  var dividendPeriodColumnIndex = dividendsHeaders.indexOf("Dividend Period");
  var startDateColumnIndex = dividendsHeaders.indexOf("Start Date");
  var endDateColumnIndex = dividendsHeaders.indexOf("End Date");
  var daysOfValidColumnIndex = dividendsHeaders.indexOf("Days of Valid");
  var daysOfDividendPeriodColumnIndex = dividendsHeaders.indexOf("Days of Dividend Period");
  var autoColumnIndex = dividendsHeaders.indexOf("Auto");

  if (
    dividendPeriodColumnIndex === -1 ||
    startDateColumnIndex === -1 ||
    endDateColumnIndex === -1 ||
    daysOfValidColumnIndex === -1 ||
    daysOfDividendPeriodColumnIndex === -1
  ) {
    Logger.log("One or more columns not found in the Dividends sheet");
    return;
  }

  var dividendPeriodValue = lastRowValues[dividendPeriodColumnIndex];
  var startDateValue = lastRowValues[startDateColumnIndex];

  // 1. Calculate and fill in "End Date" based on the last day of the quarter
  var endDateValue = getLastDayOfQuarter(dividendPeriodValue);
  dividendsSheet.getRange(dividendsData.length, endDateColumnIndex + 1).setValue(formatDate(endDateValue));

  // 2. Calculate and fill in "Days of Dividend Period" based on the days in the quarter
  var daysInQuarter = getDaysInQuarter(dividendPeriodValue);
  dividendsSheet.getRange(dividendsData.length, daysOfDividendPeriodColumnIndex + 1).setValue(daysInQuarter);

  // 3. Calculate and fill in "Days of Valid" based on the days between "Start Date" and "End Date"
  var daysBetween = getDaysBetween(startDateValue, endDateValue);
  dividendsSheet.getRange(dividendsData.length, daysOfValidColumnIndex + 1).setValue(daysBetween);

  // 4. Set the value of "Auto" to "Auto" for the last row
  dividendsSheet.getRange(dividendsData.length, autoColumnIndex + 1).setValue("Auto");

  Logger.log("Dividend Period information updated successfully");
}

// Helper function to get the last day of the quarter
function getLastDayOfQuarter(dividendPeriod) {
  var parts = dividendPeriod.match(/(\d{4})Q(\d)/);
  if (!parts) return undefined;

  var year = parseInt(parts[1]);
  var quarter = parseInt(parts[2]);

  // Calculate the last day of the quarter based on the last month of the quarter
  var lastMonthOfQuarter = quarter * 3;
  var lastDay = new Date(year, lastMonthOfQuarter, 0);
  return lastDay;
}

// Helper function to get the days in the quarter
function getDaysInQuarter(dividendPeriod) {
  var parts = dividendPeriod.match(/(\d{4})Q(\d)/);
  if (!parts) return undefined;

  var year = parseInt(parts[1]);
  var quarter = parseInt(parts[2]);

  // Calculate the first day of the quarter
  var firstMonthOfQuarter = (quarter - 1) * 3;
  var firstDay = new Date(year, firstMonthOfQuarter, 1);

  // Calculate the last day of the quarter
  var lastMonthOfQuarter = quarter * 3;
  var lastDay = new Date(year, lastMonthOfQuarter, 0);

  // Calculate the number of days in the quarter
  var daysInQuarter = Math.round((lastDay - firstDay) / (1000 * 60 * 60 * 24)) + 1;

  return daysInQuarter;
}

// Helper function to calculate the days between two dates
function getDaysBetween(startDate, endDate) {
  var millisecondsPerDay = 24 * 60 * 60 * 1000;
  var start = new Date(startDate);
  var end = new Date(endDate);
  var daysBetween = Math.round((end - start) / millisecondsPerDay);
  return daysBetween;
}

// Helper function to format date as YYYY-MM-DD
function formatDate(date) {
  var year = date.getFullYear();
  var month = (date.getMonth() + 1).toString().padStart(2, '0');
  var day = date.getDate().toString().padStart(2, '0');
  return year + '-' + month + '-' + day;
}

function processDividends() {
  var targetSheetName = "04_Dividends";

  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var targetSheet = spreadsheet.getSheetByName(targetSheetName);

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

  // Get last row data from target sheet
  var targetData = targetSheet.getDataRange().getValues();
  var lastRowTarget = targetData.length;
  var lastRowTargetValues = targetData[lastRowTarget - 1];

  // Calculate "Dividend Amount" based on other columns
  var transactionAmount = lastRowTargetValues[targetSheet.getDataRange().getValues()[0].indexOf("Transaction Amount")];
  var dividendRate = lastRowTargetValues[targetSheet.getDataRange().getValues()[0].indexOf("Dividend Rate")];
  var daysOfValid = lastRowTargetValues[targetSheet.getDataRange().getValues()[0].indexOf("Days of Valid")];
  var daysOfDividendPeriod = lastRowTargetValues[targetSheet.getDataRange().getValues()[0].indexOf("Days of Dividend Period")];
  var taxRate = lastRowTargetValues[targetSheet.getDataRange().getValues()[0].indexOf("Tax Rate")];

  var dividendAmount = (transactionAmount * dividendRate * (daysOfValid / daysOfDividendPeriod) * (1 - taxRate)).toFixed(2);

  // Update "Dividend Amount" column in the last row
  var dividendAmountColumnIndex = targetSheet.getDataRange().getValues()[0].indexOf("Dividend Amount") + 1;
  targetSheet.getRange(lastRowTarget, dividendAmountColumnIndex).setValue(dividendAmount);

  Logger.log("Dividend Amount calculated and updated successfully in the last row.");
}

function generateDividendId() {
  var targetSheetName = "04_Dividends";

  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var targetSheet = spreadsheet.getSheetByName(targetSheetName);

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

  // Get last row and second last row data from target sheet
  var targetData = targetSheet.getDataRange().getValues();
  var lastRowTarget = targetData.length;
  var secondLastRowTargetValues = lastRowTarget > 1 ? targetData[lastRowTarget - 2] : [];

  // Get necessary columns indices
  var dividendPeriodColumnIndex = targetSheet.getDataRange().getValues()[0].indexOf("Dividend Period");
  var dividendIdColumnIndex = targetSheet.getDataRange().getValues()[0].indexOf("ID_Dividend");

  if (dividendPeriodColumnIndex === -1 || dividendIdColumnIndex === -1) {
    Logger.log("Column not found");
    return;
  }

  // Get Dividend Period values from the last two rows
  var lastRowDividendPeriodValue = lastRowTarget > 0 ? targetData[lastRowTarget - 1][dividendPeriodColumnIndex] : "";
  var secondLastRowDividendPeriodValue = secondLastRowTargetValues.length > 0 ? secondLastRowTargetValues[dividendPeriodColumnIndex] : "";

  // Check if the ID is already present in the last row
  var existingDividendId = lastRowTarget > 0 ? targetData[lastRowTarget - 1][dividendIdColumnIndex] : "";
  if (existingDividendId) {
    Logger.log("ID_Dividend already exists in the last row: " + existingDividendId);
    return;
  }

  // Generate the new Dividend ID based on the logic
  var newDividendId;
  if (lastRowDividendPeriodValue !== secondLastRowDividendPeriodValue || !secondLastRowTargetValues || !secondLastRowTargetValues[dividendIdColumnIndex]) {
    newDividendId = "D_" + lastRowDividendPeriodValue + "_00001";
  } else {
    var parts = secondLastRowTargetValues[dividendIdColumnIndex].split("_");
    var number = parseInt(parts[2]) + 1;
    newDividendId = "D_" + lastRowDividendPeriodValue + "_" + ("00000" + number).slice(-5);
  }

  // Update "ID_Dividend" column in the last row
  targetSheet.getRange(lastRowTarget, dividendIdColumnIndex + 1).setValue(newDividendId);

  Logger.log("Dividend ID generated and updated successfully in the last row: " + newDividendId);
}

To be continued: