Ruby导出Google Sheets本地文件无法打开问题求助

• Ruby程序从本地表格提取SKU和QTY数据写入Google Sheets后，导出到本地的xlsx/pdf文件无法打开，提示“文件格式或扩展名无效”
• 在线Google Sheets数据显示正常，仅本地保存环节出现异常
• 程序运行无报错，但会输出String和Unknown Key: SameSite = none提示信息

1. export_as_file格式推断失效：google_drive gem的worksheet.export_as_file方法依赖文件扩展名自动推断导出格式，实际请求可能返回HTML或其他非目标格式，却被命名为xlsx/pdf，导致文件损坏
2. 路径分隔符错误：代码中使用单反斜杠\作为Windows路径分隔符（如path\to\output.xlsx），Ruby中未转义的反斜杠会被解析为转义字符，导致文件保存路径错误
3. SameSite警告干扰请求：Unknown Key: SameSite = none提示说明旧版本google_drive gem的Cookie设置不兼容Google Drive API，可能导致导出请求不完整，生成的文件缺失内容
4. 邮件未添加附件：原代码send_email方法未实现附件添加逻辑，即使文件正常生成也无法随邮件发送

1. 明确指定导出格式，替代export_as_file #

直接调用Google Drive导出API，通过MIME类型强制指定格式，避免扩展名推断错误：

def download_google_sheet(sheet_id, local_file_path)
  session = GoogleDrive::Session.from_service_account_key("client_secret.json")
  spreadsheet = session.spreadsheet_by_key(sheet_id)
  worksheet = spreadsheet.worksheets.first

  # 根据扩展名匹配对应MIME类型
  mime_type = case local_file_path.downcase
              when /\.xlsx$/ then "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
              when /\.pdf$/ then "application/pdf"
              else "text/csv"
              end

  # 直接获取导出内容并写入文件
  file_content = session.download_file(worksheet.id, mime_type: mime_type)
  File.open(local_file_path, "wb") do |file|
    file.write(file_content)
  end

  # 验证文件完整性
  raise "导出文件为空，请检查权限或Sheet内容" if File.size(local_file_path) == 0
end

2. 修复路径分隔符问题 #

将Windows路径的单反斜杠替换为正斜杠或双反斜杠：

local_file_path = 'path/to/output.xlsx'
# 或
local_file_path = 'path\\to\\output.xlsx'

3. 解决SameSite警告 #

更新google_drive gem到最新版本，修复Cookie设置兼容性问题：

bundle update google_drive

4. 补充邮件附件逻辑 #

修改send_email方法，添加附件功能：

def send_email(file_path, attachment_path)
  options = {
    address: 'smtp.gmail.com',
    port: 587,
    user_name: 'email@email.com',
    password: 'password',
    domain: 'domain.com',
    authentication: 'plain',
    enable_starttls_auto: true
  }
  Mail.defaults do
    delivery_method :smtp, options
  end

  mail = Mail.new do
    from    'email@email.com'
    to      'email@email.com'
    subject "Order Details:"
    body    "Hey Tim. Hope you're doing well! I just need to place an order for the following items:\n\nThis will be using Net Terms.\n\nPlease let me know if you need anything else from me! Have a great week!"
    add_file attachment_path # 添加导出的文件作为附件
  end
  mail.deliver!
end

# 在process_order中传递附件路径
def process_order(file_path)
  order_details = read_spreadsheet(file_path)
  formatted_data = format_data_plain_text(order_details)

  insert_data(file_path, formatted_data)
  puts "Data inserted into Google Sheets."

  sheet_id = 'randomlettersandnumbers'
  local_file_path = 'path/to/output.xlsx'
  download_google_sheet(sheet_id, local_file_path)
  send_email(file_path, local_file_path) # 传入附件路径
end

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