Google Sheet脚本调用Web App无响应，学校订房系统开发求助

Hey there! Let's dig into why your UrlFetchApp call to the Web App isn't working, even though direct access works perfectly. Here are the key fixes and debugging steps to try:

1. Double-Check Web App Deployment Settings #

First, confirm your Web App's deployment config—this is the most common culprit:

• When deploying, ensure Execute as: Me is selected (this ensures the code runs with your permissions, which is exactly what you need to edit the protected sheet).
• For Who has access to the app, choose:
  • Anyone within your school domain (safer for a school system) or Anyone, even anonymous (if external access isn't a concern).
  • If you set it to Only myself, your UrlFetchApp call must include an OAuth token to authenticate (we'll add that next).

2. Update the UrlFetchApp Call for Debugging & Auth #

Your current UrlFetchApp.fetch only uses the URL, which can fail silently. Modify it to capture errors and add authentication:

function Clickevent(e) {
  const webAppUrl = "https://script.google.com/a/st-swithuns.portsmouth.sch.uk/macros/s/AKfycbwEsUsHJ2_1VwWqu1nTh1ux2FkEwP5ul2OXBc2pTQs8Z1eV_ls/exec";
  
  try {
    const options = {
      muteHttpExceptions: true, // Prevents silent failures and lets you see error details
      headers: {
        "Authorization": "Bearer " + ScriptApp.getOAuthToken() // Passes your auth token
      }
    };
    
    const response = UrlFetchApp.fetch(webAppUrl, options);
    const responseText = response.getContentText();
    const responseCode = response.getResponseCode();
    
    // Debug info to see what's happening
    Logger.log("Response Code: " + responseCode);
    Logger.log("Response Text: " + responseText);
    Browser.msgBox("Web App Response: " + responseCode + " - " + responseText);
    
    // Rest of your existing code...
  } catch (error) {
    Browser.msgBox("Error calling Web App: " + error.toString());
    Logger.log("Error: " + error.toString());
    return;
  }
  
  // ... rest of your Clickevent code
}

Why this helps: #

• muteHttpExceptions: true ensures you get clear HTTP errors (like 403 Forbidden or 500 Server Error) instead of the call failing without feedback.
• The Authorization header passes your OAuth token, which is required if your Web App is restricted to your account or domain.

3. Verify Spreadsheet Permissions #

Make sure your account has edit access to the spreadsheet you're modifying in doGet (ID: 1eOqf1Cofkz9u0Zg2UIb9Ana3VQC5PN0aZqWQkip2d2w). Since the Web App runs as you, your account needs full permissions to edit that sheet.

4. Debug the Web App Execution #

Add logging to your doGet function to confirm it's being triggered:

function doGet(e) {
  Logger.log("doGet was called successfully!"); // Log that the function ran
  const spreadsheet = SpreadsheetApp.openById("1eOqf1Cofkz9u0Zg2UIb9Ana3VQC5PN0aZqWQkip2d2w").getSheetByName('Bookings').getRange('A1').setValue("hihihihihihi");
  Logger.log("Spreadsheet updated successfully!"); // Log that the sheet was modified
  return ContentService.createTextOutput("Success");
}

Then check the Web App's execution logs:

1. Open your Google Script project for the Web App
2. Click View > Executions to see if doGet was triggered and if any errors occurred.

Quick Side Note #

I see you're using UiApp, which is deprecated—just a heads up that it might have odd behavior with newer Apps Script features, but you mentioned ignoring that for now, so we'll stick to solving the immediate issue!

Try these steps, and the debug messages will point you straight to the problem—whether it's a permission glitch, deployment setting, or something else. Good luck with your room booking system!

内容的提问来源于stack exchange，提问作者Adam D'Arcy-Lancaster