在使用应用程序脚本将其作为附件通过电子邮件发送之前，试图对几个列进行排序和删除，但显示错误

Question

我正在向学生发送一个谷歌表单选项卡，它运行良好。但在发送给他们之前，我需要使用Column3值对工作表进行排序，然后删除column2、column4、column5和column7。但它显示了错误：

"TypeError:比较函数必须是函数或未定义的"；在代码中的contents.sort((和contents.delete((在下面

我查看了许多帖子，但无法找出问题所在。

Function sendEmails(){
var sss = SpreadsheetApp.getActiveSpreadsheet();
var ssID = sss.getId();
var sheetName = sss.getName(); 
var sh = SpreadsheetApp.getActive();
var sheet1 = sh.getSheetByName("TempDataSet");
var shID = sheet1.getSheetId().toString();

var subject = 'Your Attendance Record at BDU';
var body = 'Dear Student,'+ 'nn' + 'Greetings! Please find the attendance record attached for your reference.' + 'nn' + 'Thank you.';

var requestData = {"method": "GET", "headers":{"Authorization":"Bearer "+ScriptApp.getOAuthToken()}};  
var url = "https://docs.google.com/spreadsheets/d/"+ ssID + "/export?format=xlsx&id="+ssID+"&gid="+shID;
var result = UrlFetchApp.fetch(url , requestData);  
var contents = result.getContent();
var column = 3;
contents.sort({column: column, ascending:true});
contents.delete({column: column2, column4, column5, column7});

var sheet2 = sh.getSheetByName('StudentList');  
var data = sheet2.getLastRow();
var students = [];
var students = sheet2.getRange(2, 6, data).getValues();  

for (var i=0; i<students.length; i++){ // you are looping through rows and selecting the 1st and only column index
if (students[i][0] !== ''){           
MailApp.sendEmail(students[i][0].toString(), subject ,body, {attachments:[{fileName:sheetName+".xlsx", content:contents, mimeType:"application//xlsx"}]});     
} 
}
}

Answer 1

说明：

我的建议是只创建TempDataSet工作表的副本，并在该工作表上执行所有排序和删除列的操作：

var sh = SpreadsheetApp.getActive();
var sheet = sh.getSheetByName("TempDataSet");
var sheet1 = sheet.copyTo(sh).setName('TempDataSet_temp');
var shID = sheet1.getSheetId().toString();
sheet1.getRange(2, 1, sheet.getLastRow() -1, sheet.getLastColumn()).sort({column: 3, ascending: true}); 
var columns_delete = [7,5,4,2];
columns_delete.forEach(col=>sheet1.deleteColumn(col));

然后您可以在将临时工作表保存在result变量中后删除它

sh.deleteSheet(sh.getSheetByName('TempDataSet_temp'))

---

解决方案：

function sendEmails(){
var sss = SpreadsheetApp.getActiveSpreadsheet();
var ssID = sss.getId();
var sheetName = sss.getName(); 
var sh = SpreadsheetApp.getActive();
var sheet = sh.getSheetByName("TempDataSet");
var sheet1 = sh.insertSheet('TempDataSet_temp');
sheet.getDataRange().copyTo(sheet1.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
sheet.getDataRange().copyTo(sheet1.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_FORMAT, false);

var shID = sheet1.getSheetId().toString();
sheet1.getRange(2, 1, sheet.getLastRow() -1, sheet.getLastColumn()).sort({column: 3, ascending: true}); 
var columns_delete = [7,5,4,2];
columns_delete.forEach(col=>sheet1.deleteColumn(col));

var subject = 'Your Attendance Record at BDU';
var body = 'Dear Student,'+ 'nn' + 'Greetings! Please find the attendance record attached for your reference.' + 'nn' + 'Thank you.';

var requestData = {"method": "GET", "headers":{"Authorization":"Bearer "+ScriptApp.getOAuthToken()}};  
var url = "https://docs.google.com/spreadsheets/d/"+ ssID + "/export?format=xlsx&id="+ssID+"&gid="+shID;
var result = UrlFetchApp.fetch(url , requestData);  
var contents = result.getContent();
sh.deleteSheet(sh.getSheetByName('TempDataSet_temp'))

var sheet2 = sh.getSheetByName('StudentList');  
var data = sheet2.getLastRow();
var students = [];
var students = sheet2.getRange(2, 6, data).getValues();  

for (var i=0; i<students.length; i++){ // you are looping through rows and selecting the 1st and only column index
if (students[i][0] !== ''){           
MailApp.sendEmail(students[i][0].toString(), subject ,body, {attachments:[{fileName:sheetName+".xlsx", content:contents, mimeType:"MICROSOFT_EXCEL"}]});     
} 
}
}