Write only permission to Data Sheets connected to app-scripts

[Damian Miklojachak]

Hello, 

I'm pretty new to google app-scripts and it'll show here in a minute. I've made several standalone web-apps with intake forms and table views into google sheets data. At the moment, for a user to submit a form they need edit permissions on the sheet being written to. Same goes for viewing data in the tables I've made. I don't want users to have access to the actual documents, just the front end of the form with all data showing and forms able to post to sheets.

How can I do this?

[CBMServices Web]

Just use Google Forms to collect the input. The data from forms can be saved to a spreadsheet that only you have access to.

--
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 on the web visit https://groups.google.com/d/msgid/google-apps-script-community/5d8e6d72-8fce-465c-a405-e7d278a720bf%40googlegroups.com.

[Damian Miklojachak]

I'm not sure if this will let me have my cake and eat it too, but I am curious...

First, let me confirm and try to be as clear as possible with the original inquiry.

A handful of scripts act as semi-full web blown web apps. The front-ends provide views into data-sets from multiple sheets. Some with middle-ware like functions automating the workflow it's designed to support. Some have multiple forms.

Would it be just as simple to make the same thing in a google form without being limited to google form's basic functionality?

I've seen how you can extend forms with scripts, but never beyond the basic intake form. I can't think of a way to build around it's limitations, the theme, or the "includes" I don't want. These forms are to be used in a med health environment and I don't want google's terms of service/ report abuse links included in the iframe alongside "this was built using google forms". 

Form scripts don't get access to the form and fields on the back-end either, it can only extend options.

Also using Google Cloud IDs "Only" (no-gsuite) for some of the employees, meaning google forms might not be possible. Cloud IDs don't get access to G-Suite, but they can access my web-apps.

Most file sharing/active directories provide write only access to docs. This is not an option with google.

Anyway, am I missing something obvious? -Cause I was thinking this problem had more to do with something in the console platform.

[CBM Services]

If google forms are not complex enough for your application, then some other solution would be needed.

There is no need for users of the forms to have Gsuite or even a gmail account to fill a form, unless they need to upload files via the google forms.

You can define google web apps that feed the data collected into a spreadsheet without requiring the users to have access to the spreadsheet itself.

Perhaps if you can show an example if what one of your widgets do, can then give you better opinion on direction.

---

From: Damian Miklojachak
Sent: ‎2019-‎12-‎08 11:26 PM
To: Google Apps Script Community
Subject: [Apps-Script] Re: Write only permission to Data Sheets connected toapp-scripts

--
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 on the web visit https://groups.google.com/d/msgid/google-apps-script-community/da6378e1-66f5-469f-a68e-563ccd22d211%40googlegroups.com.

[Efficient Small Business]

Hey Damian,

It sounds like your web app is executing as "user accessing the web app" which is creating your problem. Unless your web app is accessing the user's information, you can execute the web app as "me", and then your users will not need edit permissions.

You can change this setting by going to the Script Editor > Publish > Deploy as web app... > Execute the app as...

It will then be necessary for you to run the script once to allow permissions for yourself.

Does that resolve your issue?

Micah

[Damian Miklojachak]

Sure, but I believe the root of the problem is simply having to enable edit or read privileges on any data being pulled from sheets and forms saving to sheets.

Here is an example of the main/ home view on one of the apps. 

I'm not showing everything in the following pics for HIPAA reasons. I don't want to take the time to replace the data and I don't think it'll matter in getting the point across, I hope. 

Anyway, what you see above is the home view of a standalone-app. In the image, to the left, you'll see some percentages being fed from other sheets in real time. The boxes you see have multiple options for entries and logs to previous entries. The entire thing works mostly within a single page, with views being rendered instead of pages for most of the options.

A form... 

When "New Entry" is clicked, forms appear to the right of the data and returns to the original view when completed. And if you are asking, why in this particular form are you having a users confirm information available in the data to the left? - It's because the Behavior Analyst Certification Board says we have to. Which is why I built this thing to automate and gather all the data they need in one place to maximize workflow.

Also, the rest of the forms have sensitive data propagating within the fields and I don't think showing them will make any sort of difference in the direction I must take.

I hope this enough. The app works, the forms work, I just don't want to assign edit privileges to the sheets acting as the database. This means every technician and therapist with a gsuite can see data from others as well.

The scripts must remain complaint by allowing access to only domain users, both g suite and cloud IDs. 

I was hoping there would be a service via the console and API to make the app and all the sheets binding open to a domian user or organizational unit.

To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-community+unsub...@googlegroups.com.

[Efficient Small Business]

As noted in my comment above, if you change how the app is executed, you can continue to run your app normally without enabling edit or read privileges on any Google Sheets data.

[Damian Miklojachak]

Sorry, I didn't see this until I made the post. It works! 

Damn am I glad it was just that simple. 

Thank you.

[Efficient Small Business]

Happy to help!

[CBM Services]

Using Google Web Apps and Scripts for a HIPPA compliant application is going to be quite hard to do. GAS does not have group access priviledges support or provides any flexibility for access controls unless everyone is a GSuite user and you define your groups via the console for access privilidges.

Trying to enable this outsuite the GSuite access privildges controls will be difficult. You would need to manually track who has access to what piece of data which is tedious.

Check the other reply you got on this thread. ReDeploy your web apps to run as you, you will then no longer need to give access to the spreadsheets themselves to the users. But you have now opened the barn door wide open by giving users access to all the data. So you will need to define another mechanism to control data access.

I would consider requiring everyone to be a GSuite user..

---

From: Damian Miklojachak
Sent: ‎2019-‎12-‎09 8:18 AM

To: Google Apps Script Community

Subject: Re: [Apps-Script] Re: Write only permission to Data Sheets connectedtoapp-scripts

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 on the web visit https://groups.google.com/d/msgid/google-apps-script-community/d53d5162-91ee-4e7b-95f7-0a590682c423%40googlegroups.com.

[Damian Miklojachak]

It's not all that bad. They have to be part of the domain and logged in to get access making the audit trail is easy-peasy. Their username is automatically stamped to everything they turn in as well.

Making a G-suite user out of everyone is out of the question though. Homie, not only would it triple our costs, it would make compliance far more work than it needs to be. Roughly 95% of G-Suite is unnecessary for our users, so having to keep track of every user's docs/sheets/email is only creating more work. I'd rather save that dough, make 5 apps with everything they will ever need and track only that.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/d53d5162-91ee-4e7b-95f7-0a590682c423%40googlegroups.com.