Search code examples
google-apps-scriptgoogle-sheetsweb-applicationstriggersgoogle-sheets-formula

Code Trigger does not work in App Google Sheets

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!

Solution

  • 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:

    enter image description here

    • 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.