Google Ads脚本无法创建CSV字符串Blob：Utilities.newBlob异常

我开发了一款Google Ads脚本，用来自动把CSV报表邮件给自己。流程是调用AdWords API获取数据，写入电子表格后再读取，遍历行生成CSV字符串，最后创建Blob并发送邮件。之所以用读写电子表格的方式，是为了保证数据格式正确，但现在创建Blob时失败，报错：

Exception: Unexpected error while getting the method or property newBlob on object Utilities. (line 53)

我试过移除newBlob参数、直接发送原始CSV字符串等方法，都没解决，找不到问题出在哪，附上完整脚本代码：

const SS_ID = '1_super_secret_id_Q';
const SS_NAME = 'GCLID Report';
const SHEET_NAME = 'DURING YESTERDAY'
const EMAIL_TO = 'c_super_secret_email_g'

const MILLIS_PER_DAY = 1000 * 60 * 60 * 24;

function collect_report() {
  return AdsApp.report(
    'SELECT                ' +
    '  click_view.gclid,   ' +
    '  segments.date,      ' +
    '  customer.id,        ' +
    '  ad_group.id,        ' +
    '  campaign.id         ' +
    'FROM click_view       ' +
    'WHERE segments.date DURING YESTERDAY'
  );
}

function calculate_yesterday() {
  var now = new Date();
  var yesterday = new Date(now.getTime() - MILLIS_PER_DAY);
  var timeZone = AdsApp.currentAccount().getTimeZone();
  var yesterday_str = Utilities.formatDate(yesterday, timeZone, 'yyyy-MM-dd');
  return yesterday_str;
}

function convert_sheet_to_csv(sheet) {
  var csv_data = '';
  
  // This represents ALL the data.
  var range = sheet.getDataRange();
  var values = range.getValues();

  // This logs the spreadsheet in CSV format.
  for (let i = 0; i < values.length; i++) {
    csv_data += (values[i].join(',')) + '\r\n';
  }
}

function main() {
  var report = collect_report();
  var spreadsheet = SpreadsheetApp.openById(SS_ID);
  var sheet = spreadsheet.getSheetByName(SHEET_NAME);
  
  sheet.clearContents();
  report.exportToSheet(sheet);

  var mime = 'text/plain'
  var yesterday_str = calculate_yesterday();
  var filename = 'gclid_report.csv';
  var csv_data = convert_sheet_to_csv(sheet);
  var blob = Utilities.newBlob(csv_data, mime, filename);


  var email_subject = 'Your Google Ads Report Is Ready: GCLID Report';
  var email_body = 'Attached is the ' + filename;

  MailApp.sendEmail(
    EMAIL_TO,
    email_subject,
    email_body,
    {
        name: 'Automatic Emailer Script',
        attachments: [blob]
    }
  );
}

main();

问题根源与修复方案 #

1. 核心问题：convert_sheet_to_csv未返回值 #

你的convert_sheet_to_csv函数内部拼接了CSV字符串，但最后没有用return语句把结果返回出去，导致var csv_data = convert_sheet_to_csv(sheet);得到的是undefined。把undefined传入Utilities.newBlob就会触发异常。

修复方法：在函数末尾添加返回语句：

function convert_sheet_to_csv(sheet) {
  var csv_data = '';
  
  var range = sheet.getDataRange();
  var values = range.getValues();

  for (let i = 0; i < values.length; i++) {
    csv_data += (values[i].join(',')) + '\r\n';
  }
  // 新增返回语句
  return csv_data;
}

2. 可选优化：CSV格式兼容性处理 #

当前的拼接逻辑没处理包含逗号、引号的字段，会导致CSV文件打开后格式错乱。可以优化字段处理逻辑：

function convert_sheet_to_csv(sheet) {
  var csv_data = '';
  var range = sheet.getDataRange();
  var values = range.getValues();

  for (let row of values) {
    // 处理特殊字段：含逗号/引号的字符串用双引号包裹，内部引号转义
    var formattedRow = row.map(cell => {
      if (typeof cell === 'string' && (cell.includes(',') || cell.includes('"'))) {
        return `"${cell.replace(/"/g, '""')}"`;
      }
      return cell;
    });
    csv_data += formattedRow.join(',') + '\r\n';
  }
  return csv_data;
}

3. 可选简化：跳过电子表格中转步骤 #

其实Google Ads的Report对象本身支持直接导出CSV格式数据，省去读写电子表格的步骤，提升脚本效率：

function main() {
  var report = collect_report();
  // 直接获取CSV格式的报表数据
  var csv_data = report.getReportAsString('CSV');
  var mime = 'text/csv'; // 使用更准确的CSV MIME类型
  var filename = 'gclid_report.csv';
  var blob = Utilities.newBlob(csv_data, mime, filename);

  var email_subject = 'Your Google Ads Report Is Ready: GCLID Report';
  var email_body = 'Attached is the ' + filename;

  MailApp.sendEmail(
    EMAIL_TO,
    email_subject,
    email_body,
    {
        name: 'Automatic Emailer Script',
        attachments: [blob]
    }
  );
}

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