Generate a table of payment records from a drive folder with invoices formatted with details.

Drive File Link Generator.gs

/**
 * Google Apps Script for managing a Reimbursement Table in Google Sheets.
 *
 * Features:
 * - Adds file details to a main sheet named 'File Records'
 * - Tracks processed files using hashes in a 'Meta' sheet to avoid reprocessing
 * - Ensures only the specified sheets exist in the spreadsheet
 * - Always keeps 'Meta' sheet at the end
 * 
 * Assumptions:
 * - The script is run within the context of a spreadsheet named 'Reimbursement Table'
 * - Files are processed based on their URLs, assuming URL is unique for each file (even if renamed)
 * - File name format for extracting details: "<2-digit-day><3-letter-month>_<description>_<amount>"
 * 
 * Gotchas:
 * - Any other sheets except 'File Records' and 'Meta' will be deleted upon execution
 * - Always backup data before executing script to avoid unintended data loss
 * - If file naming convention changes, 'parseFileName()' function needs to be updated
 *
 * Author: Deepraj Pandey
 * Version: 1.0.3
 * Last Updated: 05 Oct, 2023
 */

const BATCH_SIZE = 5;

const CONTAINER_FILENAME = "Reimbursement Table"; // This script runs in container file
const RECORDS_SHEET_NAME = "File Records";
const META_SHEET_NAME = "Meta"; // Subsheet to store the hash values

function listLinks() {
  const activeSpreadsheetId = SpreadsheetApp.getActiveSpreadsheet().getId();
  
  // Ensure only required sheets exist
  removeExtraSheets(activeSpreadsheetId);

  const sheet = initializeSheet(RECORDS_SHEET_NAME);
  addHeaderIfNotExist(sheet);
  
  const metaSheet = initializeSheet(META_SHEET_NAME);
  const hashSet = buildHashSet(metaSheet);

  processFiles(activeSpreadsheetId, sheet, metaSheet, hashSet);

  // Organise all subsheets appropriately
  orderSheets(activeSpreadsheetId);
}


function processFiles(activeSpreadsheetId, recordsSheet, metaSheet, hashSet) {
  const parentFolders = DriveApp.getFileById(activeSpreadsheetId).getParents();

  let fileBatch = [];
  while (parentFolders.hasNext()) {
    const parentFolder = parentFolders.next();
    // Logger.log(`Processing folder: ${parentFolder.getName()} with ID: ${parentFolder.getId()}`);
    
    const files = parentFolder.getFiles();
    
    while (files.hasNext()) {
      const file = files.next();
      // Logger.log(`Processing file: ${file.getName()} with ID: ${file.getId()}`);
      
      // Ignore the container spreadsheet
      if (file.getName() === CONTAINER_FILENAME) {
        // console.info(`Skipping: ${file.getName()}`);
        continue;
      }

      // Check if file is already processed by comparing hash in MetaSubsheet
      if (!checkAndAddHash(file, metaSheet, hashSet)) {
        // console.info(`Skipping: ${file.getName()}`);
        continue;
      }

      const fullFileName = file.getName();
      const hasExtension = fullFileName.includes(".");
      const fileNameWithoutExtension = hasExtension ? fullFileName.split('.').slice(0, -1).join('.') : fullFileName;
      const fileNameParts = parseFileName(fileNameWithoutExtension);

      fileBatch.push([
        "", // placeholder for S.No
        fileNameParts.date, 
        "", // placeholder for Bill No
        fileNameParts.description, 
        fileNameParts.amount,
        `=HYPERLINK("${file.getUrl()}","Y")`, 
        ""  // placeholder for Remarks/Cost Centre
      ]);

      if (fileBatch.length === BATCH_SIZE) {
        appendToSpreadsheet(recordsSheet, fileBatch);
        fileBatch = [];  // Reset the batch after writing to the sheet
      }
    }
  }

  // Handle any remaining files after the loop
  if (fileBatch.length > 0) {
    appendToSpreadsheet(recordsSheet, fileBatch);
  }
}


