0

I was have some google script and html (template). and i cant figure how to write the onOpen function inside the "Data (spreadsheet) links", link is bellow.

So the goal is, when i open the "Data (Link)" it will lead me to the form, after i sign in, it will lead me to the "Data (Link)" sheet again.

Is it Possible to do that ?? because logically when the data link is open, it will lead to the form, means that it will always lead to that form. very much thank you for your reply. Best Regards..

https://docs.google.com/spreadsheets/d/1-JMKa9maWG5110H8cy9Pg7eQP6wNI5Nr-miFgKXimUY/edit?usp=sharing

gk adanama
  • 1
  • 1
  • Can you share what you have tried so far? – iansedano Jul 05 '21 at 07:44
  • I think this is not possible. You can't interact with your browser from Apps script. So as example. You can't open a new browser tab with some url in it from apps script. You must manually click on a link to do that. – RemcoE33 Jul 05 '21 at 09:19
  • @iansedano all links is inside the link up there, include the develop script, so what im tring to do is, when im open an spreadsheet link, it will redirect to the login form to fill the login form, when im finish to login it will redirect back to the spreadsheet link that im trying to open. so the login form will store my log data in its own spreadsheet. is it posible to add some script, for example, onOpen, inside my link that im try to open so it can redirect to the login form to fill out, and when im done with form it will redirect me to the link that im try to open. all those form is workd – gk adanama Jul 06 '21 at 00:36
  • @RemcoE33, Thank you for the reply, that's why I'm trying to ask the master here :) I hope there is a conclusion to this issue. – gk adanama Jul 06 '21 at 01:08
  • I'm not sure I understand, can you add screenshots maybe to illustrate what you want? Also, please include the minimal relevant code in the question, don't just put your whole project in links. See [Minimal Reproducible Example](https://stackoverflow.com/help/minimal-reproducible-example). – iansedano Jul 06 '21 at 07:34
  • @iansedano simplest question, for example, I have spreadsheet link, that if im paste it on browser, it will lead me to this form https://prnt.sc/194zwrc. that form is already running, but the problem is, if someone has the spreadsheet link, they can bypass that form. it's kinda useless. maybe some script like onOpen() or something. so when I'm opening the link, the login form will pop out to be filled, then I can access that spreadsheet link. – gk adanama Jul 07 '21 at 00:49
  • @iansedano , i was found this script // Use this code for Google Docs, Forms, or new Sheets. function onOpen() { SpreadsheetApp.getUi() // Or DocumentApp or FormApp. .createMenu('Dialog') .addItem('Open', 'openDialog') .addToUi(); } function openDialog() { var html = HtmlService.createHtmlOutputFromFile('index') .setSandboxMode(HtmlService.SandboxMode.IFRAME); SpreadsheetApp.getUi() // Or DocumentApp or FormApp. .showModalDialog(html, 'Dialog title'); } – gk adanama Jul 07 '21 at 06:45
  • and maybe some IF statemen, that will open the link, when the form has filled out. very thanks for your reply, I m new to this script things. – gk adanama Jul 07 '21 at 07:00
  • i found some script in this link : https://stackoverflow.com/questions/10744760/google-apps-script-to-open-a-url (Number 7). its almost done in this sheet : https://docs.google.com/spreadsheets/d/1j9OjoExKsYcFwCn7s76_YH8RjwDz49HFn-gCkZe33jw/edit?usp=sharing , the loggin form should replacing the sheet link, not in another tab. how to do that ? and open the sheet link when its filled out ? – gk adanama Jul 07 '21 at 07:29
  • So basically you want a login screen for your spreadsheet? – iansedano Jul 07 '21 at 07:48
  • yes, it will opened when im paste the sheet link. – gk adanama Jul 07 '21 at 08:56

1 Answers1

1

Sorry, there is no way to do this

From what I understand, you want to be able to:

  • Send a normal spreadsheet link to someone.
  • Have that link redirect to your own login page.
  • Once user logs in, then redirect them to the spreadsheet.

Further I believe you want the spreadsheet to be inaccessible to the user unless they are logged in.

This is not possible. There is already a log in system, and that is Google's. You share the spreadsheet with who you want to share it with, and then they need to log into their Google account to access it.

If this is your team, then you should probably look into a Workspace account as this has more functionality that you may be looking for, like the ability to identify the user who is accessing your spreadsheet, if they are in your domain.

Ideas for workarounds

  • You could send a link to a web app instead, and then have that redirect them, or provide a link to the spreadsheet. However, once they have the link, they will have the link and can bypass the login screen.
  • You could embed the spreadsheet, so it appears on your web app. However, it is quite easy to find the URL, and further, the sheet needs to be public if you want to embed it.
  • Create a form on your web app. Depending on how much information you have, you could translate it to HTML with Client-to-server communication, that is, sending messages between the client side HTML and JS and the "back end" Apps Script. This is a good way to totally hide and secure your spreadsheet link, however, you will need to implement all of the viewing and editing functionality yourself.

Reference

Edit

Your idea of using something like window.close(); as part of the onOpen function will not work.

This is because the HTML of a popup is loaded in an iframe. This is similar to a web page inside a web page. The problem is that these iframes have strict security policies that prevent the JavaScript within it to access anything outside of it.

You can try calling window.parent on the iframe but it will give you the popup, and then if you call window.parent.parent you will get:

enter image description here

iansedano
  • 6,169
  • 2
  • 12
  • 24
  • thanks for your reply. mostly is right just, one thing, its doesn't relogin into google account, its independent simple login form. Also I was try the onOpen() script, most of the onOpen() Script is work only on menu or sidebar, not in HTML Template, hot to get these onOpen() Sript work with HTML login Template ? https://developers.google.com/apps-script/guides/html/templates#pushing_variables_to_templates. https://developers.google.com/apps-script/reference/base/ui#showmodaldialoguserinterface,-title. https://developers.google.com/apps-script/guides/dialogs#alert_dialogs. howbout combine them – gk adanama Jul 08 '21 at 09:25
  • But this can be bypassed by simply closing the dialog so the user will not have to use your form if they don't want to – iansedano Jul 08 '21 at 09:36
  • there is no way to close the dialog, because its a template HTML script, that cant be closed without fill out the login email and password. the only case is, they can bypass it if using the spreadsheet links. so I'm adding that login menu, try to take control from the last log id, that responsible for the data inside, in case there is something wrong. i found this https://developers.google.com/apps-script/guides/triggers/events#open and this https://developers.google.com/apps-script/reference/script/auth-mode. the case is when I'm refresh the sheet, the script tab is closed by it. – gk adanama Jul 08 '21 at 10:20
  • in another script, when im refresh the sheet, the script tab is turn into login form, that I mention in the screen shoot link before. Those two link is came from this one. https://developers.google.com/workspace/add-ons/concepts/editor-triggers – gk adanama Jul 08 '21 at 10:21
  • I think you may be misunderstanding. If you open a dialog there is always a small `x` that you can use to close the window. If you open the login in a new tab, then you just close the tab. If you have something working then maybe share a video screen capture showing what you mean. – iansedano Jul 08 '21 at 12:21
  • maybe you help me how to find small x in this login page that ive made https://script.google.com/macros/s/AKfycbwwoOvlAMe82Ww1Vwt7SY1Dl4ZDhYN6FG-QYvYukLy-qBsmQOJpUxTKJgpWQvy-bg/exec – gk adanama Jul 09 '21 at 01:16
  • i was found that login script from other page, so i dont really get about the google scrpt yet, maybe another method can work for this issue ? these link maybe ? https://stackoverflow.com/questions/8454510/open-url-in-same-window-and-in-same-tab https://stackoverflow.com/questions/2076299/how-to-close-current-tab-in-a-browser-window – gk adanama Jul 09 '21 at 01:22
  • I'm afraid that won't work either. I have added an edit to my answer to explain that – iansedano Jul 09 '21 at 07:12
  • I have found some sites, and read about boolean, how about that? the statement is: IF someone enters ss (SpreadSheet) link, then some onOpen script check some ss cell, IF in that day especially in last minute, no one is logged in, THAN GO to the login form, and then, when all login form has been filled and when submit button pressed it will redirect back to the ss link, and check IF the past minute Log data has filled out if it does, it will allow the user to use the ss. what do you think about that? It's called a logical statement. https://spreadsheet.dev/conditional-logic-apps-script – gk adanama Jul 09 '21 at 08:27
  • NOT SOLVED. ANOTHER HOMEWORK – gk adanama Jul 12 '21 at 04:30