Google Scripts推送数据到BigQuery列数过多报错解决

使用Google Scripts调用Salesforce API推送Opportunities（商机）数据至BigQuery时，接口返回作业提交成功，但BigQuery控制台显示作业执行失败，报字段数不匹配、CSV解析错误。

数据提取逻辑 #

定义opps变量存储从Salesforce API返回结果中筛选的目标字段，代码如下：

var opps = [];
for (var i in arrOpportunities.records) {
     let data = arrOpportunities.records[i];
     let createDate = Utilities.formatDate(new Date(data.CreatedDate), "GMT", "dd-MM-YYYY");
     let modDate = Utilities.formatDate(new Date(data.LastModifiedDate), "GMT", "dd-MM-YYYY");

     let a1 = 'C' + (parseInt(i, 10) + 2);
     let companyFormula = '=IFERROR(INDEX(Accounts,MATCH(' + a1 + ',Accounts!$B2:$B,0),1),"")';
     opps.push([data.Name, data.Id, data.AccountId, companyFormula, data.StageName, data.IsClosed, data.IsWon, createDate, modDate, data.ContactId, data['Region_by_manager__c'], data['Industry__c'], data['Customer_type__c'], data['acc__c'], data['Reason_for_lost_deal__c'], data['hs_deal_id__c'], data['Solution__c'], data['Solution_Elements__c']]);
   }

数据处理完成后调用guideInsertData('Opportunities', 'Opportunities', opps);，该函数负责检查BigQuery目标表是否存在，不存在则自动建表，最终触发数据写入逻辑。

表检查调度函数 #

function guideInsertData(tableName, headers, data) {
 const tableExists = checkTables(tableName);

   if (tableExists == false) {
     let createTable = prepareSchema(headers, tableName);
     if (createTable == false) {
       throw 'Unable to create table';
     }

   }
  insertData(tableName,data); 
}

数据写入函数 #

function insertData(tableName, arrData) {
 const projectId = 'lateral-scion-352013', datasetId = 'CRM_Data', tableId = tableName;
 const job = {
   configuration: {
     load: {
       destinationTable: {
         projectId: projectId,
         datasetId: datasetId,
         tableId: tableId
       },
       skipLeadingRows: 1
     }
   }
 };

 const strData = arrData.join("\n");
 const data = Utilities.newBlob(strData,"application/octet-stream");
 Logger.log(strData);
 Logger.log(data.getDataAsString());

 try {
   BigQuery.Jobs.insert(job, projectId, data);
   let success = 'Load job started, check job status in Google Cloud console activity page of target project';
     Logger.log(success);
     return success;
 } catch (err) {
   Logger.log(err);
   Logger.log('unable to insert job');
   return 'unable to insert data';
 }
}

实现逻辑参考Google官方BigQuery Apps Script加载CSV数据的示例，本地日志检查无异常，接口返回作业提交成功提示。

BigQuery控制台显示作业状态为Failed:Complete BigQuery job，具体错误如下：

• Invalid argument (HTTP 400): Error while reading data, error message: Too many values in row starting at position: 234. Found 23 column(s) while expected 18.（无效参数（HTTP 400）：读取数据错误，从位置234开始的行存在过多值，检测到23列，预期仅18列）
• Error while reading data, error message: CSV processing encountered too many errors, giving up. Rows: 0; errors: 1; max bad: 0; error percent: 0（读取数据错误：CSV处理错误数达上限已终止，处理行数：0；错误数：1；最大允许错误行数：0；错误占比阈值：0）
• You are loading data without specifying data format, data will be treated as CSV format by default. If this is not what you mean, please specify data format by --source_format.（加载数据未指定格式，默认按CSV解析，若不符合预期请通过--source_format参数指定源数据格式）

根因定位 #

1. CSV生成逻辑错误：直接对二维数组调用join("\n")时，子数组会默认调用toString()用英文逗号拼接字段，但不会对字段内部的逗号做转义。代码中companyFormula字段包含公式内的逗号，会被BigQuery的CSV解析器识别为列分隔符，导致原本18列的行被拆分为23列，触发字段数不匹配错误。
2. 作业配置缺失：load作业未显式指定源数据格式、CSV转义规则，依赖默认解析规则容易出现识别异常。
3. 参数配置错误：设置了skipLeadingRows: 1，但传入的数据全为业务数据、无表头行，即使解析成功也会丢失第一行数据。

修复步骤 #

1. 修正CSV生成逻辑，增加字段转义处理：替换原有直接join的逻辑，对包含逗号、双引号、换行符的字段用双引号包裹，字段内的双引号做转义处理，避免分隔符误识别。
   新增CSV行格式化方法，并替换原有字符串拼接逻辑：

   // 新增CSV字段转义方法
   function formatCsvRow(row) {
     return row.map(field => {
       if (field === null || field === undefined) return '""';
       const strField = String(field);
       // 字段含特殊分隔字符时做转义包裹
       if (strField.includes(',') || strField.includes('"') || strField.includes('\n')) {
         return `"${strField.replace(/"/g, '""')}"`;
       }
       return strField;
     }).join(',');
   }
   
   // 替换insertData中原有的strData生成逻辑
   const strData = arrData.map(formatCsvRow).join("\n");
   

2. 修正BigQuery load作业配置：显式指定CSV格式参数，移除错误的跳过首行配置，修改后的job配置如下：

   const job = {
     configuration: {
       load: {
         destinationTable: {
           projectId: projectId,
           datasetId: datasetId,
           tableId: tableId
         },
         sourceFormat: 'CSV', // 显式指定源格式为CSV
         skipLeadingRows: 0, // 无表头行，不跳过数据
         allowQuotedNewlines: true, // 允许引号包裹的字段内存在换行
         quote: '"', // 指定字段引用符为双引号
         fieldDelimiter: ',' // 指定列分隔符为逗号
       }
     }
   };
   

3. 上线前校验：打印转义后生成的CSV前几行，确认每一行解析后字段数为18，无异常拆分问题后再提交作业。

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