Google Sheet导出含特殊字符CSV时引号异常问题求助

问题描述 #

已实现按指定表头导出列并修改导出文件表头的功能（例如将"Product Name - Full"改为"Description 1"），但脚本存在以下问题：

• 字段包含逗号、引号时，引号会被转为ï
• 第二个引号会导致对应数据被挤入下一列

原问题脚本 #

function Export_Database() {
  var ss = SpreadsheetApp.getActiveSpreadsheet(); 
  var sheet = ss.getSheetByName('Database');
  var folderTime = Utilities.formatDate(new Date(), "GMT-8", "yyyy-MM-dd'_'HH:mm:ss")   // Logger 1-3 
  var folder = DriveApp.createFolder(ss.getName().toLowerCase().replace(/ /g,'_') + '_csv_' + folderTime);
  var fileName = sheet.getName() + ".csv";
  var csvFile = Convert_Database(fileName, sheet);                              //****REF
  var file = folder.createFile(fileName, csvFile);
  var downloadURL = file.getDownloadUrl().slice(0, -8);
  Export_DatabaseURL(downloadURL);                                               //****REF
  //1 Logger.log("DEBUG: Folder date = "+folderTime) // DEBUG
  //2 Logger.log("DEBUG: Proposed folder name: "+ss.getName().toLowerCase().replace(/ /g,'_') + '_csv_' + folderTime) // DEBUG
  //3 Logger.log("DEBUG: Proposed file name: "+sheet.getName() + ".csv") // DEBUG
}

function Export_DatabaseURL(downloadURL) {                                       //****REF
  var link = HtmlService.createHtmlOutput('<a href="' + downloadURL + '">Click here to download</a>');
  SpreadsheetApp.getUi().showModalDialog(link, 'Your CSV file is ready!');
}


function Convert_Database(csvFileName, sheet) {
  const wb = SpreadsheetApp.getActiveSpreadsheet();  
  const sh = wb.getSheetByName("Database");
  const allvalues = sh.getRange(1,1,sh.getLastRow(),sh.getLastColumn()).getValues()         // Logger 1 | Get values (Row,Column,OptNumRows,OptNumColumns) 
  
  const header1 = allvalues[0].indexOf("SKU")                                               // get the column Index of the headers
  const header2 = allvalues[0].indexOf("Product Name - Full") 
  const header3 = allvalues[0].indexOf("Date Updated")  
  const header4 = allvalues[0].indexOf("Purchase Unit")
  const header5 = allvalues[0].indexOf("PRICE1")      
  const header6 = allvalues[0].indexOf("Conversion") 
  const header7 = allvalues[0].indexOf("Delivery Product Name")
  const header8 = allvalues[0].indexOf("Delivery Product Description")
                                           
  allvalues[0][header2] = "Description 1"                   // Assign replacement values
  allvalues[0][header3] = "Date"
  allvalues[0][header4] = "P. Unit"
  allvalues[0][header5] = "Price 1"
  allvalues[0][header6] = "Conv"
  allvalues[0][header7] = "Short Description"
  allvalues[0][header7] = "Long Description"

  // extract only the columns that relate to the headers 
  var data = allvalues.map(function(o){return [  
  o[header1],o[header2],o[header3],o[header4],o[header5],o[header6],o[header7],o[header8]
  ]})         
  
  // convert double quotes to unicode
  //loop over the rows in the array
  for (var row in data) {
    //use Array.map to execute a replace call on each of the cells in the row.
    var data_values = data[row].map(function(original_datavalue) {
      return original_datavalue.toString().replace('"', '＂');
    })
    //replace the original row values with the replaced values
    data[row] = data_values;
  }

  // wrap any value containing a comma in double quotes
  data = data.map(function(e) {return e.map(function(f) {return ~f.indexOf(",") ? '"' + f + '"' : f})})
  
  // Logger.log("data rows = "+data.length+", data columns = "+data[0].length)

  var csvFile = undefined
  // loop through the data in the range and build a string with the csv data
    if (data.length > 1) {
      var csv = "";
      for (var dataRow = 0; dataRow < data.length; dataRow++) {

        // join each row's columns
        // add a carriage return to end of each row, except for the last one
        if (dataRow < data.length-1) {
          // valid data row
          csv += data[dataRow].join(&quot;,&quot;) + &quot;\r\n&quot;;
          //Logger.log(&quot;DEBUG: row#&quot;+dataRow+&quot;, csv = &quot;+data[dataRow].join(&quot;,&quot;) + &quot;\r\n&quot;)
        }
        else {
          csv += data[dataRow];
        }
      }
      csvFile = csv;
    }
    return csvFile;
}

