Как разрешить пользователям с ограниченными правами регистрировать данные в защищенных таблицах Google через боковую пан ⇐ Html

[Anonymous]

Я разработал приложение Google Таблицы с настраиваемой боковой панелью, позволяющей пользователям заносить данные в защищенный лист («Лист журнала прогресса»). Боковая панель реализована в HTML (Page.html) и взаимодействует со скриптом Google Apps (Log Enter.gs). Мне нужно запретить определенным пользователям редактировать лист вручную, разрешив им отправлять данные через боковую панель.
Пользователи с ограниченным доступом к «Листу журнала прогресса» не могут использовать боковую панель для регистрации данных, поскольку у них нет доступа к редактированию листа. Мне нужен способ, позволяющий этим пользователям отправлять данные через боковую панель, не предоставляя им прямого доступа к редактированию таблицы.
Все пользователи в моей таблице находятся в домене Google Workspace. Не уверен, что эта информация может быть чем-то полезна.
Войти Enter.gs:
function openSidebar() {
var lists = fetchDataLists();
Logger.log(lists);
var html = HtmlService.createTemplateFromFile('Page');
html.names = lists.names;
html.schools = lists.schools;
html.processes = lists.processes;
SpreadsheetApp.getUi().showSidebar(html.evaluate().setTitle('Production Log Entry').setWidth(300));
}

function fetchDataLists() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var names = ss.getSheetByName("Progress Log Sheet").getRange("N7:N").getValues().flat().filter(String);
var schools = ss.getSheetByName("23-24 COMPOSITES").getRange("A2:A").getValues().flat().filter(String);
var processes = ["Filtering", "First Correction", "Double Check", "Ripping"];

schools.sort(); // Add this line to sort the schools array

return {names: names, schools: schools, processes: processes};
}

function logDataFromSidebar(data) {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Progress Log Sheet");
var name = data.name;
var school = data.school;
var process = data.process;
var classCount = data.classCount;
var startTime = data.startTime;
var endTime = data.endTime;
var notes = data.notes;

var logs = sheet.getRange("B7:I" + sheet.getLastRow()).getValues();
var duplicateFound = false;
for (var i = 0; i < logs.length; i++) {
if (logs[2] === school && logs[3] === process) {
duplicateFound = true;
break;
}
}

if (name !== "" && school !== "" && process !== "" && !duplicateFound) {
var currentDate = new Date();
var utcDate = new Date(currentDate.toLocaleString("en-US", { timeZone: "America/Los_Angeles" }));
var formattedDate = Utilities.formatDate(utcDate, "GMT-7", "MM/dd/yy");
var formattedTime = Utilities.formatDate(utcDate, "GMT-7", "h:mm:ss a");

// Use adjustedDuration if available, otherwise calculate from startTime and endTime
var totalMinutes = data.adjustedDuration !== undefined ? data.adjustedDuration : getMinutesFromTime(endTime) - getMinutesFromTime(startTime);

sheet.appendRow(["", name, school, process, classCount, startTime, endTime, notes, formattedDate, formattedTime, totalMinutes]);

// Call updateBreakType at the end
updateBreakType();
} else {
if (name === "") {
SpreadsheetApp.getUi().alert("Name is missing. Please fill in the Name field.");
} else if (school === "") {
SpreadsheetApp.getUi().alert("School is missing. Please fill in the School field.");
} else if (process === "") {
SpreadsheetApp.getUi().alert("Process is missing. Please fill in the Process field.");
} else if (duplicateFound) {
SpreadsheetApp.getUi().alert("A log with the same School and Process already exists.");
}
}
}

function getMinutesFromTime(time) {
var timeComponents = time.split(":");
var hours = parseInt(timeComponents[0]);
var minutes = parseInt(timeComponents[1].split(" ")[0]);
var meridiem = timeComponents[1].split(" ")[1].toUpperCase();

if (meridiem === "PM" && hours !== 12) {
hours += 12;
} else if (meridiem === "AM" && hours === 12) {
hours = 0;
}

return hours * 60 + minutes;
}

function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu('Composites')
.addItem('Production Log Sheet', 'openSidebar')
.addItem('Customer Service', 'openCustomerServiceUI')
.addItem('Notes/Requests', 'openClientRequestsUI')
.addToUi();
}

Page.html





body {
font-family: Arial, sans-serif;
padding: 15px;
}
h2 {
color: #2C3E50;
border-bottom: 2px solid #3498DB;
padding-bottom: 10px;
}
form {
margin-top: 20px;
}
label {
display: block;
margin-bottom: 5px;
font-weight: bold;
}
.option-label {
font-weight: normal;
display: flex;
align-items: center;
margin-bottom: 12px;
position: relative;
}
select, textarea, input[type='text'] {
width: 100%;
padding: 8px;
margin-bottom: 15px;
border: 1px solid #BDC3C7;
border-radius: 4px;
}
input[type="checkbox"] {
margin-right: 10px;
}
input[type="button"] {
background-color: #3498DB;
color: white;
padding: 10px 15px;
border: none;
border-radius: 4px;
cursor: pointer;
transition: background-color 0.3s ease;
}
input[type="button"]:hover {
background-color: #2980B9;
}
input[type="button"]:disabled {
background-color: #AED6F1;
cursor: not-allowed;
}
.break-lunch-options {
margin-top: 20px;
padding: 10px;
border: 1px solid #BDC3C7;
border-radius: 4px;
background-color: #f9f9f9;
}
.break-lunch-options h3 {
color: #3498DB;
margin-bottom: 10px;
}
.disabled-checkbox {
opacity: 0.6;
pointer-events: none;
}
.space-above {
margin-top: 20px;
}
/* Adjusting the checkbox alignment */
.checkbox-inline {
position: relative;
padding-left: 24px;
margin-right: 10px;
cursor: pointer;
}
.checkbox-inline input[type='checkbox'] {
position: absolute;
opacity: 0;
cursor: pointer;
height: 0;
width: 0;
}
.checkmark {
position: absolute;
top: 0;
left: 0;
height: 20px;
width: 20px;
background-color: #eee;
border-radius: 4px;
}



