google-apps-script - The number of columns in the data does not match the number of columns in the range. The data has 0 but the range has 1(line 32, file "Code")

Question

I keep getting the above message when trying to write data back to a sheet (over the existing row 3 of the sheet). I am using the map method to find and replace data. The script runs fine until it needs to write the data back. There is about 21000 rows with 5 columns of data and it writes up to about 10000 rows back to the sheet before the error message occurs.

I just don't understand how there can be 0 rows in the data? Below is what the data looks like if I log it out

[
[Queue 1], 
[Queue 3], 
[Queue 3], 
[Queue 2], 
]

And this is what the original data looks like that I run the map method on (row[2]) before returning the above array of replaced values.

[
[1.59740765627988E9, Fri Aug 14 12:21:33 GMT+02:00 2020, 45*10, Agent/25*1000, RINGNOANSWER],
[1.59740765627988E9, Fri Aug 14 12:21:56 GMT+02:00 2020, 45*10, Agent/25*1009, RINGNOANSWER],
[1.59741951423215E9, Fri Aug 14 15:39:12 GMT+02:00 2020, 45*10, Agent/25*1035, RINGNOANSWER]
]

This is the full script

function missedCalls(){

var hotFolder = DriveApp.getFolderById('Id here');
var targetSheet = SpreadsheetApp.openById('Id here').getSheetByName('Sheet Name Here');
var missedCallsSheet = SpreadsheetApp.openById('Id here').getSheetByName('Sheet Name Here');
var csv = hotFolder.getFilesByName('filename.csv')

if(csv.hasNext()){

  var csvFile = csv.next();
  
  var csvData = Utilities.parseCsv(csvFile.getBlob().getDataAsString());
  
  //Logger.log(csvData);
  
  targetSheet.getRange(1,1,csvData.length,csvData[0].length).setValues(csvData);
  
  //csvFile.setTrashed(true);

  } else{
  
  }

var newData = targetSheet.getRange(2,1,targetSheet.getLastRow(),targetSheet.getLastColumn()).getValues();

Logger.log(newData);

var replace = newData.map(replaceNames);

Logger.log(replace);

targetSheet.getRange(2,3,replace.length,1).setValues(replace);


}

function replaceNames(row){

    if(row[2] === '45*05'){
    
    return ['Queue 1'];
    
    }else if(row[2] === '45*06') {
    
    return ['Queue 2'];
    
    }else if(row[2] === '45*07') {
    
    return ['Queue 3'];
    
    }else if(row[2] === '45*08') {
    
    return ['Queue 4'];
    
    }else if(row[2] === '45*09') {
    
    return ['Queue 5'];
    
    }else if(row[2] === '45*10') {
    
    return ['Queue 6'];

    } else {
    
    }

}

标签： google-apps-script

Answer 1

问题：

当不满足任何条件时，该replaceNames函数不返回任何内容。undefined当将此函数作为回调传递给Array.map

解决方案：

使回调始终返回相同类型的对象（即，只有一个元素的数组）

片段：

function replaceNames(row){

    if(row[2] === '45*05'){
    
    return ['Queue 1'];
    
    } else if(row[2] === '45*06'){}
    /*Other conditions*/
    } else {
      /*Added*/return [''];
    }

}