CSV 导入脚本在 Google 电子表格中不起作用

CSV Import Script Not Functioning in Google Spreadsheets

本文关键字:电子表格 不起作用 Google 导入 脚本 CSV      更新时间:2023-09-26

我创建了以下脚本。但它不起作用,我无法弄清楚为什么我按照谷歌上的教程进行操作,但脚本不断超时。有关如何解决此问题的任何帮助都将非常完善。

function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var csvMenuEntries = [{name: "Update HIPAA", functionName: "importHIPAA"}, 
                        {name: "Update Harassment", functionName: "importHarassment"} ];
  ss.addMenu("Update Scores", csvMenuEntries);
}
function importHIPAA() {
  var fileName = "59156.csv";
 var files = DocsList.getFiles();
  var csvFile = "";
  for (var i = 0; i < files.length; i++) {
    if (files[i].getName() == fileName) {
      csvFile = files[i].getContentAsString();
      break;
    }
  }
  var csvData = CSVToArray(csvFile, ",");
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("HIPAA");
  for (var i = 0; i < csvData.length; i++) {
    sheet.getRange(i+1, 1, 1, csvData[i].length).setValues(new Array(csvData[i]));
  }
}
function importHarassment() {
  var fileName = "105921.csv";
 var files = DocsList.getFiles();
  var csvFile = "";
  for (var i = 0; i < files.length; i++) {
    if (files[i].getName() == fileName) {
      csvFile = files[i].getContentAsString();
      break;
    }
  }
  var csvData = CSVToArray(csvFile, ",");
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Harassment");
  for (var i = 0; i < csvData.length; i++) {
    sheet.getRange(i+1, 1, 1, csvData[i].length).setValues(new Array(csvData[i]));
  }
}
// http://www.bennadel.com/blog/1504-Ask-Ben-Parsing-CSV-Strings-With-Javascript-Exec-Regular-Expression-Command.htm
// This will parse a delimited string into an array of
// arrays. The default delimiter is the comma, but this
// can be overriden in the second argument.
function CSVToArray( strData, strDelimiter ){
  // Check to see if the delimiter is defined. If not,
  // then default to comma.
  strDelimiter = (strDelimiter || ",");
  // Create a regular expression to parse the CSV values.
  var objPattern = new RegExp(
    (
      // Delimiters.
      "(''" + strDelimiter + "|''r?''n|''r|^)" +
      // Quoted fields.
      "(?:'"([^'"]*(?:'"'"[^'"]*)*)'"|" +
      // Standard fields.
      "([^'"''" + strDelimiter + "''r''n]*))"
    ),
    "gi"
  );

  // Create an array to hold our data. Give the array
  // a default empty first row.
  var arrData = [[]];
  // Create an array to hold our individual pattern
  // matching groups.
  var arrMatches = null;

  // Keep looping over the regular expression matches
  // until we can no longer find a match.
  while (arrMatches = objPattern.exec( strData )){
    // Get the delimiter that was found.
    var strMatchedDelimiter = arrMatches[ 1 ];
    // Check to see if the given delimiter has a length
    // (is not the start of string) and if it matches
    // field delimiter. If id does not, then we know
    // that this delimiter is a row delimiter.
    if (
      strMatchedDelimiter.length &&
      (strMatchedDelimiter != strDelimiter)
    ){
      // Since we have reached a new row of data,
      // add an empty row to our data array.
      arrData.push( [] );
    }

    // Now that we have our delimiter out of the way,
    // let's check to see which kind of value we
    // captured (quoted or unquoted).
    if (arrMatches[ 2 ]){
      // We found a quoted value. When we capture
      // this value, unescape any double quotes.
      var strMatchedValue = arrMatches[ 2 ].replace(
        new RegExp( "'"'"", "g" ),
        "'""
      );
    } else {
      // We found a non-quoted value.
      var strMatchedValue = arrMatches[ 3 ];
    }

    // Now that we have our value string, let's add
    // it to the data array.
    arrData[ arrData.length - 1 ].push( strMatchedValue );
  }
  // Return the parsed data.
  return( arrData );
}

试试没有这个错别字的脚本:

  while (arrMatches = objPattern.exec( strData )){

应该while (arrMatches == objPattern.exec( strData )){

条件中的相等是双倍 =

您可能

不再遇到这个问题,但以防万一change var files = DocsList.getFiles();对于 var files = DocsList.getAllFiles();

应该工作正常,我做了测试

埃尔梅德克斯