function generateJathaLogEntries() {
const ss = [Link]();
const inputSheet = [Link]("Jatha Attendance");
const codeMapSheet = [Link]("List");
const logSheet = [Link]("Log");
// Get the data from A10 to F (includes Badge, Name, Dept, From Date, To Date)
const inputRange = [Link]("A10:F" + [Link]());
const inputData = [Link]();
// Get Place of Sewa and Department from B2 and B3
const placeOfSewa = [Link]("B2").getValue().trim();
const department = [Link]("B3").getValue().trim();
[Link]("Place of Sewa: " + placeOfSewa);
[Link]("Department: " + department);
// Get mapping from List sheet (R:S:T)
const codeMapData = [Link]("R2:T" +
[Link]()).getValues();
const codeMap = {};
[Link](row => {
const key = `${row[0].toLowerCase().trim()}|${row[1].toLowerCase().trim()}`;
codeMap[key] = row[2];
});
const codeKey = `${[Link]()}|${[Link]()}`;
const code = codeMap[codeKey] || "OTOT";
[Link]("Full Code Mapped: " + code);
const logData = [];
[Link]((row, index) => {
const badgeNumber = row[0];
const name = row[1];
const dept = row[2];
const fromDate = row[4];
const toDate = row[5];
if (!(fromDate instanceof Date) || !(toDate instanceof Date)) {
[Link](`Skipping row ${index + 10} due to invalid dates.`);
return;
}
const days = [Link]((toDate - fromDate) / (1000 * 3600 * 24)) + 1;
for (let i = 0; i < days; i++) {
const date = new Date(fromDate);
[Link]([Link]() + i);
const formattedDate = [Link](date, [Link](),
"dd-MM-yy");
[Link]([badgeNumber, name, dept, formattedDate, code]);
}
});
if ([Link] > 0) {
[Link](2, [Link]); // Push existing data down from row 2
[Link](2, 1, [Link], 5).setValues(logData);
[Link](`${[Link]} entries inserted into Log sheet.`);
} else {
[Link]("No data to insert into Log sheet.");
}
// Clear D3:E3 after successful entry
[Link]("D3:E3").clearContent();
}
function punchAttendance() {
const sheetName = "Daily Duty (Satsang Purpose)";
const spLogSheetName = "SP_Log";
const logSheetName = "Log";
const ss = [Link]();
const sourceSheet = [Link](sheetName);
const spLogSheet = [Link](spLogSheetName);
const logSheet = [Link](logSheetName);
// Get values from A12 to G12
const fullData = [Link]("A12:G12").getValues()[0];
// Check if any required data is empty or null
if (!fullData[0] || !fullData[1] || !fullData[2] || !fullData[3] || !fullData[6])
{
// Show message if any of the essential fields are missing
[Link]().alert("Invalid Operation: Missing or empty required
data. Please fill all fields correctly.");
return; // Stop the function if data is invalid
}
// Insert into SP_Log (top insert)
[Link](1);
const spTargetRange = [Link](2, 1, 1, 7);
[Link]([fullData]);
[Link]();
[Link](2, 5).setNumberFormat("hh:mm AM/PM"); // Time column format
// Convert Duty Type
const dutyType = fullData[6];
let dutyCode = "";
if (dutyType === "SATSANG POINT DUTY") dutyCode = "DD";
else if (dutyType === "WATCH & WARD") dutyCode = "W&W";
else if (dutyType === "DAILY DUTY") dutyCode = "D";
else dutyCode = dutyType;
// Log Sheet Insert
const logData = [[fullData[0], fullData[1], fullData[2], fullData[3], dutyCode]];
[Link](1);
const logTargetRange = [Link](2, 1, 1, 5);
[Link](logData);
[Link]();
// Set B2 = TODAY()
[Link]("B2").setFormula("=TODAY()");
// Reset Formulas
[Link]("E2").setFormula(`=IFNA(XLOOKUP(B1,List!E:E,List!
D:D)&"GA","")`);
[Link]("F2").setFormula(`=IFNA(XLOOKUP(B1,List!E:E,List!
D:D)&"LA","")`);
[Link]("J3:J4").setFormula(`=XLOOKUP(
IF(E2<>"", E2 & TEXT(E3, "0000"),
IF(F3<>"", F2 & TEXT(F3, "0000"), "")
),
'Derived Dump'!A:A,
'Derived Dump'!O:O,
""
)`);
[Link]("A12").setFormula(`=IFNA(IF(
And(E3<>"",J3<>"No"),
E2 & TEXT(E3, "0000"),
IF(F3<>"", F2 & TEXT(F3, "0000"), "")
),"")`);
[Link]("B12").setFormula(`=IFNA(XLOOKUP(A12,'Derived Dump'!
A:A,'Derived Dump'!B:B),"")`);
[Link]("C12").setFormula(`=IFNA(XLOOKUP(A12,'Derived Dump'!
A:A,'Derived Dump'!G:G),"")`);
[Link]("D12").setFormula(`=IFNA(IFS(AND(A12<>"",I3=""),TODAY(),AND(A1
2<>"",I3<>""),I3),"")`);
[Link]("E12").setFormula(`=IF(A12<>"", IF(AND(G4="",H4=""), NOW(),
TIME(G4,H4,0)), "")`);
[Link]("F12").setFormula(`=IFNA(IFS(AND(E12<>"",E12<B4),"P",AND(E12<>
"",E12>=B4,E12<B5),"Late",AND(E12<>"",E12>B5),"A"),"")`);
// Clear G4:H4, I3:I4, G12
[Link]("G4:H4").clearContent();
[Link]("E3:E4").clearContent();
[Link]("F3:F4").clearContent();
[Link]("I3:I4").clearContent();
}