英文:
Creating a Collaborative Chart Editing Template with Cell Modification Tracking in Google Sheets
问题
我对这个项目有两个不同的要求：
1）我想要一个用作模板的电子表格，在这里人们可以编辑图表。在这个要求中，两个用户可以同时填写图表，而不会覆盖彼此的工作（他们也不应该看到对方）。
2）在某种提交电子表格的形式之后，使每个随后编辑的单元格都更改背景颜色，以显示单元格已经被修改。
（附带一提，有人要求用户不必处理授权脚本的问题，我理解为我只能处理已发布的 Web 应用程序，除非有人知道一种方法可以创建一个新的工作表并以编程方式授权其运行脚本。即使你能解决这个问题，我也不确定它会有所帮助，我仍然需要解决将新创建的工作表的ID添加到脚本触发器的问题，如下所述）。
对于第1点，我的想法是创建一个 Web 应用程序，可以复制主模板并为用户提供一个新副本，下面是我的代码：
const d = new Date();
const fileName = `Created: ${formatDate(d)}`;
console.log(fileName);
const destFolder = DriveApp.getFolderById("ID");
const newID = DriveApp.getFileById("ID").makeCopy(fileName, destFolder).getId();
const file = DriveApp.getFileById(newID);
file.setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.EDIT);
const url = (`https://docs.google.com/spreadsheets/d/${newID}/edit`);
return HtmlService.createHtmlOutput('<script>window.location.href="' + url + '";</script>');
}
function formatDate(date) {
let options = {
year: 'numeric',
month: '2-digit',
day: '2-digit',
hour: '2-digit',
minute: '2-digit',
second: '2-digit',
hour12: false
};
return date.toLocaleString('en-US', options).replaceAll("/", "-").replaceAll(":", "-").replaceAll(", ", "-");
}
我的问题是，我现在陷入困境。我想不出实现第2点的办法。我有一个想法，可以创建一个无绑定的应用程序脚本，其中有一个onEdit触发器，可以像这样运行：
function onEdit(e){
if(e.oldValue && e.value && e.oldValue !== e.value){
e.range.setBackground('red');
}
}
但我需要为每个新创建的工作表添加一个新的触发器，这很繁琐，而且，应用脚本最多只允许25个触发器。我想到了一个荒谬的系统，在触发器接近最大限制时，脚本将复制自身，但这似乎非常不雅。有人有更好的想法吗，我怎样才能完成这个目标？
英文:
I have two different requirements for this project:
-
I want to have a spreadsheet that's used as a template, where people can edit the chart. Within this requirement is that two users can fill out the chart at the same time and not overwrite each other's work (and they also shouldn't see each other).
-
After some form of submitting the spreadsheet, make it that every cell that's subsequently edited has the background change colors, so that it's evident that the cell was modified.
(As a side point, it was requested that user's should not have to deal with authorizing a script to run, which I took to mean that I'm only left to deal with published web apps UNLESS someone knows a way to create a new sheet and programmatically give it authorization to run a script. Even if you could figure that out, I'm not sure it'll help, I would still need to get around the problem of adding a newly created sheet's ID to the script's triggers, as I spell out below).
For number 1, my thought was to make a web app that would duplicate the main template and give the user a new copy, here's my code for that:
const d = new Date();
const fileName = `Created: ${formatDate(d)}`;
console.log(fileName);
const destFolder = DriveApp.getFolderById("ID");
const newID = DriveApp.getFileById("ID").makeCopy(fileName, destFolder).getId();
const file = DriveApp.getFileById(newID);
file.setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.EDIT);
const url = (`https://docs.google.com/spreadsheets/d/${newID}/edit`);
return HtmlService.createHtmlOutput('<script>window.location.href="' + url + '";</script>');
}
function formatDate(date) {
let options = {
year: 'numeric',
month: '2-digit',
day: '2-digit',
hour: '2-digit',
minute: '2-digit',
second: '2-digit',
hour12: false
};
return date.toLocaleString('en-US', options).replaceAll("/", "-").replaceAll(":", "-").replaceAll(", ", "-");
}
My problem is, I'm now stuck. I can't think of a way to accomplish number 2. I had any idea to create a unbound apps script that had an onEdit trigger, which would run like this:
function onEdit(e){
if(e.oldValue && e.value && e.oldValue !== e.value){
e.range.setBackground('red');
}
}
But I would need to add a new trigger for every new sheet created, which is cumbersome, but also, apps scripts only allows for 25 triggers max. I thought of a ridiculous system where a script will copy itself when the triggers are close to maxing out, but that seems very inelegant. Does anyone have any better ideas of how I can pull this off?
答案1
得分: 0
以下是您提供的代码的翻译部分：
如果对任何人有帮助，我解决这个问题的方式是将以下代码部署为Web应用程序。如果有人有任何评论、建议或更好的方法来完成这个任务，我将非常乐意听取。
function doGet() {
const d = new Date();
const fileName = `创建时间：${formatDate(d)}`;
console.log(fileName);
const destFolder = DriveApp.getFolderById([已删除的ID]);
const newID = DriveApp.getFileById([已删除的ID]).makeCopy(fileName, destFolder).getId();
const file = DriveApp.getFileById(newID);
file.setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.EDIT);
const url = `https://docs.google.com/spreadsheets/d/${newID}/edit`;
const html = `<!DOCTYPE html>
<html>
<head>
<title>急诊科分配表</title>
<script>
var formSubmitted = false;
function submitForm() {
var submitButton = document.getElementById("submit-button");
google.script.run.withSuccessHandler(function(date) {
if (!date) {
alert("在提交之前，请在B2单元格中输入日期。确保在输入日期后按Enter键。");
} else {
submitButton.value = "提交成功！";
submitButton.disabled = true;
google.script.run.addConditionalFormatting(date, '${newID}', '${url}');
formSubmitted = true;
}
}).getCellValue('${newID}', 'B2');
}
window.addEventListener('beforeunload', function(event) {
exitPage(event);
});
function exitPage(event) {
if (!formSubmitted) {
event.preventDefault();
event.returnValue = '您确定吗？如果您离开而没有提交，您的数据将丢失！';
google.script.run.withSuccessHandler(function(response) {
if (response && response.deleted) {
console.log("表已删除！");
} else {
console.log("表删除失败！");
}
}).deleteSheet('${newID}', '${fileName}');
}
}
</script>
<style>
html, body {
height: 100%;
margin: 0;
padding: 0;
}
iframe {
width: 100%;
height: 95%;
border: none;
}
#submit-button {
background-color: #008000; /* 设置背景颜色 */
color: #FFFFFF; /* 设置文本颜色 */
padding: 10px 20px; /* 设置按钮的内边距 */
font-size: 16px; /* 设置字体大小 */
border: none; /* 移除按钮边框 */
cursor: pointer; /* 鼠标悬停时添加指针光标 */
}
#submit-button:hover {
background-color: #006400; /* 悬停时更改背景颜色 */
}
</style>
</head>
<body>
<iframe src="${url}" frameborder="0" scrolling="yes"></iframe>
<form>
<input id="submit-button" type="button" value="点击此处提交" onclick="submitForm()">
</form>
<script>
window.addEventListener('unload', exitPage);
</script>
</body>
</html>`
return HtmlService.createHtmlOutput(html);
}
// 其他函数的翻译...
请注意，我只提供了代码的翻译部分，不包括代码中的函数实现。如果您需要更多翻译或有其他问题，请随时提问。
英文:
In case this helps anyone, the way I solved this was by deploying the code below as a web app. If anyone has any comments, suggestions, or ideas of a better way to accomplish this, I would love to hear.
function doGet() {
const d = new Date();
const fileName = `Created: ${formatDate(d)}`;
console.log(fileName);
const destFolder = DriveApp.getFolderById([ID removed]);
const newID = DriveApp.getFileById([ID removed]).makeCopy(fileName, destFolder).getId();
const file = DriveApp.getFileById(newID);
file.setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.EDIT);
const url = `https://docs.google.com/spreadsheets/d/${newID}/edit`;
const html = `<!DOCTYPE html>
<html>
<head>
<title>Emergency Department Assignment Sheet</title>
<script>
var formSubmitted = false;
function submitForm() {
var submitButton = document.getElementById("submit-button");
google.script.run.withSuccessHandler(function(date) {
if (!date) {
alert("Please enter the date in cell B2 before submitting. Make sure press the Enter button after entering the date.");
} else {
submitButton.value = "Submit Successful!";
submitButton.disabled = true;
google.script.run.addConditionalFormatting(date, '${newID}', '${url}');
formSubmitted = true;
}
}).getCellValue('${newID}', 'B2');
}
window.addEventListener('beforeunload', function(event) {
exitPage(event);
});
function exitPage(event) {
if (!formSubmitted) {
event.preventDefault();
event.returnValue = 'Are you sure? If you leave without submitting, your data will be lost!';
google.script.run.withSuccessHandler(function(response) {
if (response && response.deleted) {
console.log("Sheet Deleted!");
} else {
console.log("Sheet Deletion Failed!");
}
}).deleteSheet('${newID}', '${fileName}');
}
}
</script>
<style>
html, body {
height: 100%;
margin: 0;
padding: 0;
}
iframe {
width: 100%;
height: 95%;
border: none;
}
#submit-button {
background-color: #008000; /* Set the background color */
color: #FFFFFF; /* Set the text color */
padding: 10px 20px; /* Set padding for the button */
font-size: 16px; /* Set the font size */
border: none; /* Remove the button border */
cursor: pointer; /* Add a pointer cursor on hover */
}
#submit-button:hover {
background-color: #006400; /* Change the background color on hover */
}
</style>
</head>
<body>
<iframe src="${url}" frameborder="0" scrolling="yes"></iframe>
<form>
<input id="submit-button" type="button" value="Click here to submit" onclick="submitForm()">
</form>
<script>
window.addEventListener('unload', exitPage);
</script>
</body>
</html>`
return HtmlService.createHtmlOutput(html);
}
function addConditionalFormatting(date, newID, url) {
var dORn = "";
date[0] == 0 ? dORn = "Day" : dORn = "Night";
console.log("dORn: " + dORn);
let fileName = `${date[1]} - ${dORn}`;
const spreadsheet = SpreadsheetApp.openById(newID);
spreadsheet.rename(fileName);
console.log(`Renamed file to "${fileName}`);
const sheets = spreadsheet.getSheets();
date = new Date(date[1]);
for (var i = 0; i < sheets.length; i++) {
const range = sheets[i].getRange("A1:E46");
var values = range.getDisplayValues();
var rules = [];
for (var row = 0; row < values.length; row++) {
for (var col = 0; col < values[row].length; col++) {
var cellValue = values[row][col];
var cell = sheets[i].getRange(row + 1, col + 1);
var rule;
if (cell.getA1Notation() === "B2") {
// Rule for cell B2
rule = SpreadsheetApp.newConditionalFormatRule()
.whenFormulaSatisfied(`=B2 <> date(${date.getFullYear()}, ${date.getMonth() + 1}, ${date.getDate()})`)
.setFontColor('#FF0000')
.setRanges([cell])
.build();
} else {
// Rules for other cells
if (cellValue === '' || cellValue === undefined) {
rule = SpreadsheetApp.newConditionalFormatRule()
.whenFormulaSatisfied(`=ISBLANK(${cell.getA1Notation()}) = FALSE`)
.setFontColor('#FF0000')
.setRanges([cell])
.build();
} else {
rule = SpreadsheetApp.newConditionalFormatRule()
.whenFormulaSatisfied(`=${cell.getA1Notation()} <> "${cellValue}"`)
.setFontColor('#FF0000')
.setRanges([cell])
.build();
}
}
rules.push(rule);
} //col loop (inner)
} //row loop (outer)
sheets[i].setConditionalFormatRules(rules);
} //sheet loop
console.log("Set conditional formatting");
sendEmail(url, fileName);
} //end function
function getCellValue(sheetId, cell) {
var value = "";
var sheets = SpreadsheetApp.openById(sheetId).getSheets();
for (var i = 0; i < sheets.length; i++) {
value = sheets[i].getRange(cell).getDisplayValue();
if (value !== "" && value !== undefined) {
value = [i, value];
break;
}
}
return value;
}
function sendEmail(sheetUrl, subject) {
MailApp.sendEmail({
to: "[Email removed]",
subject: subject,
body: "Please find the link to the Google Sheet: " + sheetUrl
});
console.log("Sent Email!");
}
function deleteSheet(id, fileName) {
try {
DriveApp.getFileById(id).setTrashed(true);
console.log(`Sent "${fileName}" to the trash`);
return { deleted: true };
} catch (error) {
console.error(error);
return { deleted: false };
}
}
function formatDate(date) {
let options = {
year: 'numeric',
month: '2-digit',
day: '2-digit',
hour: '2-digit',
minute: '2-digit',
second: '2-digit',
hour12: false
};
return date.toLocaleString('en-US', options).replaceAll("/", "-").replaceAll(":", "-").replaceAll(", ", "-");
}
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论