Google Apps脚本Utilities.parseCsv（）和替换字符- [英] Google Apps Script Utilities.parseCsv() and replacement character - �

问题描述

我正在从事一个涉及Google云端硬盘中csv文件的项目，该文件每分钟左右都会更新一次。

I'm working on a project that involves a csv file in Google Drive that is updated with new data every minute or so.

我建立了一个电子表格仪表板，以使csv中的数据更有意义。

I've built a spreadsheet dashboard to make the data in the csv more meaningful.

假设我可以使用Google Spreadsheet函数= importdata（url）将数据从Google Drive中的CSV数据导入我的Google Spreadsheet的错误，但是，这会产生错误，除非我将CSV公开，这对安全性是不可行的和隐私原因。即使我确实公开了CSV并使用importdata，输入的数据也完全不正确且无法使用-看起来与实际CSV完全不同。

I made the mistake of assuming that I could use the Google Spreadsheet function =importdata(url) to get the data from the CSV in Google Drive into my Google Spreadsheet, however, that produces an error unless I make the CSV public, which is not feasible for security and privacy reasons. Even if I do make the CSV public and use importdata, the data that comes in is completely malformed and unuseable - it looks nothing like the actual CSV.

带有importdats（url）的格式错误的数据

I我正在尝试编写一个脚本，以使用DriveApp打开csv文件自动导入csv数据，使用Utilities.parseCsv将csv转换为数据数组，然后使用setValues将数据写入工作表。

I'm trying to write a script to automatically import the csv data using DriveApp to open the csv file, Utilities.parseCsv to turn the csv into an array of data, and then setValues to write the data to the sheet.

function importData() {
  var ss = SpreadsheetApp.getActive();
  var file = DriveApp.getFilesByName("Agent Performance.csv")
  var csv = file.next().getBlob().getDataAsString();
  var csvData = Utilities.parseCsv(csv);
  var sheet = ss.getSheetByName('CSV Import TEST');
  for (var i = 0; i < csvData.length; i++) {
    sheet.getRange(i+1, 1, 1, csvData[i].length).setValues(new Array(csvData[i]));
  }
}

问题是我得到了替换字符，例如：写入工作表的所有数据。真奇怪工作表看起来很正常，但是如果您单击一个单元格以查看其值，则编辑栏将具有该单元格中任何文本，但每个字符之间是一个``。这样便无法在Google表格中进行任何计算。

The issue is I get replacement characters like: � all throughout the data that gets written to the sheet. It's so strange. The sheet looks normal, but if you click on a cell to see it's value the formula bar has whatever the text is in the cell, but between each character is a �. This makes any calculation in Google Sheets impossible.

注意公式栏中的替换字符

如果我只是使用Google表格文件和导入以及替换当前表格导入相同的CSV，数据很好。这不是一个好的解决方案，因为我真的希望CSV更新与Google表格中的仪表板更新之间的延迟不超过几分钟。如果我必须手动上载CSV，则会破坏系统的用途。

If I just import the same CSV using Google Sheets "File" and "Import" and "Replace current sheet", the data comes in fine. This isn't a good solution because really I want no more than a few minutes of a delay between when the CSV updates and when the dashboard in the Google Sheets update. If I have to manually upload the CSV, it defeats the purpose of the system.

任何帮助将不胜感激。谢谢！

Any help would be greatly appreciated. Thank you!

推荐答案

您检查文件字符集了吗？您可以在调用 getDataAsString（字符集） 。试试这个：

Have you check the file charset? You can specify it when calling getDataAsString(charset). Try this:

function importData() {
  var ss = SpreadsheetApp.getActive();
  var file = DriveApp.getFilesByName("Agent Performance.csv")
  var csv = file.next().getBlob().getDataAsString('ISO-8859-1'); //note the charset
  var csvData = Utilities.parseCsv(csv);
  //unless you csv has variable amount of columns per line, you should do this
  if(csvData.length > 0) {
    ss.getSheetByName('CSV Import TEST')
      .getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);
  } else
    throw 'Blank file';
}

这篇关于Google Apps脚本Utilities.parseCsv（）和替换字符- 的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！