如何自动化检查各Google Sheets是否已获用户授权？现有脚本返回错误数据

问题描述 #

我拥有多个Google Sheets，每次复制后需用户授权才能使脚本正常运行。请问能否通过脚本检查各表格是否已获用户授权？我尝试运行以下脚本，但它返回错误数据：

function checkAuthorizationForSheetsFromTab() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('New'); // Get the "New" sheet
var urlRange = sheet.getRange('A1:A' + sheet.getLastRow()); // Get all URLs in column A
var urls = urlRange.getValues(); // Get the values as a 2D array

var results = []; // Array to store authorization results for writing to column C

// Loop through each URL in column A
urls.forEach(function(row, index) {
var url = row[0]; // Get the URL from the first column of each row
var isAuthorized = false; // Default to false if not authorized or if there's an error

    if (url) {  // Check if the URL is not empty
      try {
        // Try to open the sheet by its URL
        var targetSheet = SpreadsheetApp.openByUrl(url);
    
        // Try accessing a protected property (e.g., sheet name)
        var sheetName = targetSheet.getName();
    
        // If no error occurs, the script is authorized for this sheet
        Logger.log('Authorized access for sheet: ' + url);
        isAuthorized = true;
      } catch (e) {
        // Handle authorization or other errors
        if (e.message.includes('Authorization')) {
          Logger.log('Script is not authorized for sheet: ' + url);
        } else {
          // Log any other errors (e.g., invalid URL)
          Logger.log('An error occurred with sheet: ' + url + ' - ' + e.message);
        }
      }
    } else {
      Logger.log('No URL found in row ' + (index + 1));
    }
    
    // Push the result (TRUE or FALSE) to the results array
    results.push([isAuthorized]);

});

// Write the results to column C
var resultRange = sheet.getRange(1, 3, results.length, 1); // Column C, from row 1 to the last row
resultRange.setValues(results);

Logger.log('Authorization results have been written to column C.');
}

原脚本问题分析 #

1. 错误判断不精准：Google Apps Script中，授权失败的错误信息不一定包含"Authorization"关键词，可能是"Unauthorized"、"Access denied"或"Permission denied"等，导致无法正确识别授权未通过的情况。
2. 未做边界校验：没有判断主工作表是否存在、A列是否有数据，可能触发无意义的循环或报错。
3. URL处理粗糙：未去除URL前后空格，可能因格式问题导致打开表格失败。

修正后的脚本 #

function checkAuthorizationForSheetsFromTab() {
  const mainSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('New');
  if (!mainSheet) {
    Logger.log('未找到名为"New"的工作表');
    return;
  }

  const lastRow = mainSheet.getLastRow();
  if (lastRow === 0) {
    Logger.log("A列没有任何URL数据");
    return;
  }

  const urlRange = mainSheet.getRange('A1:A' + lastRow);
  const urls = urlRange.getValues();
  const results = [];

  urls.forEach((row, index) => {
    const url = row[0].trim();
    let isAuthorized = false;

    if (url) {
      try {
        // 尝试打开目标表格并执行权限验证操作
        const targetSpreadsheet = SpreadsheetApp.openByUrl(url);
        targetSpreadsheet.getId();
        isAuthorized = true;
        Logger.log(`行${index+1}：已授权访问表格 - ${url}`);
      } catch (e) {
        // 匹配所有常见权限相关错误关键词
        const permissionErrorKeywords = ["Unauthorized", "Access denied", "Permission denied", "Authorization"];
        const isPermissionIssue = permissionErrorKeywords.some(keyword => e.message.includes(keyword));
        
        if (isPermissionIssue) {
          Logger.log(`行${index+1}：脚本未授权访问表格 - ${url}`);
        } else {
          Logger.log(`行${index+1}：处理表格出错 - ${url}，错误：${e.message}`);
        }
      }
    } else {
      Logger.log(`行${index+1}：未找到有效URL`);
    }

    results.push([isAuthorized]);
  });

  // 将结果写入C列
  const resultRange = mainSheet.getRange(1, 3, results.length, 1);
  resultRange.setValues(results);
  Logger.log("授权检查结果已写入C列");
}

关键改动说明 #

• 精准权限错误识别：通过数组匹配多个常见权限错误关键词，避免漏判授权失败情况。
• 增加边界校验：提前检查主工作表是否存在、A列是否有数据，减少无效执行。
• 优化URL处理：添加trim()去除URL前后空格，避免格式问题导致的错误。
• 细化日志：为每一行结果添加行号标识，便于快速定位问题表格。

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