GAS脚本导入邮件XLSX至谷歌表格时临时文件访问失败求助

问题 #

我用了一段Google Apps Script代码，想从每日收到的邮件（发件人it@xyz.org，主题含“cod pendency report”且带附件）里提取XLSX附件，转成临时谷歌表格后导入指定目标表格。代码如下：

function getRawData() {
    // This gets the threads
    var threads = GmailApp.search('from:(it@xyz.org) ((has:attachment OR has:drive) cod pendency report)');
    var message = threads[0].getMessages()[0]; // returns the first message from the first thread
    var attachment = message.getAttachments()[0]; // returns the first attachment
    var xlsxBlob = attachment; // This is a blob, yes.
    
    var convertedSpreadsheetId = Drive.Files.insert({mimeType: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"},xlsxBlob).id;
    Logger.log('successfully converted')
    var sheet = SpreadsheetApp.openById(convertedSpreadsheetId).getSheets()[0];
    Logger.log('temp file opened')
    var data = sheet.getDataRange().getValues(); // gets values from first tab
    
  
    var dataSheet = SpreadsheetApp.openById('designated_target_sheet_id').getSheetByName("Sheet1");
    var dataRange = dataSheet.getRange(1, 1, data.length, data[0].length);
    dataSheet.clear()
    dataRange.setValues(data);
    
    // Moved this line to the end because you were deleting it before using the data inside it.
    // Changed to trash instead of remove.
    Drive.Files.trash(convertedSpreadsheetId); // Remove the converted file.
 }

执行时报错：

Exception: Service Spreadsheets failed while accessing document with id 1lWeTw7MuEOuhD5KedL2J3r5xeY-hZIgl.
getRawData @ c2.gs:10

临时文件已在云端硬盘创建，但脚本无法访问。我试过以下方法都没用：

• 转换为CSV后导入
• 不使用Drive API
• 怀疑文件过大，间隔固定时长后多次重试

我怀疑问题出在附件的MIME类型是octet-stream，卡了很久，求技术帮助。

解决方案 #

1. 强制修正附件MIME类型 #

如果附件MIME类型为application/octet-stream，Drive API无法正确识别为XLSX，导致转换后的文件无法被SpreadsheetApp读取。手动指定Blob的MIME类型：

// 替换原xlsxBlob定义
var xlsxBlob = attachment.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");

2. 转换为谷歌表格格式而非XLSX #

原代码创建的是XLSX文件，而非谷歌表格，SpreadsheetApp无法直接访问。改为指定谷歌表格的MIME类型并强制转换：

// 替换原转换文件代码
var convertedFile = Drive.Files.insert(
  {
    title: "Temp Cod Pendency Report",
    mimeType: "application/vnd.google-apps.spreadsheet" // 谷歌表格专属MIME类型
  },
  xlsxBlob,
  { convert: true } // 强制触发格式转换
);
var convertedSpreadsheetId = convertedFile.id;

3. 添加权限并等待生效（可选） #

若仍存在访问权限问题，给脚本执行账号添加临时文件的编辑权限：

// 创建文件后添加权限
Drive.Permissions.insert(
  {
    role: "writer",
    type: "user",
    value: Session.getActiveUser().getEmail()
  },
  convertedSpreadsheetId
);
Utilities.sleep(3000); // 等待3秒确保权限同步

4. 完整修复后的代码 #

function getRawData() {
    var threads = GmailApp.search('from:(it@xyz.org) ((has:attachment OR has:drive) cod pendency report)');
    if (threads.length === 0) {
        Logger.log("No matching threads found");
        return;
    }
    var message = threads[0].getMessages()[0];
    var attachment = message.getAttachments()[0];
    if (!attachment) {
        Logger.log("No attachment found in the message");
        return;
    }

    // 强制设置正确的XLSX MIME类型
    var xlsxBlob = attachment.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");

    // 转换为谷歌表格
    var convertedFile = Drive.Files.insert(
        { title: "Temp Cod Pendency Report", mimeType: "application/vnd.google-apps.spreadsheet" },
        xlsxBlob,
        { convert: true }
    );
    var convertedSpreadsheetId = convertedFile.id;
    Logger.log('Successfully converted to Google Sheet: ' + convertedSpreadsheetId);

    // 添加权限并等待生效
    Drive.Permissions.insert(
        { role: "writer", type: "user", value: Session.getActiveUser().getEmail() },
        convertedSpreadsheetId
    );
    Utilities.sleep(3000);

    try {
        var sheet = SpreadsheetApp.openById(convertedSpreadsheetId).getSheets()[0];
        Logger.log('Temp file opened');
        var data = sheet.getDataRange().getValues();

        var dataSheet = SpreadsheetApp.openById('designated_target_sheet_id').getSheetByName("Sheet1");
        dataSheet.clear();
        dataSheet.getRange(1, 1, data.length, data[0].length).setValues(data);
        Logger.log('Data imported successfully');
    } catch (e) {
        Logger.log('Error accessing temp sheet or importing data: ' + e.toString());
    } finally {
        // 无论成功与否都清理临时文件
        Drive.Files.trash(convertedSpreadsheetId);
        Logger.log('Temp file moved to trash');
    }
}

5. 额外检查项 #

• 开启Drive API：在脚本编辑器的「资源」→「高级Google服务」中启用Drive API。
• 验证附件完整性：手动下载附件确认是可正常打开的XLSX文件。
• 确认API版本：确保使用的是Drive API v2（脚本默认版本）。

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