app script GS
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253
let loginSheetName = "Admin"; // Nama sheet yang menyimpan data login
// Sintaks untuk maping ke index.html
function doGet() {
return HtmlService.createHtmlOutputFromFile('dashboard'); // Mengembalikan halaman index
}
function getPage(pageName) {
if (pageName === 'dashboard') {
return HtmlService.createHtmlOutputFromFile('dashboard');
} else if (pageName === 'nilai') {
return HtmlService.createHtmlOutputFromFile('nilai');
} else if (pageName === 'lihat-data') {
return HtmlService.createHtmlOutputFromFile('lihat-data');
} else if (pageName === 'pengaturan') {
return HtmlService.createHtmlOutputFromFile('pengaturan');
}
}
function logoutUser() {
// Fungsi untuk menghapus status login dan mengarahkan ke halaman login
return HtmlService.createHtmlOutput("window.location.href = 'login.html';");
}
function doGet(e) {
// Ambil parameter page dari URL, default ke 'login'
const page = (e.parameter.page || 'login').toLowerCase();
// Daftar halaman HTML yang diizinkan
const allowedPages = ['login', 'lihat-data', 'nilai', 'pengaturan', 'dashboard'];
// Cek apakah halaman yang diminta diizinkan
const safePage = allowedPages.includes(page) ? page : 'login';
return HtmlService.createHtmlOutputFromFile(safePage)
.setTitle("Aplikasi Penilaian")
.setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
}
// Fungsi cek login
// Fungsi untuk memverifikasi login dan mengambil data nama guru
function verifyLogin(username, password) {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Admin');
const data = sheet.getDataRange().getValues(); // Ambil semua data dari sheet Admin
// Loop untuk mencari username dan password yang cocok
for (let i = 1; i < data.length; i++) {
const storedUsername = data[i][1]; // Kolom USERNAME (indeks 1)
const storedPassword = data[i][2]; // Kolom PASSWORD (indeks 2)
if (storedUsername === username && storedPassword === password) {
// Login berhasil, simpan status login
const userProps = PropertiesService.getUserProperties();
userProps.setProperty('username', username); // Menyimpan username login
// Mengembalikan data pengguna yang login, termasuk nama guru
return {
success: true,
nama: data[i][3], // Kolom NAMA GURU (indeks 3)
role: 'Guru' // Menyimpan role sebagai Guru
};
}
}
function getDataGuru() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName("Admin");
const data = sheet.getRange(2, 1, sheet.getLastRow()-1, 6).getValues(); // tanpa header
return data;
}
// Jika tidak ditemukan kecocokan, kembalikan status gagal dengan pesan kesalahan
return {
success: false,
message: 'Username atau Password salah!' // Pesan kesalahan yang jelas
};
}
// Menampilkan halaman dashboard setelah login berhasil
function loadIndexPage(nama, position, email) {
// Ini adalah template untuk halaman index.html setelah login
var template = HtmlService.createTemplateFromFile('dashboard.html'); // Pastikan nama file 'index.html' sesuai
template.nama = nama;
template.position = position;
template.email = email;
// Kembalikan HTML ke browser
return template.evaluate().getContent();
}
function getPage(pageName) {
const allowedPages = ['dashboard', 'nilai', 'lihat-data', 'pengaturan'];
if (!allowedPages.includes(pageName)) {
pageName = 'dashboard';
}
return HtmlService.createHtmlOutputFromFile(pageName).getContent();
}
// Menu di spreadsheet
function onOpen() {
SpreadsheetApp.getUi()
.createMenu('📘 Menu Rapor')
.addItem('📊 Tampilkan Sidebar', 'tampilkanSidebar')
.addToUi();
}
// Buat header jika belum ada di sheet "Data"
function buatHeaderJikaBelumAda() {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("DATA");
if (!sheet) {
SpreadsheetApp.getActiveSpreadsheet().insertSheet("DATA");
}
if (sheet.getLastRow() === 0) {
sheet.appendRow([
"NO", "NAMA", "NISN", "MAPEL", "KELAS",
"TP1", "TP2", "TP3", "TP4", "TP5", "TP6", "TP7", "TP8",
"LM1", "LM2", "LM3", "LM4", "LM5", "LM6", "LM7", "LM8",
"NA STS", "NA SAS", "TANGGAL INPUT"
]);
}
}
function showDataPage() {
return HtmlService.createHtmlOutputFromFile('data')
.setWidth(1200)
.setHeight(600);
}
function getData() {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('DATA'); // ganti sesuai nama sheet kamu
const data = sheet.getDataRange().getValues();
return data;
}
// Simpan data siswa
function simpanData(data) {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data");
const semuaData = sheet.getDataRange().getValues();
const nomorUrut = semuaData.length;
const totalTP = [data.tp_1, data.tp_2, data.tp_3, data.tp_4, data.tp_5, data.tp_6, data.tp_7, data.tp_8]
.reduce((sum, val) => sum + (parseFloat(val) || 0), 0);
const totalLM = [data.lm_1, data.lm_2, data.lm_3, data.lm_4, data.lm_5, data.lm_6, data.lm_7, data.lm_8]
.reduce((sum, val) => sum + (parseFloat(val) || 0), 0);
const na_sts = (totalTP / 8).toFixed(2);
const na_sas = (totalLM / 8).toFixed(2);
const row = [
nomorUrut,
data.nama_siswa,
data.nisn,
data.mata_pelajaran,
data.kelas,
data.tp_1, data.tp_2, data.tp_3, data.tp_4,
data.tp_5, data.tp_6, data.tp_7, data.tp_8,
data.lm_1, data.lm_2, data.lm_3, data.lm_4,
data.lm_5, data.lm_6, data.lm_7, data.lm_8,
na_sts,
na_sas,
new Date()
];
sheet.getRange(sheet.getLastRow() + 1, 1, 1, row.length).setValues([row]);
SpreadsheetApp.flush(); // Pastikan data segera disimpan
resetNomorUrut(); // Menjaga urutan NO tetap rapi
return '✅ Data berhasil disimpan!';
}
// Fungsi reset nomor urut otomatis
function resetNomorUrut() {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data");
const data = sheet.getDataRange().getValues();
for (let i = 1; i < data.length; i++) {
sheet.getRange(i + 1, 1).setValue(i);
}
SpreadsheetApp.flush();
}
// Cari data berdasarkan NISN
function cariData(nisn) {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data");
const data = sheet.getDataRange().getValues();
for (let i = 1; i < data.length; i++) {
if (data[i][2] == nisn) {
return {
rowIndex: i + 1,
nama_siswa: data[i][1],
nisn: data[i][2],
mata_pelajaran: data[i][3],
kelas: data[i][4],
tp_1: data[i][5], tp_2: data[i][6], tp_3: data[i][7], tp_4: data[i][8],
tp_5: data[i][9], tp_6: data[i][10], tp_7: data[i][11], tp_8: data[i][12],
lm_1: data[i][13], lm_2: data[i][14], lm_3: data[i][15], lm_4: data[i][16],
lm_5: data[i][17], lm_6: data[i][18], lm_7: data[i][19], lm_8: data[i][20],
na_sts: data[i][21],
na_sas: data[i][22],
tanggal_input: data[i][23]
};
}
}
return null; // Jika data tidak ditemukan
}
function ambilDataRapor() {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data Rapor");
const data = sheet.getDataRange().getValues(); // Ambil semua data
return data.slice(1); // Hapus header
}
// Skript Untuk integrasi ke pengaturan.html
function simpanDataTP(mapel, tpList, semester) {
const ss = SpreadsheetApp.openById("1AThNmj05y6NqfXWpn9AQOPoNcNf-xpEeRx58WZlQnfY");
let sheet = ss.getSheetByName("TP");
if (!sheet) {
sheet = ss.insertSheet("TP");
sheet.appendRow(["No", "MAPEL", "TP", "Semester"]);
}
const tps = tpList.split("\n").map(tp => tp.trim()).filter(tp => tp !== "");
const startRow = sheet.getLastRow() + 1;
for (let i = 0; i < tps.length; i++) {
sheet.appendRow([
startRow + i - 1, // Nomor urut
mapel,
tps[i],
semester
]);
}
}
function doPost(e) {
try {
const sheet = SpreadsheetApp.openById('1AThNmj05y6NqfXWpn9AQOPoNcNf-xpEeRx58WZlQnfY').getSheetByName('TP');
const data = JSON.parse(e.postData.contents);
const lastRow = sheet.getLastRow();
const no = lastRow + 1; // Menambah nomor urut
sheet.appendRow([no, data.mapel, data.tp, data.semester]);
return ContentService.createTextOutput(JSON.stringify({ success: true }))
.setMimeType(ContentService.MimeType.JSON);
} catch (err) {
return ContentService.createTextOutput(JSON.stringify({ success: false, error: err.message }))
.setMimeType(ContentService.MimeType.JSON);
}
}
JavaScript
INFO