dope-shit.js

//*****************************GLOBAL*****************************//
/**
 * @NotOnlyCurrentDoc
 */
const ind_data_sheet = SpreadsheetApp.openById(
  "REDACTED"
).getActiveSheet();
const fam_data_sheet = SpreadsheetApp.openById(
  "REDACTED"
).getActiveSheet();
const pretty_data_sheet = SpreadsheetApp.openById(
  "REDACTED"
).getActiveSheet();
const ui = SpreadsheetApp.getUi();
const searchQuery = pretty_data_sheet.getRange("C2").getValue().toUpperCase();
const membersStart = 19;
const locations = "REDACTED";
const letters = "ABCDEFGHIJKLMNOPQRSTUVWXYZ".split("");
//****************************************************************//


function reset() {
  const ss = pretty_data_sheet;
  const data = ss.getDataRange().getValues();
  let rangeList = [];
  for (var row in data) {
    if(row > 100) {
      break;
    }
    for (var col in data[row]) {
      const a1Notation = letters[col] + [parseInt(row) + 1];
      const cell = ss.getRange(a1Notation);
      if(cell.getValue() === "" || cell.getFontWeight() == "bold" || cell.getA1Notation() == "C2") {
        continue
      } else {
        rangeList.push(a1Notation);
      }
    }
  }
  ss.getRangeList(rangeList).clearContent();
}

function main() {
  if(searchQuery === "") {
    ui.alert("FAMILY ID CANNOT BE EMPTY");
    return;
  }
  try {
    const textFinder = fam_data_sheet.createTextFinder(searchQuery);
    textFinder.matchCase(false);
    const targetRow = textFinder.findNext().getRow();  
    fam_data_sheet.setActiveRange(
      fam_data_sheet.getRange("A" + targetRow + ":z" + targetRow)
    );
    const targetFamily = fam_data_sheet.getSelection().getActiveRange().getValues()[0];
    setData(targetFamily);
  } catch(error) {
    ui.alert("INVALID FAMILY-ID/FAMILY DOES NOT EXISTS: " + error);
  }
}

function getFamilyMembers(numberOfMembers) {
  let currentRow = 19;
   const textFinder = ind_data_sheet.createTextFinder(searchQuery);
    textFinder.matchCase(false);
    const rows = textFinder.findAll();
    
    rows.forEach((x)=>{
      setMemberData(ind_data_sheet.getRange(x.getRowIndex(), 1, 1, ind_data_sheet.getLastColumn()).getValues()[0], currentRow)
      currentRow = currentRow + 1;
    })
}

function setMemberData(member, index) {
  const s = locations.MEMBERS.DOB.source + 1;
  const birthday = member[s];
  const age = calculateAge(new Date(birthday));
  member.splice(s, 0 , age);
  for (const location in locations.MEMBERS) {
    let { source, destination } = locations.MEMBERS[location];
    source = source + 2;
    let toWrite = member[source];
    if(destination == "B" && toWrite == "SELF") {
      toWrite = "HEAD";
    }
    if((source == 16 && toWrite == "") || (source == 17 && toWrite == "")) {
      continue;
    }
   
    pretty_data_sheet.getRange(destination + index).setValue(toWrite);
  }
}

function setData(family) {
  for (const location in locations.FAMILY) {
    const { source, destination } = locations.FAMILY[location];
    const toWrite = family[source];
    Logger.log("[" + (source) + "] " + toWrite + " ====> " + destination);
    pretty_data_sheet.getRange(destination).setValue(family[source]);
  }
  getFamilyMembers(family[6]);
}

function calculateAge(birthday) {
  var ageDifMs = Date.now() - birthday.getTime();
  var ageDate = new Date(ageDifMs);
  return Math.abs(ageDate.getUTCFullYear() - 1970);
}