Script Access to outside user

[Bob Bryan]

I have a project using Google Sheets.  The project is in a Google Workplace environment. It is a Customer Donation Pickup Request system. the Scripts create a form allowing a customer to link to the form from a URL in our website. They are not members of our organization, but I want them to be able to enter a request. The scripts work fine when utilized by members of the organization but when the URL is accessed by someone outside the organization, they get unable to find file. The problem is that I would like them to be able to access and execute the form. Any help on getting around the autorization?

[Keith Andersen]

https://knowledge.workspace.google.com/kb/how-to-share-a-form-with-external-users-000005913

On Mon, Nov 18, 2024 at 1:31 AM Bob Bryan <bbr...@garlandcountyhabitat.org> wrote:

I have a project using Google Sheets.  The project is in a Google Workplace environment. It is a Customer Donation Pickup Request system. the Scripts create a form allowing a customer to link to the form from a URL in our website. They are not members of our organization, but I want them to be able to enter a request. The scripts work fine when utilized by members of the organization but when the URL is accessed by someone outside the organization, they get unable to find file. The problem is that I would like them to be able to access and execute the form. Any help on getting around the autorization?

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/d03abfb7-236c-4f92-8b74-50c872c33a24n%40googlegroups.com.

--

My Site: https://www.sites.google.com/view/klaweb/

Passions: God, Family, Friends, Scripture, Data Management, Google Sheets + App Script, MS Access, Programing, sharing and much more.

[Bob Bryan]

Thank you for the quick response. I don't believe that it will work for me however. I forgot to mention that the form is not created using Google Forms, it is a form I designed using an HTML script.  The script is deployed as a web app with Execute set as me and Who Has Access set as any,  This is my deployment setup.

You received this message because you are subscribed to a topic in the Google Groups "Google Apps Script Community" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-apps-script-community/MsQmAC7Bt-A/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/CAFKgK%2BH5S200HKumUhxQV7SzFRntzbc30t_Y6x4HwGOtdshXRA%40mail.gmail.com.

--

Bob Bryan

Information Systems Consultant

bbr...@garlandcountyhabitat.org

 

    240 Hobson Avenue | Hot Springs, AR 71913

   Telephone 501-623-5600 M 480-299-1767

   www.garlandcountyhabitat.org

    

[Andrew Roberts]

Could their be a library that the script is using that has not been globally shared?

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/CABv5fLCVXsfZXiENQnPGH_%3DWXKOsb5Jv43oDFUJ3cgRJnsWG4A%40mail.gmail.com.

[Bob Bryan]

No, the project consists of several scripts written by me. The form is created by a script called index.html, the java script is called code.js. There are a few other scripts that do other tasks, but those are the two main ones. I have scopes authorization to allow the Gmail function. The form itself  allows a user to pick a date for donation pickup, enter user information, and what needs to be done. It then sends an email to the customer and the Dispatch Manager to confirm the request. Everything works correctly if I log into the organization. When I use the URL provided by the Deployment when I am not a member of the organization, I receive the following message.

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/CAN1QPJQ6dxb4hiHcj7fqW%3D%2BWQH3UGz3Apn6Ladgnwsp9ATihow%40mail.gmail.com.

[Andrew Roberts]

When anyone clicks on the web app link it'll send a GET request to the app. Does looking at the log of that give you any clues?

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/CABv5fLBZ%3Dak_LA8NK%2Bp26m3sFQMBXZkU_YytvNaEZErw9ULcCw%40mail.gmail.com.

[Bob Bryan]

I don't see any log entry that they tried to use form. I had another employee try to use the form with the URL I copied the from my PC. She got the Request Access prompt. Once the prompt is used that person can use the form any time after. If I give her the URL from the form itself she can access the form internally and externally to the Organization. But if i give her the URL created when the deployment is made she gets the Unable to Open file message. The problem comes back to letting a user access the form without having to be authorized. If we had to authorize each new user that would not allow us to use the system like we want to. We would like any customer to be able to input a request. Is this just one of those things Google thought we were not smart enough to do? (they may be right)

[Bob Bryan]

Additional info on the issue. One of the possibilities is that the Organization settings could be blocking access to an outside user. I talked to the System Administrator and she said to her knowledge that was not happening. She did some checking but could not find where a setting would be doing that. Any comments on where she could look to find out if the Organization is the cause?

