Access control issues with Apps Script and linked spreadsheets

[ClimbingLion]

We built a system using multiple connected Google Sheets where some sheets work as input forms and others store all the data like a database.

Here’s our Apps Script code for the input forms:

// Clear form fields
function clearFields() {
  var currentSheet = SpreadsheetApp.getActiveSpreadsheet();
  var inputForm = currentSheet.getSheetByName("Employee Form");

  var cellsToClear = ["D2", "D3", "D4", "D5", "D6", "D7", "D10"];
  for (var j=0; j<cellsToClear.length; j++){
    inputForm.getRange(cellsToClear[j]).clearContent();
  }
}

// Save form data
function saveData(){
  var currentSheet = SpreadsheetApp.getActiveSpreadsheet();
  var inputForm = currentSheet.getSheetByName("Employee Form");
  var targetSheet = SpreadsheetApp.openById('your-database-sheet-id-here')
  var dataStorage = targetSheet.getSheetByName("Employee-Records");

  var recordData = [[inputForm.getRange("D2").getValue(),
                    inputForm.getRange("D3").getValue(),
                    inputForm.getRange("D4").getValue(),
                    inputForm.getRange("D5").getValue(),
                    inputForm.getRange("D6").getValue(),
                    inputForm.getRange("D7").getValue()]];

  dataStorage.getRange(dataStorage.getLastRow()+1,1,1,6).setValues(recordData);
  clearFields();
}

// Search function
var SEARCH_COLUMN = 0;
function searchRecord() {
  var currentSheet = SpreadsheetApp.getActiveSpreadsheet();
  var inputForm = currentSheet.getSheetByName("Employee Form");
  var searchValue = inputForm.getRange("D10").getValue();
  var targetSheet = SpreadsheetApp.openById('your-database-sheet-id-here')
  var allRecords = targetSheet.getSheetByName("Employee-Records").getDataRange().getValues();
  
  for (var k = 0; k < allRecords.length; k++) {
    var currentRow = allRecords[k];
    if (currentRow[SEARCH_COLUMN] == searchValue) {
      inputForm.getRange("D2").setValue(currentRow[0]);
      inputForm.getRange("D3").setValue(currentRow[1]);
      inputForm.getRange("D4").setValue(currentRow[2]);
      inputForm.getRange("D5").setValue(currentRow[3]);
      inputForm.getRange("D6").setValue(currentRow[4]);
      inputForm.getRange("D7").setValue(currentRow[5]);
    }
  }
}

The problem: When we try to restrict access to the database sheets but allow people to use the input forms, we get this error message:

“You do not have permission to access the requested document”

How can we let users work with the input forms while keeping the database sheets private from them?

[nina.k]

Those permission errors are classic Google Sheets limitations. Apps Script uses whatever permissions the user has, so if they can’t access the database sheet, Google blocks the script.

I’ve hit this wall tons of times. Web app solutions work but turn into maintenance nightmares - you’re building HTML interfaces, handling validation, managing sessions. Gets messy fast.

What actually fixed this for me was switching to Latenode. Connect your Google Sheets as data sources and create workflows that run with your credentials, not theirs. Users just fill out simple forms (Google Forms work great), and Latenode handles the database stuff behind the scenes.

Best part? Set it up once and forget it. No permission headaches, no custom web apps to babysit. Built a similar employee data system in 30 minutes - form gets submitted, Latenode workflow kicks in, data gets processed and stored in the restricted sheets automatically.

Users get clean forms, your data stays locked down, and you’re done fighting Apps Script permissions.

Latenode - Create AI Agents & Autonomous Workflows | No-Code &...

[Mike71]

you could also set up an installable trigger instead of a simple one. create an onEdit trigger that runs under your account, not theirs. when someone fills out the form, it’ll fire with your permissions and handle writing to the database. it’s a bit of a hack, but it works without having to rebuild everything as a web app.

[livbrown]

Had this exact problem building an inventory system for our office. Apps Script uses the user’s permissions, so when they hit SpreadsheetApp.openById(), Google checks if they can access the database sheet. Since you’ve locked them out, it crashes right there. I ditched that approach entirely. Built a web app using HtmlService instead - runs under my credentials but gives users a clean form interface. They submit data through the web app, my script handles all the database stuff behind the scenes. Users never touch sheets they can’t access, but they get everything they need through the interface. More work upfront than what you’re doing now, but it actually separates user access from data management properly.

