-1

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!

ziganotschka
  • 25,866
  • 2
  • 16
  • 33
Karl Müller
  • 15
  • 7
  • These seem related if not duplicates for your question - [google speadsheet api onOpen does not work on mobile ios](https://stackoverflow.com/q/50784573/1330560) and [Executing Google Apps Script Functions from Mobile App](https://stackoverflow.com/q/33373826/1330560) – Tedinoz Oct 02 '19 at 13:39

1 Answers1

1

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.

ziganotschka
  • 25,866
  • 2
  • 16
  • 33
  • Hey thank you very much for all your help! However, I think I explained the problem wrong. I am satisfied with how everything works on desktop. I share a link which allows the person to edit, yet only the current day: you provided the code https://stackoverflow.com/questions/58060110/google-sheets-exclude-the-current-date-row-from-protection. Further, if you open the spreadsheet you should jump. Both is not the case. Is there any way to relatively easy achieve this also in the Google Sheets App? I am sorry the similar solutions presented above are a little bit over my skill level. – Karl Müller Oct 04 '19 at 11:15
  • Onedit triggers work on Adroid devices, but as you realized yourself - they do not allow to obtain the active range for viewers (I believe this is what you want. This is why I offered you a workaround: hiding all the rows before the active row so that the active row will be automatically the upper one and selected one when a person opens a sheet. The solution however implies using a Web Appwhich means opening Spreadsheets in a browser rather than in the App. – ziganotschka Oct 06 '19 at 19:11