function initializeSheet(sheetName) {
  let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  if (!sheet) {
    sheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet(sheetName);
  }
  return sheet;
}

function removeExtraSheets(activeSpreadsheetId) {
  const allowedSheets = [RECORDS_SHEET_NAME, META_SHEET_NAME];
  const spreadsheet = SpreadsheetApp.openById(activeSpreadsheetId);
  const allSheets = spreadsheet.getSheets();

  for (let sheet of allSheets) {
    if (!allowedSheets.includes(sheet.getName())) {
      spreadsheet.deleteSheet(sheet);
    }
  }
}

function orderSheets(activeSpreadsheetId) {
  const spreadsheet = SpreadsheetApp.openById(activeSpreadsheetId);
  const metaSheet = spreadsheet.getSheetByName(META_SHEET_NAME);
  
  // Move Meta sheet to the last position
  if (metaSheet) {
    spreadsheet.setActiveSheet(metaSheet);
    spreadsheet.moveActiveSheet(spreadsheet.getNumSheets());
  } else {
    // Meta subsheet should exist by now
    throw new Error("Meta sheet not found.");
  }
}


function removeExtraSheets(activeSpreadsheetId) {
  const allowedSheets = [RECORDS_SHEET_NAME, META_SHEET_NAME];
  const spreadsheet = SpreadsheetApp.openById(activeSpreadsheetId); 
  const allSheets = spreadsheet.getSheets();

  for (let sheet of allSheets) {
    if (!allowedSheets.includes(sheet.getName())) {
      spreadsheet.deleteSheet(sheet);
    }
  }
}


function addHeaderIfNotExist(sheet) {
  const headers = ["S. No", "Date", "Bill No", "Description/ Nature of Expense/Details", "Amount Rs.", "Receipt Attached Y/N", "Remarks/Cost Centre"];
  const firstCellValue = sheet.getRange(1, 1).getValue();
  
  if (!firstCellValue || firstCellValue !== headers[0]) {
    sheet.insertRowBefore(1);
    sheet.getRange(1, 1, 1, headers.length).setValues([headers]).setFontWeight("bold");
  }
}


function buildHashSet(metaSheet) {
  const existingHashes = metaSheet.getDataRange().getValues();
  const hashSet = {};
  for (let i = 0; i < existingHashes.length; i++) {
    hashSet[existingHashes[i][0]] = true;
  }
  return hashSet;
}


function doesHashExist(hash, hashSet) {
  return hashSet[hash];
}


function checkAndAddHash(file, metaSheet, hashSet) {
  const fileNameHash = Utilities.computeDigest(Utilities.DigestAlgorithm.SHA_256, file.getUrl());
  const hashString = fileNameHash.join('');

  if (doesHashExist(hashString, hashSet)) {
    return false;
  }
  metaSheet.appendRow([hashString]);
  hashSet[hashString] = true;  // Update the hashSet in-memory as well
  return true;
}


function parseFileName(fileName) {
  const parts = fileName.split('_');
  
  // Convert "04aug" to "04 Aug"
  const date = `${parts[0].substring(0, 2)} ${parts[0].substring(2).charAt(0).toUpperCase()}${parts[0].substring(2).slice(1)}`;
  
  // Extract the middle part for the description, ignoring the first and last parts.
  const descriptionParts = parts.slice(1, parts.length - 1);
  const description = descriptionParts.map(word => word.charAt(0).toUpperCase() + word.slice(1)).join(' ');

  const amount = parts[parts.length - 1];

  return { date, description, amount };
}


function appendToSpreadsheet(sheet, fileData) {
  const startRow = sheet.getLastRow() + 1;
  const range = sheet.getRange(startRow, 1, fileData.length, fileData[0].length);
  range.setFontWeight("normal");

  // Populate S.No with sequential numbers
  for (let i = 0; i < fileData.length; i++) {
    // Adjust Sl No for 1 header row. rowNum - 1.
    fileData[i][0] = startRow + i - 1;
  }
  
  range.setValues(fileData);
}