问题根源 #

1. 表头赋值错误：原脚本中allvalues[0][header7]被重复赋值，覆盖了"Short Description"，应该是给header8赋值"Long Description"。
2. 引号处理不符合CSV规范：原脚本错误替换HTML实体"，且未全局替换，同时CSV规范要求内部双引号需转成双引号，而非替换成全角引号。
3. 编码问题：创建CSV文件时未指定UTF-8编码，导致特殊字符乱码（如ï）。
4. 废弃API使用：getDownloadUrl()已被Google废弃，需改用getUrl()。

修复后脚本 #

function Export_Database() {
  var ss = SpreadsheetApp.getActiveSpreadsheet(); 
  var sheet = ss.getSheetByName('Database');
  var folderTime = Utilities.formatDate(new Date(), "GMT-8", "yyyy-MM-dd'_'HH:mm:ss");
  var folder = DriveApp.createFolder(ss.getName().toLowerCase().replace(/ /g,'_') + '_csv_' + folderTime);
  var fileName = sheet.getName() + ".csv";
  var csvContent = Convert_Database(sheet);
  // 创建UTF-8编码的CSV文件，避免乱码
  var file = folder.createFile(fileName, csvContent, MimeType.CSV);
  // 使用新的下载链接方式
  var downloadURL = file.getUrl();
  Export_DatabaseURL(downloadURL);
}

function Export_DatabaseURL(downloadURL) {
  // 修复HTML输出的引号问题
  var link = HtmlService.createHtmlOutput(`<a href="${downloadURL}" target="_blank">Click here to download</a>`);
  SpreadsheetApp.getUi().showModalDialog(link, 'Your CSV file is ready!');
}

function Convert_Database(sheet) {
  const allvalues = sheet.getRange(1, 1, sheet.getLastRow(), sheet.getLastColumn()).getValues();
  
  // 获取目标列索引并映射新表头
  const headerMap = {
    "SKU": "SKU",
    "Product Name - Full": "Description 1",
    "Date Updated": "Date",
    "Purchase Unit": "P. Unit",
    "PRICE1": "Price 1",
    "Conversion": "Conv",
    "Delivery Product Name": "Short Description",
    "Delivery Product Description": "Long Description"
  };
  
  // 提取目标列并替换表头
  const targetHeaders = Object.keys(headerMap);
  const columnIndices = targetHeaders.map(header => allvalues[0].indexOf(header));
  const newHeaders = Object.values(headerMap);
  
  // 构建数据：表头+内容
  const data = [newHeaders];
  for (let i = 1; i < allvalues.length; i++) {
    const row = columnIndices.map(idx => allvalues[i][idx]);
    data.push(row);
  }
  
  // 按CSV规范处理每个单元格：包含逗号、引号、换行的字段用双引号包裹，内部引号转成双引号
  const formattedData = data.map(row => {
    return row.map(cell => {
      const str = String(cell);
      if (str.includes(',') || str.includes('"') || str.includes('\n') || str.includes('\r')) {
        return `"${str.replace(/"/g, '""')}"`;
      }
      return str;
    }).join(',');
  });
  
  // 拼接成CSV字符串，用\r\n换行
  return formattedData.join('\r\n');
}

修复说明 #

• 修正表头赋值错误，用对象映射方式更清晰，避免重复赋值问题。
• 遵循RFC4180 CSV规范处理特殊字符：
  • 包含逗号、引号、换行的字段用双引号包裹
  • 字段内部的双引号替换为两个双引号
• 创建文件时指定MimeType.CSV，确保UTF-8编码，解决乱码问题。
• 替换废弃的getDownloadUrl()为getUrl()，修复下载链接问题。
• 使用模板字符串简化HTML输出，避免引号转义错误。

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