参考：未定义

Question

功能lastrows2正常工作。但是，convert_txt_gsheets投掷

参考文献：未定义拆分

哪个终止整个过程，并阻止Lastrow2参与。分裂使我非常困惑。

function pricing() {
  convert_txt_gsheets();
  lastrow2();
}

function convert_txt_gsheets() {
  var source = SpreadsheetApp.openById('ID1').getSheetByName('Daily Report');
  var targetrange = source.getRange(2, 1, source.getLastRow(), source.getLastColumn());
  targetrange.clear();

  var file = DriveApp.getFileById('ID2');
  var body = file.getBlob().getDataAsString().split(/\r/);
  var result = body.map(split(/|/))
    // vvv
    .map(row => row.map(cell => cell.replaceAll(`"`, ``)));
  SpreadsheetApp.getActive().getSheetByName('Daily Report').getRange(1, 1, result.length, result[0].length).setValues(result);
  return;
}



function lastrow2() {
var source = SpreadsheetApp.openById('ID1').getSheetByName('Daily Report');
var target = SpreadsheetApp.openById('ID1').getSheetByName('Permanent Record');
var target = target.getRange(target.getLastRow()+1, 1, source.getLastRow(), source.getLastColumn());
var rangeValues = source.getRange(2, 1, source.getLastRow(), source.getLastColumn()).getValues();
target.setValues(rangeValues); 
}

这是从TXT提取的示例数据。

COLUMN0|COLUMN1|COLUMN2|COLUMN3|COLUMN4|COLUMN5|COLUMN6|COLUMN7|COLUMN8  
"Rocketship"|"5.99"|"5.39"|"5.39"|""|"5.39"|"5.39"|"7.5.2022"|"william"

修改的代码

function pricing() {
  convert_txt_gsheets();
  lastrow2();
}

function convert_txt_gsheets() {
  var source = SpreadsheetApp.openById('ID1').getSheetByName('Daily Report');
  var targetrange = source.getRange(2, 1, source.getLastRow(), source.getLastColumn());
  targetrange.clear();

  var file = DriveApp.getFileById('ID2');
let body = file.getBlob().getDataAsString().split('\r'); //  split into rows
let results = body.map( row => row.split('|') ); // split into columns
results = result.map( col => col.map( cell => cell.replaceAll(`"`, ``)));
  SpreadsheetApp.getActive().getSheetByName('Daily Report').getRange(1, 1, result.length, result[0].length).setValues(result);
  return;
}



function lastrow2() {
var source = SpreadsheetApp.openById('ID1').getSheetByName('Daily Report');
var target = SpreadsheetApp.openById('ID1').getSheetByName('Permanent Record');
var target = target.getRange(target.getLastRow()+1, 1, source.getLastRow(), source.getLastColumn());
var rangeValues = source.getRange(2, 1, source.getLastRow(), source.getLastColumn()).getValues();
target.setValues(rangeValues); 
}

Answer 1

您在var result = body.map（split（/|/））中犯了一个错误。尝试一下。

替换：

var body = file.getBlob().getDataAsString().split(/\r/);
var result = body.map(split(/|/))
// vvv
  .map(row => row.map(cell => cell.replaceAll(`"`, ``)));

这是我的测试集。适当地更换上面的行。

function test() {
  try {
    let data = 'COLUMN0|COLUMN1|COLUMN2|COLUMN3|COLUMN4|COLUMN5|COLUMN6|COLUMN7|COLUMN8\r "Rocketship"|"5.99"|"5.39"|"5.39"|""|"5.39"|"5.39"|"7.5.2022"|"william"';
    let body = data.split('\r'); //  split into rows
    let results = body.map( row => row.split('|') ); // split into columns
    results = results.map( col => col.map( cell => cell.replaceAll(`"`, ``) ) );
    console.log(results);
  }
  catch(err) {
    console.log(err);
  }
}

执行日志

我不确定日期，但是当您setValues（）（）时，其他人会转换为适当的类型。

6:59:01 AM  Notice  Execution started
6:59:04 AM  Info    [ [ 'COLUMN0', 'COLUMN1', 'COLUMN2', 'COLUMN3', 'COLUMN4', 'COLUMN5', 'COLUMN6', 'COLUMN7', 'COLUMN8' ],
  [ ' Rocketship', '5.99', '5.39', '5.39', '', '5.39', '5.39',  '7.5.2022', 'william' ] ]
6:59:02 AM  Notice  Execution completed