代码触发器在App Google表格中不起作用 [英] Code Trigger does not work in App Google Sheets
问题描述
我有一个代码,可以让人们在打开工作表时跳至当前日期行。
我通过以下方式解决了台式机上的问题:
- 为用户提供WebApp的URL,而不是电子表格的URL。
注意:由于
是用户特定的视图,因此无法在WebApp中setActiveRange
模式,而不是一般的编辑,即您
作为编辑者可以为查看者实施。
I have a code that lets people jump to the current date row, when opening my sheet.
I solved the problem on desktop in the following way: Previous solution
Now this should also work in the same way on the iOS and Android App Google Sheets. However, nothing happens if I open the sheet in the App. Does someone know how to trigger jumping to the current date row if the sheet is opened in the App?
Thank you very much!
Both simple and installable triggers are subject to restrictions and cannot be fired by viewers. I therefore suggest you the following workaround:
On every open
event, hide all rows apart from the one of interest.
If you create a WebApp, you can use it to run the row hiding function even if the user opening the link does not have edit permissions.
Sample:
- Code.gs part of the Web App
function doGet() {
return HtmlService.createHtmlOutputFromFile("index.html")
.setSandboxMode(HtmlService.SandboxMode.IFRAME);
}
function onOpen() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var lastRow=sheet.getLastRow();
sheet.showRows(1, lastRow);
var range = sheet.getRange("B:B");
var values = range.getValues();
var day = 24*3600*1000;
var today = parseInt((new Date().setHours(0,0,0,0))/day);
var ssdate;
for (var i=0; i<values.length; i++) {
try {
ssdate = values[i][0].getTime()/day;
}
catch(e) {
}
if (ssdate && Math.floor(ssdate) == today) {
Logger.log(i);
sheet.hideRows(i+2,lastRow-i-1);
sheet.hideRows(1,i);
break;
}
}
}
- index.html part of the Web App
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<script>
function redirect() {
window.open("PASTEHERETHEURLOFTHESPREADSHEET","_blank");
google.script.run.onOpen();
}
</script>
</head>
<body onload="redirect()">
</body>
</html>
- Deploy the WebApp as
me
and give access as required:
- Provide the users the URL of the WebApp instead of the URL to the spreadsheet.
Note: It is not possible to
setActiveRange
within the WebApp, since this is a user-specific view mode, rather than a general edit that you as an editor can enforce for the viewer.
这篇关于代码触发器在App Google表格中不起作用的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!