[Keith Andersen]

Try deploying as new version. This will incorporate any new changes you made to the form or script.

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/1ee9b662-811e-46af-b429-44c6131e84b2n%40googlegroups.com.

[Andrew Roberts]

Could it be that the app is trying to access a doc that is only available to members of your organisation?

And if you are just sending emails it's best to use the MailApp rather than GMailApp (you mentioned GMail).

Failing all that you could share your script and we could take a look... 

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/CAFKgK%2BFD84gwEn1cKWgTdY7jGfLBnuL0ZSnd%2BHbyHjVckc75ig%40mail.gmail.com.

[Bob Bryan]

A little recap and clarification. The Google Sheet is located on a shared drive in our organization, the drive is shared to anyone. The sheet itself is also shared to anyone with the link. The scripts reside on the sheet and it is deployed with me as the owner and user access to any. The deployment has been done multiple times and so I don't believe that is the problem.  There are a total of 7 scripts stored on the sheet, but only two of them, the index.html and code.js run most of the project. They are respectively 929 and 287 lines of code. They  create an HTML form that allows input from outside customers to schedule a donation pickup. The email I referred to earlier is because after a donation is requested, the system sends an email to the customer and our Dispatch Manager to verify the request. When I work within the Workplace environment, everything in the system works correctly. If I have another employee try to use the system, it asks them to request access. Once they do that, then the system works. The problem arises when access to the form is attempted from outside the organization. That is when the Sorry Unable to Open File error appears. I can't have outside users  request access to the files every time a new request is made.

I checked the Organization settings for Workplace to see if it was restricting access in any way.

