1

I have set up a Google Apps Script that validates data that is entered into various Google Sheets files. I am looking for a way to allow users to access those Google Sheets files without making them sign in manually.

There are various onEdit() triggers set up that monitor these Sheets files and apply formatting to cells when data input errors occur. I'm doing validation with Google Apps Scripts because the errors are determined through non-straightforward business logic which makes Sheets' built-in data validation insufficient. The triggers are set up by a standalone Google Apps Script project. This setup means I cannot simply share a link to the Google Sheets file and have people edit it anonymously and have the data checked and cells formatted, because the scripts require more permissions than are necessary for anonymous editing.

I've looked briefly at some Google Auth API docs, but I'm not a security or web dev by trade so it's a bit confusing.

I'm looking for a solution where the user could click on a link to a Sheets file and automatically be redirected to the Sheets file and be signed in to a Google account that is managed by me, and which already has permission to access that Sheets file (this way they won't be editing these files with their personal accounts). The link would be on a Drupal site that users would have already signed into using Drupal's built-in sign-in functionality, in case that makes a difference.

2 Answers 2

1

Only users with edit access to the spreadsheet are able to fire through their actions on edit / on change triggers.

The alternative is to create a web application setting it to run as the script owner by anyone. The overview about how to create such web application are on https://developers.google.com/apps-script/guides/web.

1

Turns out I can check "Anyone with the link can edit" in the Sharing menu on Google Sheets and anonymous edits do in fact trigger the scripts successfully. The only thing that doesn't work in my case are toast messages (they don't show up for anonymous users) and the executions don't get tracked in the dashboard, which makes it impossible to accurately track your execution time.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.