Google Apps Script 403: The caller does not have permission

Task

I'm creating a Apps Script that adds new Feedback row to the containing Google Sheet whenever the API is invoked.

Setup.

1. I have a Google Sheet named Feedback
2. Created the Apps Script (Code.gs - attached below)
3. Deployed it as API Executable, and copied the deployment URL
4. I have a custom backend server written in Dart. This server will be the one calling the Apps Script to add new feedback.
5. Enabled Google Sheets API, Google Apps Script API
6. Created a new Service Account. Shared the Feedback Google Sheet to the particular service account's email as editor.

Now after all this, I ran the API, but failed with the following message:

Response: {
  "error": {
    "code": 403,
    "message": "The caller does not have permission",
    "status": "PERMISSION_DENIED"
  }
}

How do I solve this? Necessary codes are given below.

Code.gs

function doPost(e) {
  try {
    // Parse the incoming data
    const data = JSON.parse(e.postData.contents);
    
    // Validate required fields
    const requiredFields = ['date', 'name', 'email', 'rating', 'message'];
    for (const field of requiredFields) {
      if (!data[field]) {
        throw new Error(`Missing required field: ${field}`);
      }
    }

    // Get the spreadsheet and sheet
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    const sheet = ss.getSheetByName('Feedback');
    
    // Get the last row to calculate new index
    const lastRow = sheet.getLastRow();
    const newIndex = lastRow === 1 ? 1 : sheet.getRange(lastRow, 1).getValue() + 1;
    
    // Prepare the new row data
    const newRow = [
      newIndex,
      data.date,
      data.name,
      data.email,
      data.rating,
      data.message
    ];
    
    // Insert the new row
    sheet.appendRow(newRow);
    
    // Apply conditional formatting for the rating column (column E)
    const ratingRange = sheet.getRange(lastRow + 1, 5); // Column E for the new row
    
    if (data.rating >= 4) {
      ratingRange.setBackground('#93C47D'); // Green
    } else if (data.rating >= 3) {
      ratingRange.setBackground('#FFD966'); // Yellow
    } else {
      ratingRange.setBackground('#E06666'); // Red
    }
    
    return ContentService.createTextOutput(JSON.stringify({
      success: true,
      message: 'Feedback added successfully',
      rowNumber: lastRow + 1
    })).setMimeType(ContentService.MimeType.JSON);
    
  } catch (error) {
    return ContentService.createTextOutput(JSON.stringify({
      success: false,
      error: error.message
    })).setMimeType(ContentService.MimeType.JSON);
  }
}

appsscript.json

{
  "timeZone": "Asia/Kolkata",
  "dependencies": {
    "enabledAdvancedServices": [
      {
        "userSymbol": "Sheets",
        "version": "v4",
        "serviceId": "sheets"
      }
    ]
  },
  "exceptionLogging": "STACKDRIVER",
  "runtimeVersion": "V8",
  "executionApi": {
    "access": "ANYONE"
  },
  "oauthScopes": [
    "https://www.googleapis.com/auth/spreadsheets"
  ]
}

api.dart

import 'package:googleapis_auth/auth_io.dart';

Future<void> sendFeedback(Feedback feedback) async {
  final serviceAccountJson = File('feedback-account.json').readAsStringSync();
  final credentials = ServiceAccountCredentials.fromJson(serviceAccountJson);

  final client = await clientViaServiceAccount(
    credentials,
    [
      'https://www.googleapis.com/auth/spreadsheets',
    ],
  );

  final url = "https://my-apps-script-deployment-url";

  final bodyJson = jsonEncode({
    "function": "doPost",
    "parameters": [
      {
        'date': getFormattedDate(feedback.date),
        'name': feedback.name,
        'email': feedback.email,
        'rating': feedback.rating,
        'message': feedback.message,
      },
    ],
  });

  final response = await client.post(
    Uri.parse(url),
    headers: {'Content-Type': 'application/json'},
    body: bodyJson,
  );

  print('Response: ${response.body}');
}

To run a doPost function from a Google Apps Script project using a POST HTTP request, the project should be deployed as a web application and set the deployment settings according to your needs, i.e., Run as Me and Allow anyone to execute it.

If you set the deployment to run as the user executing it or only to allow signed-in users or users from your domain, you should include the OAuth credentials in the POST request.

Note: doPost is a reserved function name. Ref. https://developers.google.com/apps-script/guides/web

Reference

• https://developers.google.com/apps-script/guides/web