Enter Log Data

Name:

Select Name





MM





AM
PM


End Time:


HH





MM





AM
PM


Breaks and Lunch
10 Minute Break
30 Minute Lunch
1 Hour Lunch

Notes:






document.addEventListener("DOMContentLoaded", function() {
console.log("Names:", document.querySelector('[name="name"]').innerHTML);
console.log("Schools:", document.querySelector('[name="school"]').innerHTML);
console.log("Processes:", document.querySelector('[name="process"]').innerHTML);
});

function toggleCheckboxes(selectedCheckbox, value) {
var checkboxes = document.querySelectorAll('input[name="breakLunchOption"]');
checkboxes.forEach(function(checkbox) {
if (checkbox.value !== value) {
checkbox.disabled = selectedCheckbox.checked;
checkbox.closest('.option-label').classList.toggle('disabled-checkbox', selectedCheckbox.checked);
} else {
checkbox.disabled = false;
}
});
}

function handleButtonClick(button) {
var startHour = document.querySelector('[name="startHour"]').value;
var startMinute = document.querySelector('[name="startMinute"]').value;
var startMeridiem = document.querySelector('[name="startMeridiem"]').value;
var endHour = document.querySelector('[name="endHour"]').value;
var endMinute = document.querySelector('[name="endMinute"]').value;
var endMeridiem = document.querySelector('[name="endMeridiem"]').value;

var startTime = startHour + ':' + startMinute + ' ' + startMeridiem;
var endTime = endHour + ':' + endMinute + ' ' + endMeridiem;

var selectedBreak = document.querySelector('input[name="breakLunchOption"]:checked');

if (!validateTimeSelection(startTime, endTime, selectedBreak)) {
window.alert("The selected break or lunch option is not valid for the chosen time interval.");
return;
}

button.disabled = true;

logData().finally(() => {
document.getElementById('dataForm').reset();
toggleCheckboxes();
button.disabled = false;
});
}

function logData() {
return new Promise((resolve, reject) => {
var formData = {};
var formElements = document.getElementById("dataForm").elements;
for (var i = 0; i < formElements.length; i++) {
if (formElements.type !== "button") {
formData[formElements.name] = formElements.value;
}
}

formData["startTime"] = formData["startHour"] + ":" + formData["startMinute"] + " " + formData["startMeridiem"];
formData["endTime"] = formData["endHour"] + ":" + formData["endMinute"] + " " + formData["endMeridiem"];

var startTime = parseTime(formData["startTime"]);
var endTime = parseTime(formData["endTime"]);
var totalDuration = endTime - startTime;

var breakDuration = document.querySelector('input[name="breakLunchOption"]:checked') ? parseInt(document.querySelector('input[name="breakLunchOption"]:checked').value) : 0;
var adjustedDuration = totalDuration - breakDuration;

console.log("Break Duration: " + breakDuration);
console.log("Total Duration: " + totalDuration);
console.log("Adjusted Duration: " + adjustedDuration);

formData["adjustedDuration"] = adjustedDuration;

google.script.run.withSuccessHandler(function(response) {
resetFormState(); // Reset the form state after successful logging
resolve();
}).withFailureHandler(function() {
resetFormState(); // Reset the form state even if logging fails
reject();
}).logDataFromSidebar(formData);
});
}

function resetFormState() {
document.getElementById('dataForm').reset(); // Reset the form values
resetCheckboxesAndButton(); // Reset checkboxes and button state
}

function resetCheckboxesAndButton() {
var checkboxes = document.querySelectorAll('input[name="breakLunchOption"]');
checkboxes.forEach(function(checkbox) {
checkbox.disabled = false; // Re-enable all checkboxes
checkbox.checked = false; // Uncheck all checkboxes
checkbox.closest('.option-label').classList.remove('disabled-checkbox'); // Remove the disabled style
});

var logButton = document.querySelector('input[type="button"]');
logButton.disabled = false; // Re-enable the log button
}

function validateTimeSelection(start, end, selectedBreak) {
if (!selectedBreak) return true;
var startTime = parseTime(start);
var endTime = parseTime(end);
var totalMinutes = endTime - startTime;
var breakDuration = parseInt(selectedBreak.value);
return totalMinutes >= breakDuration;
}

function parseTime(timeStr) {
var parts = timeStr.match(/(\d+):(\d+) (\w+)/);
var hours = parseInt(parts[1], 10);
var minutes = parseInt(parts[2], 10);
var meridian = parts[3];
if (meridian === 'PM' && hours < 12) hours += 12;
if (meridian === 'AM' && hours === 12) hours = 0;
return hours * 60 + minutes;
}





Подробнее здесь: https://stackoverflow.com/questions/774 ... ia-apps-sc