Financial Performance - Monthly
PT. Jembo Cable Company Tbk
SharePoint
Synced
22 Sep, 13:53
PT. Jembo Cable Company Tbk
setupDatabase di atas lalu klik Jalankan (Run)./exec) dan simpan ke kolom input di atas.// -------------------------------------------------------------
// FUNGSI UJI COBA (Bisa dijalankan langsung di Apps Script Editor)
// -------------------------------------------------------------
function setupDatabase() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
Logger.log("Database SpreadSheet Terhubung: " + ss.getName());
return "Koneksi Spreadsheet Berhasil!";
}
function doGet(e) {
e = e || {};
try {
var ss = SpreadsheetApp.getActiveSpreadsheet();
// Helper to read all rows of a sheet (excluding header)
function readSheetRows(name) {
var s = ss.getSheetByName(name);
if (!s || s.getLastRow() < 2) return [];
return s.getRange(2, 1, s.getLastRow() - 1, s.getLastColumn()).getValues();
}
// Check if the base sheet 'Months' exists and has rows
var monthsRows = readSheetRows('Months');
if (monthsRows.length === 0) {
return ContentService.createTextOutput(JSON.stringify({ status: "empty", message: "Database is empty. Please push data first." }))
.setMimeType(ContentService.MimeType.JSON);
}
var reconstructed = {};
// 1. Months
reconstructed.months = monthsRows.map(function(r) {
return {
key: r[0], name: r[1], enName: r[2], year: Number(r[3]), month: Number(r[4]), fileName: r[5]
};
});
// 2. Proy_Rates
var ratesRows = readSheetRows('Proy_Rates');
reconstructed.projection_inputs = { kurs: {}, copper: {}, aluminium: {}, pvc: {}, output: {} };
var monthMapping = {
'juni': 'jun', 'juli': 'jul', 'agustus': 'aug', 'september': 'sep',
'oktober': 'oct', 'november': 'nov', 'desember': 'dec'
};
var ratesSheet = ss.getSheetByName('Proy_Rates');
if (ratesSheet && ratesSheet.getLastRow() >= 1) {
var ratesHeaders = ratesSheet.getRange(1, 1, 1, ratesSheet.getLastColumn()).getValues()[0];
ratesRows.forEach(function(r) {
var cat = (r[0] || '').toString().trim().toLowerCase();
if (reconstructed.projection_inputs[cat]) {
for (var i = 2; i < r.length; i++) {
var colHeader = (ratesHeaders[i] || '').toString().trim().toLowerCase();
var monthKey = monthMapping[colHeader];
if (monthKey) {
var val = Number(r[i]);
if (!isNaN(val)) {
reconstructed.projection_inputs[cat][monthKey] = val;
}
}
}
}
});
}
// 3. Financials
var financialsRows = readSheetRows('Financials');
reconstructed.financials = {};
financialsRows.forEach(function(r) {
var month = r[0];
var metric = r[1];
var budget = Number(r[2]);
var ly = Number(r[3]);
var prev = Number(r[4]);
var actual = Number(r[5]);
if (!reconstructed.financials[month]) reconstructed.financials[month] = {};
reconstructed.financials[month][metric] = { budget: budget, ly: ly, prev: prev, actual: actual };
});
// 4. AR_AP_Aging
var agingRows = readSheetRows('AR_AP_Aging');
var hasActualsAR = false;
agingRows.forEach(function(r) {
if (r[0] === 'actuals' && r[1] === 'AR') hasActualsAR = true;
});
if (hasActualsAR) {
reconstructed.piutang = [];
} else {
reconstructed.piutang = {};
}
var hasActualsAP = false;
agingRows.forEach(function(r) {
if (r[0] === 'actuals' && r[1] === 'AP') hasActualsAP = true;
});
if (hasActualsAP) {
reconstructed.hutang = { aging: [], bank_debt: [] };
} else {
reconstructed.hutang = {};
}
agingRows.forEach(function(r) {
var month = r[0];
var type = r[1];
var group = r[2];
var itemKey = r[3];
var field = r[4];
var val = r[5];
if (type === 'AR') {
if (hasActualsAR) {
var item = reconstructed.piutang.find(function(x) { return x.category === itemKey; });
if (!item) {
item = { category: itemKey };
reconstructed.piutang.push(item);
}
if (field === 'prev' || field === 'actual' || field === 'variance' || field === 'pct') {
item[field] = Number(val);
} else {
if (!item.values) item.values = {};
item.values[field] = Number(val);
}
} else {
if (!reconstructed.piutang[month]) reconstructed.piutang[month] = [];
var item = reconstructed.piutang[month].find(function(x) { return x.category === itemKey; });
if (!item) {
item = { category: itemKey };
reconstructed.piutang[month].push(item);
}
if (!item.values) item.values = {};
item.values[field] = Number(val);
}
} else if (type === 'AP') {
if (hasActualsAP) {
if (group === 'aging') {
var item = reconstructed.hutang.aging.find(function(x) { return x.category === itemKey; });
if (!item) {
item = { category: itemKey };
reconstructed.hutang.aging.push(item);
}
item[field] = Number(val);
} else if (group === 'bank_debt') {
var item = reconstructed.hutang.bank_debt.find(function(x) { return x.bank === itemKey; });
if (!item) {
item = { bank: itemKey };
reconstructed.hutang.bank_debt.push(item);
}
item[field] = Number(val);
}
} else {
if (!reconstructed.hutang[month]) reconstructed.hutang[month] = [];
var item = reconstructed.hutang[month].find(function(x) { return x.category === itemKey; });
if (!item) {
item = { category: itemKey };
reconstructed.hutang[month].push(item);
}
item[field] = Number(val);
}
}
});
return ContentService.createTextOutput(JSON.stringify({ status: "success", data: reconstructed }))
.setMimeType(ContentService.MimeType.JSON);
} catch (error) {
return ContentService.createTextOutput(JSON.stringify({ status: "error", message: error.toString() }))
.setMimeType(ContentService.MimeType.JSON);
}
}
function doPost(e) {
e = e || {};
if (!e.postData || !e.postData.contents) {
return ContentService.createTextOutput(JSON.stringify({ status: "info", message: "doPost aktif. Kirim POST dari Dashboard untuk menyimpan data." }))
.setMimeType(ContentService.MimeType.JSON);
}
try {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var data = JSON.parse(e.postData.contents);
function getAndClearSheet(name) {
var s = ss.getSheetByName(name);
if (!s) {
s = ss.insertSheet(name);
} else {
s.clear();
}
return s;
}
function writeSheetRows(sheet, headers, rows, headerBg) {
sheet.getRange(1, 1, 1, headers.length).setValues([headers]);
var headerRange = sheet.getRange(1, 1, 1, headers.length);
headerRange.setBackground(headerBg || '#1e293b');
headerRange.setFontColor('#ffffff');
headerRange.setFontWeight('bold');
if (rows.length > 0) {
sheet.getRange(2, 1, rows.length, headers.length).setValues(rows);
}
sheet.autoResizeColumns(1, headers.length);
}
// Write Sheets
if (data.months) {
var mSheet = getAndClearSheet('Months');
var mHeaders = ['Key', 'Name', 'EnName', 'Year', 'Month', 'FileName'];
var mRows = data.months.map(function(m) {
return [m.key, m.name, m.enName, m.year, m.month, m.fileName];
});
writeSheetRows(mSheet, mHeaders, mRows, '#3b82f6');
}
return ContentService.createTextOutput(JSON.stringify({ status: "success", message: "Data berhasil disimpan ke Google Sheets." }))
.setMimeType(ContentService.MimeType.JSON);
} catch (error) {
return ContentService.createTextOutput(JSON.stringify({ status: "error", message: error.toString() }))
.setMimeType(ContentService.MimeType.JSON);
}
}
Terdapat dua metode sinkronisasi dengan Excel Online:
Data Pendukung Performance Review.xlsx) ke OneDrive atau SharePoint./redir? atau /embed? pada URL menjadi /download?.
?download=1 di akhir tautan.
Menghubungkan ke Microsoft SharePoint Online...
Menghubungkan ke Microsoft SharePoint Online...