• Sharing outside of Garland County Habitat for Humanity, Inc.
• ON - Files owned by users or shared drives in garlandcountyhabitat.org can be shared outside of Garland County Habitat for Humanity, Inc.
• When sharing outside of Garland County Habitat for Humanity, Inc. is allowed, users in garlandcountyhabitat.org can make files and published web content visible to anyone with the link
• ON
• Access Checker
• Recipients only, suggested target audience, or public (no Google account required).
• Distributing content outside of Garland County Habitat for Humanity, Inc.
• Anyone
• "oauthScopes": [

      "https://www.googleapis.com/auth/drive",

      "https://www.googleapis.com/auth/spreadsheets",

      "https://www.googleapis.com/auth/gmail.send",

      "https://www.googleapis.com/auth/gmail.compose",

      "https://www.googleapis.com/auth/gmail.modify",

      "https://mail.google.com/",

      "https://www.googleapis.com/auth/script.container.ui",

      "https://www.googleapis.com/auth/script.send_mail"

As you can see, I have a somewhat elaborate set of scripts that work inside the cocoon of the Workplace. When it is attempted outside the workplace I get the error. My best guess is that it is an authorization issue, but I have granted every authorization I can think of and still does not work.

Any ideas on what may be overlooked. From all the documentation I have been able to see, this should be working.

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/CAFKgK%2BFD84gwEn1cKWgTdY7jGfLBnuL0ZSnd%2BHbyHjVckc75ig%40mail.gmail.com.

[Brent Guttmann]

You could put the webapp form in an iframe on your website.

[Brent Guttmann]

Related settings in admin are here:

   Apps



Google Workspace



Settings for Drive and Docs



Sharing settings

[Brent Guttmann]

Is the shared file in the root folder of the shared drive? I don't think you can share things in the root publicly, you would have to create a folder and then put the file in there and share it. Could be wrong on that one. But, I can tell you from personal experience that using an iframe to display an appscript served html form to collect form submissions from the public does work.

On Tuesday, November 19, 2024 at 9:24:27 PM UTC-5 Bob Bryan wrote:

A little recap and clarification. The Google Sheet is located on a shared drive in our organization, the drive is shared to anyone. The sheet itself is also shared to anyone with the link. The scripts reside on the sheet and it is deployed with me as the owner and user access to any. The deployment has been done multiple times and so I don't believe that is the problem.  There are a total of 7 scripts stored on the sheet, but only two of them, the index.html and code.js run most of the project. They are respectively 929 and 287 lines of code. They  create an HTML form that allows input from outside customers to schedule a donation pickup. The email I referred to earlier is because after a donation is requested, the system sends an email to the customer and our Dispatch Manager to verify the request. When I work within the Workplace environment, everything in the system works correctly. If I have another employee try to use the system, it asks them to request access. Once they do that, then the system works. The problem arises when access to the form is attempted from outside the organization. That is when the Sorry Unable to Open File error appears. I can't have outside users  request access to the files every time a new request is made.

I checked the Organization settings for Workplace to see if it was restricting access in any way.

[Bob Bryan]

I do appreciate the feedback so far. But alas, the problem is still there. In response to one of the suggestions, the sheet is in a folder in the shared drive. I am not sure about the iframe solution, my understanding is that it is used to open a window in a web page. That is not really what I am trying to do here. Our customers (or anyone) has the link, either by sending it to them in an email,  or by clicking on it from our web page. I would post the link here, but I really can't have people hitting it while I am still in development. I have enough issues already. I have verified the settings that were mentioned in a response and they are correct. So I think we keep circling back to somehow the form needs my authorization the first time someone runs it. That solution does not work for our purposes. The link is supposed to work so that when selected, a form screen will appear in the users browser. This form is created by the HTML script code. The user then inputs the information about the donation. When the submit button is pressed  the information is passed along to the spreadsheet. The link is created when the script is deployed as a webapp. There are several things happening in the background, but they are not something the user sees or would be aware of such as validation, scheduling, emailing. There is one situation I can't quite get a handle on.  If I deploy the app, I copy the URL that is created. I then open a second Edge browser outside of the Organization, not logged into a Google account,  and paste that URL. That is when I get the File Not Available error. However, when I make the deployment and click on the URL (because I am still in the Organization Workplace) the form opens correctly. If I then copy the URL in the address bar of the form, which is not the same URL created by the deployment, and then paste that URL in the new Edge browser, the form opens.I hope that I haven't overloaded this with too much information, but I wanted to give all the variables I see.

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/2cb26c93-9b74-419f-8f34-365f338ebc26n%40googlegroups.com.

[Garrett Howe]

"However, when I make the deployment and click on the URL (because I am still in the Organization Workplace) the form opens correctly. If I then copy the URL in the address bar of the form, which is not the same URL created by the deployment, and then paste that URL in the new Edge browser, the form opens."

The script's deployment URL is changing after you click on it from your account within your Org? If you could provide a before and after of the URL build that may help/provide more clarification. Can blur out the id.

If that's the case and the url that gets created works, why not use this url?

[Keith Andersen]

I really think the problem centers around your administrator who "thinks" they I have no restrictions on sharing outside the organization. I think that needs to be revisited. There is a setting that only the administrator has access to which is not set correctly. That seems to be the only logical conclusion.

https://support.google.com/a/answer/60781?hl=en

My website: https://sites.google.com/view/klaweb/
Passions: God, Family, Scriptures, Learning, Data Management, Google Sheets + App Script and much more!

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/4b11b916-9074-4307-9dc8-bff9c776862fn%40googlegroups.com.

[Garrett Howe]

Keith, if it's organizational restrictions causing the problem, he shouldn't be able to access the form from outside of his organization (i.e. using a new edge browser).

I think the problem lies closer to the source. This is why understanding the urls he is trying to use to access the web app might be helpful, especially considering they're somehow "not the same" after he visits which is not typical. 

[Bob Bryan]

Keith, I have revisited the sharing settings for the Organization. I also have admin privileges so I had a chance to personally verify the settings. According to the guidelines from Google, the scripts should be able to run form an outside source.

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/CAFKgK%2BHFTStCDBFCvZeqHScLk%3D0NrdJkqrx4TPsMcXq%2Buj9rBw%40mail.gmail.com.

[Keith Andersen]

You mentioned access from the form URL itself and the deployment URL. Where are you getting these two different URL's? There should only be one access point - the deployment URL. Unless your form is hosted elsewhere and not from app script.

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/CABv5fLBYmxexLnhm%3DUWXZFaUxjYcOO_48H%3DE6zcoNFX0zVLC0A%40mail.gmail.com.

[Bob Bryan]

Here is what I was talking about with the URL. I deployed the script and this is the URL to start the form.  https://script.google.com/macros/s/AKfycbwS6BMvewu7_gWtmJfwRKhv1gujJvEvSx4JhzMjtUbz/dev. The URL in the browser once the form is opened is   https://script.google.com/a/macros/garlandcountyhabitat.org/s/AKfycbwS6BMvewu7_gWtmJfwRKhv1gujJvEvSx4JhzMjtUbz/dev. As you can see, the URL when the form is actually opened contains the organization address. That is not present in the URL created by the deployment. And the form will not open with the deployment URL in a Edge browser not logged into the organization. If I launch the form while I am still logged into the organization, I can copy the URL in the browser, paste it into the same Edge browser, and the form will launch.

          

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/156985bc-e5d8-43a5-8b9d-e27d74814a13n%40googlegroups.com.

[Bob Bryan]

Those two links will show what I am talking about. One works, one does not'.

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/CAFKgK%2BEE728uCZRTg%2BqfAW1gnm%3DDwWMeN1u7ZP9YVE-9aUnA3w%40mail.gmail.com.

[Keith Andersen]

Suggestion.

In Claude AI describe your problem and copy and paste your code in Claude AI and ask it to analyse your code for conflicts. I have done this before. I suspect part of your code is in conflict. One thing could be the script accessing email or thr drive. Sometimes deployments need additional services like drive api added and initilized.

Just a thought

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/CABv5fLD4MTzBVBNULKxXBwq4TvwdmTW2upOi-73c47uZpDoRJA%40mail.gmail.com.

[Brent Guttmann]

Deployed webapp's will have an /exec url ending, not /dev

Just to be 100% sure, you are deploying as a webapp, you check execute as me, and access is set to everyone? That should end up giving you an /exec url.

I would also make sure that you have enabled / added any 'services' that you may require for the script to run within the left menu, under code and libraries.

May want to also ensure you have the below setXFrameOptionsMode line in your code...

function doGet(e) {
    // Create and serve HTML content
    var output = HtmlService.createHtmlOutputFromFile('your html file name without the .html extension')
    .setTitle('your title here')
    .setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);

    return output;
}

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/CABv5fLBdWWvd7je46pWbYJ--smEYRe74KDQuaY0r5LUFioUnnA%40mail.gmail.com.

