Personal Finance CC Expence Category Report.gs.js

// Sheet 1 - Description,Category
// Sheet 2 - CC Transactions.
// Run main() with Sheet 2 active.

function insertTitle(sheet, destinationColumnName, title) {
  console.log(destinationColumnName)
  var titleCell = sheet.getRange(destinationColumnName + "1");
  titleCell.setValue(title);
}

// Function to convert column letters to numerical values
function columnLetterToNumber(columnLetter) {
  return columnLetter.charCodeAt(0) - 64; // 'A' is 65 in ASCII, so subtracting 64 will give 1
}

// Function to handle prompting of column names
function promptColumnName(promptMessage, defaultValue) {
  var userInput = Browser.inputBox(promptMessage, "Column Name", Browser.Buttons.OK_CANCEL);
  if (userInput === "cancel") return null; // Exit if user cancels the prompt
  if (userInput === null || userInput === '') return defaultValue; // Use default value if user closes the prompt without input
  return userInput;
}

function insertFormulas(sheet, sourceColumnName, destinationColumnName, formula) {
  var sourceColumn = sheet.getRange(sourceColumnName + ":" + sourceColumnName);
  var lastRow = sourceColumn.getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow();
  var destinationColumn = sheet.getRange(destinationColumnName + "2:" + destinationColumnName + lastRow);

  var formulas = [];
  for (var i = 2; i <= lastRow; i++) {
    formulas.push([formula.replace("[1]", sourceColumnName + i)]);
  }
  destinationColumn.setFormulas(formulas);
}

function createPivotTable(rowGroupIds, pivotValueColumnIndex, sheetName = "Pivot Table") {
  console.log({ rowGroupIds, pivotValueColumnIndex, sheetName })
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var pivotSheet = spreadsheet.getSheetByName(sheetName);

  // Delete the existing sheet if it exists
  if (pivotSheet) {
    spreadsheet.deleteSheet(pivotSheet);
  }

  // Identify the entire data range
  var sheet = spreadsheet.getActiveSheet();
  var range = sheet.getDataRange();

  // Create a new sheet for the pivot table
  pivotSheet = spreadsheet.insertSheet(sheetName);

  // Create pivot table
  const pivotTableRange = pivotSheet.getRange('A1');
  const pivotTable = pivotTableRange.createPivotTable(range);

  // Configure pivot table
  rowGroupIds.forEach(function (groupId) {
    pivotTable.addRowGroup(groupId);
  });
  pivotTable.addPivotValue(pivotValueColumnIndex, SpreadsheetApp.PivotTableSummarizeFunction.SUM);
}

// Main function
function main() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  // Prompt user for date column source name
  var dateColumnSourceName = promptColumnName("Enter the source column name for the date (e.g., A):", "A");
  if (dateColumnSourceName === null) return; // Exit if user cancels the prompt

  // Prompt user for category column source name
  var descriptionColumnSourceName = promptColumnName("Enter the source column name for the description (e.g., B):", "B");
  if (descriptionColumnSourceName === null) return; // Exit if user cancels the prompt

  // Prompt user for amount column source name
  var amountColumnSourceName = promptColumnName("Enter the source column name for the amount (e.g., C):", "C");
  if (amountColumnSourceName === null) return; // Exit if user cancels the prompt

  // Prompt user for destination column name
  var destinationColumnName = promptColumnName("Enter the destination column name (e.g., E):", "E");
  if (destinationColumnName === null) return; // Exit if user cancels the prompt

  // Set default values if user does not provide any input
  dateColumnSourceName = dateColumnSourceName || "A";
  descriptionColumnSourceName = descriptionColumnSourceName || "B";
  amountColumnSourceName = amountColumnSourceName || "C";
  destinationColumnName = destinationColumnName || "E";

  // Insert title for year
  insertTitle(sheet, destinationColumnName, "Year");
  // Calculate destinationColumnName + 1 for Month
  var monthDestinationColumn = String.fromCharCode(destinationColumnName.charCodeAt(0) + 1);
  insertTitle(sheet, monthDestinationColumn, "Month");
  // Calculate destinationColumnName + 2 for Category
  var categoryDestinationColumn = String.fromCharCode(destinationColumnName.charCodeAt(0) + 2);
  insertTitle(sheet, categoryDestinationColumn, "Category");

  // Insert formulas for Year, Month, and Category columns
  insertFormulas(sheet, dateColumnSourceName, destinationColumnName, "=YEAR([1])");
  insertFormulas(sheet, dateColumnSourceName, monthDestinationColumn, "=TEXT([1],\"mmmm\")");
  insertFormulas(sheet, descriptionColumnSourceName, categoryDestinationColumn, "=QUERY(Sheet1!$A$1:$B$3000, \"SELECT B WHERE A LIKE '%\" & [1] & \"%' LIMIT 1\", 0)");

  // Convert column letters to numerical values
  var rowGroupIds = [columnLetterToNumber(destinationColumnName), columnLetterToNumber(destinationColumnName) + 1, columnLetterToNumber(destinationColumnName) + 2];
  var pivotValueColumnIndex = columnLetterToNumber(amountColumnSourceName);

  // Create pivot table
  createPivotTable(rowGroupIds, pivotValueColumnIndex, "My Table");
}