csv - Google Apps Script Utilities.parseCsv() 更改小数点和千位分隔符

Question

我是 GAS 的新手，我正在努力解决我遇到的问题。（我没有在网站上找到可以解决我的问题的类似问题，因此我要问一个新问题）

目标：将 CSV 从 Google Drive 导入 Google Sheets
问题：
csv 文件中的货币为“1,000.57”--> 美国格式
我需要的货币格式为“1.000,57”--> 欧洲格式

目前Utilities.parseCsv()的格式只是搞砸了，货币完全是错误的。

问题：有没有办法将“，”更改为“。” 和 ”。” 在解析期间到“，”？如果是这样，由于 csv 的分隔符也是“，”，因此是否会有进一步的问题。

我已经在网上找到了一些代码片段（不是我的代码：电子表格的道具）并尝试更改以下内容，但它似乎不起作用：

//Imports a CSV file in Google Drive into the Google Sheet
function importCSVFromDrive() {
  var fileName = promptUserForInput("Please enter the name of the CSV file to import from Google Drive:");
  var files = findFilesInDrive(fileName);
  if(files.length === 0) {
    displayToastAlert("No files with name \"" + fileName + "\" were found in Google Drive.");
    return;
  } else if(files.length > 1) {
    displayToastAlert("Multiple files with name " + fileName +" were found. This program does not support picking the right file yet.");
    return;
  }
  var file = files[0];
  var csvString = file.getBlob().getDataAsString()
  var escapedString = csvString.replace(",",".")
                                .replace(".",",");
  var contents = Utilities.parseCsv(escapedString);
  var sheetName = writeDataToSheet(contents);
  displayToastAlert("The CSV file was successfully imported into " + sheetName + ".");
}

//Prompts the user for input and returns their response
function promptUserForInput(promptText) {
  var ui = SpreadsheetApp.getUi();
  var prompt = ui.prompt(promptText);
  var response = prompt.getResponseText();
  return response;
}

//Returns files in Google Drive that have a certain name.
function findFilesInDrive(filename) {
  var files = DriveApp.getFilesByName(filename);
  var result = [];
  while(files.hasNext())
    result.push(files.next());
  return result;
}

//Inserts a new sheet and writes a 2D array of data in it
function writeDataToSheet(data) {
  var ss = SpreadsheetApp.getActive();
  sheet = ss.insertSheet();
  sheet.getRange(1, 1, data.length, data[0].length).setValues(data);
  return sheet.getName();
}

我究竟做错了什么？

标签： csvgoogle-apps-scriptgoogle-sheets

Answer 1

Utilities.parseCsv()是一团糟。我建议你不要使用它。相反，请尝试高级 Google 服务 - Drive V2

您需要在服务下添加云端硬盘。

这是您需要的代码片段：

function insertFromCsv(fileName) {
  var blob = DriveApp.getFilesByName(fileName).next().getBlob();
  var tempFile = Drive.Files.insert({title: "tempSheet"}, blob, {
      convert: true
  });
  var tempSsId = tempFile.getId();
  var tempSheet = SpreadsheetApp.openById(tempSsId).getSheets()[0];
  var newSheet = tempSheet.copyTo(SpreadsheetApp.getActive());
  DriveApp.getFileById(tempSsId).setTrashed(true);
  return newSheet.getName();
}

并更改importCSVFromDrive如下：

function importCSVFromDrive() {
  var fileName = promptUserForInput("Please enter the name of the CSV file to import from Google Drive:");
  var files = findFilesInDrive(fileName);
  if(files.length === 0) {
    displayToastAlert("No files with name \"" + fileName + "\" were found in Google Drive.");
    return;
  } else if(files.length > 1) {
    displayToastAlert("Multiple files with name " + fileName +" were found. This program does not support picking the right file yet.");
    return;
  }
  var file = files[0];
  // var csvString = file.getBlob().getDataAsString()
  // var escapedString = csvString.replace(",",".")
  //                               .replace(".",",");
  // var contents = Utilities.parseCsv(escapedString);
  // var sheetName = writeDataToSheet(contents);
  var fileName = file.getName();
  var sheetName = insertFromCsv(fileName);

  displayToastAlert("The CSV file was successfully imported into " + sheetName + ".");
}