I’m currently working on a Google Sheets-based database management solution to maintain daily records with input from different users. I have a simple Google Form that others fill out, and I use Google Apps Script to save the form data to a separate sheet in the same Google Sheets file.
Here’s my setup:
I collect data via a Google Form on an Android tablet.
The form saves its data to a common database in Google Sheets.
I use Google Apps Script to process and save the data to a separate tab, organized by days.
To ensure data integrity, certain cells in the Google Sheets database are protected from editing by users.
Problem:
When I share the Google Form with different users, they can’t submit it. An error occurs when the Google Apps Script runs, indicating that the users don’t have permission to edit the protected form. This suggests that the permissions set on the protected cells may interfere with the script execution when the form is submitted by multiple users.
What I’ve Tried:
I checked the Google Apps Script for any issues, but it appears to work fine when executed by an account with editing permissions.
I tried adjusting the cell protections to allow specific users to edit the form, but the error persists.
My Questions:
- How can I allow multiple users to fill and submit the Google Form
without encountering permission issues in Google Sheets? - Is there a way to grant script execution permissions without exposing the
protected cells for editing? - Are there best practices for managing Google Forms with Google Sheets to avoid such
permission-related errors?