嵌入Google Apps Script至Google Sites遇权限要求错误求助

我有一个Google网站，需要从Google表单的响应表格中拉取数据。尝试嵌入Google Apps Script时，出现「Access required」错误（截图：）。
代码在新标签页预览时能成功获取并显示数据（截图：），但因权限问题无法验证HTML嵌入后的正常渲染。我已经将脚本权限设置为组织内所有人可访问，甚至开放了所有权限，但问题仍未解决。

可用的Apps Script代码 #

function doGet() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var data = sheet.getDataRange().getValues();
  
  // Remove header row
  data.shift();
  
  // Transform data into structured JSON
  var activities = data.map(function(row) {
    return {
      timestamp: row[0],
      name: row[1],
      indoorOutdoor: row[2],
      resourcesRequired: row[3],
      instructions: row[4],
      pictures: row[5],
      videoLink: row[6],
      riskAssessment: row[7],
      numberOfChildren: row[8],
      activityType: row[9],
      seasons: row[10],
      NameofCreator : row [11]
    };
  });
  console.log(activities);
  return ContentService.createTextOutput(JSON.stringify(activities))
    .setMimeType(ContentService.MimeType.JSON);
}

预期使用的HTML代码 #

<!DOCTYPE html>
<html>
<head>
  <style>
    .activity-item { 
      cursor: pointer; 
      margin: 10px 0; 
      border: 1px solid #ddd; 
    }
    .activity-details { 
      display: none; 
      background-color: #f9f9f9; 
      padding: 15px; 
    }
  </style>
</head>
<body>
   <title>Activities List</title>

  <div id="activities-container"></div>

  <script>
    const SCRIPT_URL = 'HIDDEN';

    async function fetchActivities() {
      try {
        const response = await fetch(SCRIPT_URL);
        const activities = await response.json();
        displayActivities(activities);
      } catch (error) {
        console.error('Error fetching activities:', error);
      }
    }

    function displayActivities(activities) {
      const container = document.getElementById('activities-container');
      
      activities.forEach(activity => {
        const activityElement = document.createElement('div');
        activityElement.classList.add('activity-item');
        activityElement.innerHTML = `
          <h3>${activity.name}</h3>
          <div class="activity-details">
            <p><strong>Type:</strong> ${activity.indoorOutdoor}</p>
            <p><strong>Resources:</strong> ${activity.resourcesRequired}</p>
            <p><strong>Instructions:</strong> ${activity.instructions}</p>
            <p><strong>Number of Children:</strong> ${activity.numberOfChildren}</p>
            <p><strong>Activity Type:</strong> ${activity.activityType}</p>
            <p><strong>Seasons:</strong> ${activity.seasons}</p>
            <p><strong>Pictures:</strong> ${activity.pictures}</p>
            <p><strong>Video Link:</strong> ${activity.videoLink}</p>
            <p><strong>Risk Assessment:</strong> ${activity.riskAssessment}</p>
          </div>
        `;

        activityElement.querySelector('h3').addEventListener('click', () => {
          const details = activityElement.querySelector('.activity-details');
          details.style.display = details.style.display === 'none' ? 'block' : 'none';
        });

        container.appendChild(activityElement);
      });
    }

    fetchActivities();
  </script>
</body>
</html>

1. 检查脚本部署权限细节 #

• 重新部署脚本时，确认谁有访问权限设置为「任何人，甚至匿名」，避免仅限制组织内访问；执行权限选择「以我（脚本所有者）的身份运行」，让访问者无需额外权限即可获取数据。
• 确保部署类型为「Web应用」，而非其他类型。

2. 整合HTML与Apps Script规避跨域限制 #

Google网站的CSP（内容安全策略）会拦截跨域fetch请求，直接将HTML与脚本整合可解决这个问题：

修改后的Apps Script代码

function doGet() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var data = sheet.getDataRange().getValues();
  data.shift();
  
  var activities = data.map(function(row) {
    return {
      timestamp: row[0],
      name: row[1],
      indoorOutdoor: row[2],
      resourcesRequired: row[3],
      instructions: row[4],
      pictures: row[5],
      videoLink: row[6],
      riskAssessment: row[7],
      numberOfChildren: row[8],
      activityType: row[9],
      seasons: row[10],
      NameofCreator : row [11]
    };
  });

  // 绑定HTML模板并传入数据
  var htmlOutput = HtmlService.createTemplateFromFile('Index');
  htmlOutput.activities = activities;
  // 允许嵌入到其他网站
  return htmlOutput.evaluate().setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
}

创建IndexHTML文件

<!DOCTYPE html>
<html>
<head>
  <title>Activities List</title>
  <style>
    .activity-item { 
      cursor: pointer; 
      margin: 10px 0; 
      border: 1px solid #ddd; 
    }
    .activity-details { 
      display: none; 
      background-color: #f9f9f9; 
      padding: 15px; 
    }
  </style>
</head>
<body>
  <div id="activities-container"></div>

  <script>
    // 直接使用脚本传入的数据
    const activities = <?= JSON.stringify(activities) ?>;

    function displayActivities(activities) {
      const container = document.getElementById('activities-container');
      
      activities.forEach(activity => {
        const activityElement = document.createElement('div');
        activityElement.classList.add('activity-item');
        activityElement.innerHTML = `
          <h3>${activity.name}</h3>
          <div class="activity-details">
            <p><strong>Type:</strong> ${activity.indoorOutdoor}</p>
            <p><strong>Resources:</strong> ${activity.resourcesRequired}</p>
            <p><strong>Instructions:</strong> ${activity.instructions}</p>
            <p><strong>Number of Children:</strong> ${activity.numberOfChildren}</p>
            <p><strong>Activity Type:</strong> ${activity.activityType}</p>
            <p><strong>Seasons:</strong> ${activity.seasons}</p>
            <p><strong>Pictures:</strong> ${activity.pictures}</p>
            <p><strong>Video Link:</strong> ${activity.videoLink}</p>
            <p><strong>Risk Assessment:</strong> ${activity.riskAssessment}</p>
          </div>
        `;

        activityElement.querySelector('h3').addEventListener('click', () => {
          const details = activityElement.querySelector('.activity-details');
          details.style.display = details.style.display === 'none' ? 'block' : 'none';
        });

        container.appendChild(activityElement);
      });
    }

    displayActivities(activities);
  </script>
</body>
</html>

部署这个Web应用后，将生成的URL直接嵌入Google网站即可。

3. 确认表格共享权限 #

确保Google表单响应表格的共享权限设置为「任何人可查看」（或组织内所有人可查看），避免脚本因无法访问表格触发权限错误。

4. 清除浏览器缓存测试 #

清除浏览器缓存后重新打开Google网站，避免旧权限缓存导致的问题。

内容的提问来源于stack exchange，提问作者Jacob Berlyn