[avamtz]

had a similar problem too. using a service account or a web app that runs under your account can fix it. if the users can’t access the db sheet, the script won’t work. consider deploying it as a web app with “execute as me” option.

[sofiag]

The Problem: You’re encountering “You do not have permission to access the requested document” errors when using Apps Script to interact with Google Sheets. Your input forms work correctly, but the Apps Script code fails because it tries to access database sheets that the users lack permission to view. You want a solution to allow users to submit data through input forms while maintaining the privacy and restricted access of the database sheets.

Understanding the “Why” (The Root Cause):

The core issue is that Google Apps Script executes with the user’s permissions, not the script owner’s. When your script uses SpreadsheetApp.openById() to access the database sheet, Google checks if the user submitting the form has permission to access that sheet. Since you’ve restricted access to the database sheets, the script fails with a permission error for any user other than the script owner. This means the current approach fundamentally violates the desired security model.

Step-by-Step Guide:

Step 1: Create a Web App to Separate User Access and Data Management.

This is the most robust and secure solution. A web app runs under your account’s permissions, not the user’s. Users interact with a form provided by the web app, and your script handles all interactions with the protected database sheets.

1. Create a New Web App in Google Apps Script: In your Google Sheet’s script editor, go to “Deploy” → “New deployment”. Choose “Web app” as the type.
2. Configure the Web App: Set the “Execute the app as:” option to “Me” (your account). This ensures the web app runs with your permissions. Choose “Who has access to the app” as “Anyone, even anonymous”. Click “Deploy”. Copy the web app URL.
3. Create a Simple HTML Form: You’ll need a basic HTML form that collects the data from the users. This can be embedded in a web app directly or hosted separately. Here’s a rudimentary example:

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
    <h1>Employee Form</h1>
    <form id="employeeForm">
      <!-- Add your form fields here -->
      <input type="text" name="field1" placeholder="Field 1"><br>
      <input type="text" name="field2" placeholder="Field 2"><br>
      <button type="submit">Submit</button>
    </form>
    <script>
      document.getElementById('employeeForm').addEventListener('submit', function(e) {
        e.preventDefault();
        const formData = new FormData(this);
        fetch(google.script.run.withSuccessHandler(function(response){
            window.location.href = 'confirmation.html';
        }).withFailureHandler(function(error){
            alert('Error submitting data!');
        }).getData(formData), {
            method: 'post'
          })
          .then(response => response.json())
          .then(data => {
            // Handle success response.
          })
          .catch(error => {
            console.error('Error:', error);
          });
      });
    </script>
  </body>
</html>

4. Modify your Apps Script: Replace your existing saveData() function with one that handles form submissions from the web app. This function will receive the form data and write it to your database sheet using your permissions. Example (adjust based on your form and data structure):

function doGet(e) {
  return HtmlService.createTemplateFromFile('index').evaluate();
}

function doPost(e) {
  const data = e.parameter;
  // Access your database sheet here using your account's credentials.
  // ...
  return ContentService.createTextOutput(JSON.stringify({success:true}));
}

function getData(formData) {
  //Process the formData and save it to your database
    const data = {};
    formData.forEach((value, key) => { data[key] = value; });
    var currentSheet = SpreadsheetApp.getActiveSpreadsheet();
    var targetSheet = currentSheet.getSheetByName("Employee-Records");
    targetSheet.appendRow([data.field1, data.field2, /*...*/]);
    return {success: true};
}

5. Deploy the Web App: Redeploy your web app to update it with these changes.

Step 2: Test Thoroughly.

Submit test data through the web app to verify that the data is correctly written to your protected database sheet.

Common Pitfalls & What to Check Next:

• Security: Always review your web app’s security settings to ensure only authorized users can access and manipulate your data.
• Error Handling: Implement robust error handling in your Apps Script to catch and report issues during data submission and database interaction.
• Data Validation: Add data validation to your form to prevent incorrect or malicious data from entering your system.
• Scalability: Consider the scalability of your web app as the number of users and data entries increase.

Still running into issues? Share your (sanitized) config files, the exact command you ran, and any other relevant details. The community is here to help!