Google Apps Script Web应用超链接跳转异常问题求助

需求 #

• 在Google Sheet中维护“Passcode”和“Url”两列数据
• 通过Google Apps Script部署Web应用，用户输入密码后，匹配Passcode列内容则返回对应Url值

问题现象 #

返回的Url设置为超链接后，点击会跳转到异常地址，示例：https://n-ccxp7zryfzliwrfqnp5cqe6fz2xj5be3d6vsvpq-0lu-script.googleusercontent.com/google.com

当前代码 #

Google Apps Script (GS) 代码 #

function getPasswordURL(password) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var data = sheet.getDataRange().getValues();
  
  for (var i = 0; i < data.length; i++) {
    if (data[i][0] === password) {
      return data[i][1];
    }
  }
  
  return "URL not found";
}

function doGet() {
  return HtmlService.createHtmlOutputFromFile('index');
}

// 重复定义的冗余函数
function getPasswordURL(password) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var data = sheet.getDataRange().getValues();
  
  for (var i = 0; i < data.length; i++) {
    if (data[i][0] === password) {
      return data[i][1];
    }
  }
  
  return "URL not found";
}

index.html代码 #

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
    <form id="passwordForm">
      <label for="password">Enter Password:</label>
      <input type="text" id="password" name="password" required>
      <input type="submit" value="Submit">
    </form>
    
    <div id="result"></div>

    <script>
      document.getElementById("passwordForm").addEventListener("submit", function(event) {
        event.preventDefault();
        
        var password = document.getElementById("password").value;
        
        google.script.run.withSuccessHandler(updateResult).getPasswordURL(password);
      });

      function updateResult(url) {
        var resultDiv = document.getElementById("result");
        var domain = extractDomain(url);
        var link = document.createElement("a");
        link.href = "#";
        link.textContent = domain;
        link.addEventListener("click", function(event) {
          event.preventDefault();
          openNewTab(url);
        });
        resultDiv.innerHTML = "";
        resultDiv.appendChild(link);
      }

      function extractDomain(url) {
        var domain;
        if (url.indexOf("://") > -1) {
          domain = url.split('/')[2];
        } else {
          domain = url.split('/')[0];
        }
        domain = domain.split(':')[0];
        domain = domain.replace("www.", "");
        return domain;
      }

      function openNewTab(url) {
        var baseUrl = url.replace(/^(https?:\/\/[^/]+)\/.*$/, "$1");
        var newTab = window.open(baseUrl, '_blank');
        newTab.focus();
      }
    </script>
  </body>
</html>

问题根源及解决方法 #

1. 冗余函数问题 #

GS代码中重复定义了getPasswordURL函数，属于无效冗余代码，需删除其中一个重复函数。

2. URL跳转异常核心原因 #

• 当Sheet中的Url缺失完整协议头（如仅填google.com而非https://google.com）时，浏览器会将其识别为相对路径，拼接在Web应用域名后生成异常地址。
• openNewTab函数中错误使用正则提取baseUrl，反而破坏了完整Url的跳转逻辑。

修复后代码 #

调整后的GS代码

function getPasswordURL(password) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var data = sheet.getDataRange().getValues();
  
  for (var i = 0; i < data.length; i++) {
    if (data[i][0] === password) {
      return data[i][1];
    }
  }
  
  return "URL not found";
}

function doGet() {
  return HtmlService.createHtmlOutputFromFile('index');
}

调整后的index.html代码

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
    <form id="passwordForm">
      <label for="password">Enter Password:</label>
      <input type="text" id="password" name="password" required>
      <input type="submit" value="Submit">
    </form>
    
    <div id="result"></div>

    <script>
      document.getElementById("passwordForm").addEventListener("submit", function(event) {
        event.preventDefault();
        
        var password = document.getElementById("password").value;
        
        google.script.run.withSuccessHandler(updateResult).getPasswordURL(password);
      });

      function updateResult(url) {
        var resultDiv = document.getElementById("result");
        if (url === "URL not found") {
          resultDiv.textContent = url;
          return;
        }
        // 补全URL协议头，避免相对路径问题
        var fullUrl = ensureUrlProtocol(url);
        var domain = extractDomain(fullUrl);
        var link = document.createElement("a");
        link.href = fullUrl;
        link.target = "_blank";
        link.textContent = domain;
        resultDiv.innerHTML = "";
        resultDiv.appendChild(link);
      }

      function ensureUrlProtocol(url) {
        // 若URL无协议头，默认添加https://
        if (!/^https?:\/\//i.test(url)) {
          return "https://" + url;
        }
        return url;
      }

      function extractDomain(url) {
        var domain;
        if (url.indexOf("://") > -1) {
          domain = url.split('/')[2];
        } else {
          domain = url.split('/')[0];
        }
        domain = domain.split(':')[0];
        domain = domain.replace("www.", "");
        return domain;
      }
    </script>
  </body>
</html>

修复说明 #

1. 补全协议头：新增ensureUrlProtocol函数，确保所有Url都带有https://协议头，避免被识别为相对路径。
2. 简化跳转逻辑：直接将超链接的href设为完整Url，添加target="_blank"属性，无需额外点击事件监听，逻辑更可靠。
3. 错误提示优化：当返回“URL not found”时，直接显示文本提示，避免生成无效链接。

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