[Brent Guttmann]

I made an example form which can be viewed here:

https://script.google.com/macros/s/AKfycbwct7ZiOzdvkzkpz4JUX1_Qr8FhQx-1MHsMS3dyPKT99wYKThWuyKf4mDOa338-r5I10w/exec

and you can copy the sheet and its code from here:

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

To determine if you are running into a permissions issue or a coding issue, you can copy that script along with the html and deploy it, and see if it works.

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/CABv5fLD4MTzBVBNULKxXBwq4TvwdmTW2upOi-73c47uZpDoRJA%40mail.gmail.com.

[Andrew Roberts]

That's works for me either logged into my account, or from an incognito window. So it points back to admin settings.

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/CAMBjqjnFgf9GD%2BGy7P-Ar9dPYppeCsRZLexMKEgNke%3DvrM2gGA%40mail.gmail.com.

[Garrett Howe]

Your web app seems to be deployed in developer mode which doesn't allow users to view it without edit access. 

When you want to make your script publicly accessible, make sure you click 'New Deployment', not 'Test Deployment'. Making a new deployment should fix this issue. Just remember to set the access option to 'Anyone'.

https://stackoverflow.com/questions/62045820/dev-vs-exec-documentation-and-mishandling
https://developers.google.com/apps-script/guides/web#deploying_a_script_as_a_web_app  (Ctrl + F this to find 'Test deployments')

[Bob Bryan]

Traveling today, will continue troubleshooting when I get a chance to login.

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/e0fef185-9b62-47c7-ab2e-2dd6e86ad587n%40googlegroups.com.

[Bob Bryan]

I had a chance to troubleshoot a few of the suggestions. I made sure that I used the URL  created when I deployed. It had an exec at the end. If I copied that URL and went to another browser (Edge in this case}. That URL came up with the unable to open file error. It dawned on me when I deployed the script the URL being created is for the script itself. Selecting that URL is running the script which creates the Form. The form itself has a different URL, one that includes the company name. That URL will run in another browser.. That is what I think is happening, but I will do a little further checking.

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/f61cc585-17e3-4845-88fa-ad034966d0edn%40googlegroups.com.

[Brent Guttmann]

The script that you are using as the webapp should be returning the html code for the webpage that has the form. Did you look at the example I provided? If you copy that sheet, and then deploy it yourself, does it work?

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/CABv5fLAMi656Kxs53JO_H%3DMkap9fRTSnbsMMZ%2B%2BXpxR9QmUEQA%40mail.gmail.com.

