英文:
How to show columns based on today's date, and hide the rest?
问题
我是新手，对Google表格和编码不太熟悉，希望能得到一些帮助，因为我已经尝试了几天来解决这个问题。
当我点击B1处的复选框时，我想让当前工作表只显示"今天"列，并隐藏其余列，该如何操作？假设今天是7月12日，那么它将显示A、B、L、M和N列；并隐藏其余列。在此之前，非常感谢。
我尝试过搜索类似的结果，反复编辑脚本，但都没有成功。我知道问题出在我身上哈哈。
英文:
I am new to Google sheets and coding stuffs, hoping to get some help, as I have been trying to fix this for couple of days.
I want my current sheet to show only Today column, and hide the rest of the columns when I click the checkbox at B1, how do I go about it? Say today is 12th Jul, so it will show Column A,B,L,M,N; and hide the rest of the column. Thank you in advance.
I tried to google for similar result, edited the script over and over again but didnt work. I know the problem lies in me lol.
答案1
得分: 0
在你的情况下，以下是一个示例脚本如何：
示例脚本：
请将以下脚本复制并粘贴到Google电子表格的脚本编辑器中，并保存脚本。
当您使用此脚本时，请勾选“出勤”工作表的“B1”的复选框。通过这样做，脚本会自动由onEdit的简单触发器运行。请不要直接运行onEdit的脚本。因为在这种情况下，事件对象e是未定义的，会出现类似“TypeError: Cannot destructure property 'range' of 'e' as it is undefined.”的错误。请注意这一点。
function onEdit(e) {
const { range } = e;
const sheet = range.getSheet();
if (range.getA1Notation() != "B1" || sheet.getSheetName() != "Attendance") return;
if (!range.isChecked()) {
sheet.showColumns(3, sheet.getMaxColumns() - 2);
return;
}
const now = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "d-MMM");
sheet.hideColumns(3, sheet.getMaxColumns() - 2);
sheet.getRange(3, 3, 1, sheet.getLastColumn() - 2).getDisplayValues()[0]
.forEach((e, i) => {
if (e == now) {
sheet.showColumns(i + 3, 3);
}
});
}
测试：
当此脚本在您提供的电子表格上运行时，将获得以下结果。当我今天（2023年07月12日）勾选了“B1”的复选框时，获得了以下结果。
注意：
- 此脚本适用于您提供的电子表格。当您更改电子表格时，此脚本可能无法使用。请注意这一点。
参考：
英文:
In your situation, how about the following sample script?
Sample script:
Please copy and paste the following script to the script editor of Google Spreadsheet, and save the script.
When you use this script, please check the checkbox of "B1" of "Attendance" sheet. By this, the script is automatically run by the simple trigger of onEdit. And, please don't directly run the script of onEdit. Because in that case, the event object e is not given. So, an error like TypeError: Cannot destructure property 'range' of 'e' as it is undefined. occurs. Please be careful about this.
function onEdit(e) {
const { range } = e;
const sheet = range.getSheet();
if (range.getA1Notation() != "B1" || sheet.getSheetName() != "Attendance") return;
if (!range.isChecked()) {
sheet.showColumns(3, sheet.getMaxColumns() - 2);
return;
}
const now = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "d-MMM");
sheet.hideColumns(3, sheet.getMaxColumns() - 2);
sheet.getRange(3, 3, 1, sheet.getLastColumn() - 2).getDisplayValues()[0]
.forEach((e, i) => {
if (e == now) {
sheet.showColumns(i + 3, 3);
}
});
}
Testing:
When this script is run to your provided Spreadsheet, the following result is obtained. When I checked the checkbox of "B1" today (2023-07-12), the following result is obtained.
Note:
- This script is for your provided Spreadsheet. When you change the Spreadsheet, this script might not be able to be used. Please be careful about this.
References:
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论