[Bob Bryan]

The sheet you provided I made a copy of since it is read only. I deployed the script. I had the same result. If i run the URL in the login I used to create it, it works fine. If I try from another browser, I get the unable to find file error.

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/CAMBjqjnFgf9GD%2BGy7P-Ar9dPYppeCsRZLexMKEgNke%3DvrM2gGA%40mail.gmail.com.

[Bob Bryan]

I am back at the troubleshooting on this issue. I have checked the sharing options for the organization and they are correct. The sheet itself is shared to anyone with a link. The URL being created has the exe link. I still come up with unable to find file at this time.

[Keith Andersen]

Bob, I had an issue once very similar. A function name had somehow become corrupted and although perfectly correct in syntax and everything - would not work. I redid naming it differently and suddenly - poof- it worked.

Just and idea.

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/8bc1b13e-dba1-41e4-b9f3-db395ed0a2e3n%40googlegroups.com.

[Bob Bryan]

Keith;   I have several functions in the scripts. If a function was corrupted, wouldn't that show up when I ran the project. As I have stated before, I can run the project within the organization. Everything works fine. The form appears, all of the data entry points, calendars, formatting is correct. The form updates the spreadsheet pages correctly. The emails all are sent to the appropriate recipients. The problem only exists outside of the organization. This leads me back to a Google Workspace issue. I have all of the sharing parameters set to allow outside users to use the URL to call up the form. That is where the problem begins. I do appreciate all of the suggestions so far, I just have not been able to resolve the issue.

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/CAFKgK%2BG1kVcOeXhmoAP1AGr01zvt_JW011Q4nnBFOfC-3atiNg%40mail.gmail.com.

[Edward Wu]

Sorry if this has already been brought up. Are you sure the *web app* deployment permissions are set correctly?

This makes me think the problem might be there: "The scripts work fine when utilized by members of the organization but when the URL is accessed by someone outside the organization, they get unable to find file."

I believe the only way to give access to "anyone" (with or without a Google account) is to set the script to "Execute as me".

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/8bc1b13e-dba1-41e4-b9f3-db395ed0a2e3n%40googlegroups.com.

[George Ghanem]

Bob,

Have you thought of just using a Google Form rather than an HTML form? It would make deployment easier in this case.

I would also check your deployment settings on the script as well as on the receiving software that handles the HTML form submission.

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/8bc1b13e-dba1-41e4-b9f3-db395ed0a2e3n%40googlegroups.com.

[Brent Guttmann]

Has the sheet/script been copied by anyone? For some reason I have noticed that if you deploy a webapp and then someone else copies it, with I assume the same permissions set in the files, it causes permissions problems.

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/CABv5fLCyLA63%3DOxQCS-1BZha5DF8MkBLwithP2%3DK8G%3D45a%3D7ew%40mail.gmail.com.

[Bob Bryan]

I am not sure I totally understand how I got to this point, but here goes. I have investigated all of the suggestions so far and it seems that none of them corrected the issue. As far as the suggestion for using Google Forms, it really does not work for the way we wanted to structure the application. Now after all of that, I went back to something I had been checking earlier. When I deploy the script as a web app, the URL created works in the organization environment, but not as an outside user. This was what I had been having an issue. Earlier I had mentioned that the actual form itself has a different URL than the one created when I did the deployment. As far as I could figure the deployed URL is for running the scripts. One of the functions of the scripts is to create the form. It has a different URL. I have copied the URL created when the form is on the screen, and it seems to run outside the Workplace environment. I would appreciate it if you could test my URL outside of my environment and let me know the result. The short form url is https://bit.ly/4imIbb1 . I would appreciate the feedback.

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/CAMBjqjniA6TF45%3DDpeqLzUUwToSwrtzb4DrEHvLVZyKsb-6i0Q%40mail.gmail.com.

[Andrew Roberts]

That works fine for me.

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/CABv5fLBXAn4TENWRyvLuvQZ7qOnR8BBt-mC6RfonVRWrDp4-rw%40mail.gmail.com.

[George Ghanem]

Yes, that link works. 

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/CAN1QPJRSa-Md65dt_90fjJqWq618h6sod8Xgo0_Te3NROYJq3A%40mail.gmail.com.