我目前有一个代码(向@Jescanellas喊叫以编写代码),该代码标识特定列(D列)中的唯一值,并在Google表格中创建一个新的电子表格,并将表格标题和与新电子表格中单独工作表中的值。表标题如下;
Ticket Number | Issue | Description | StatusInternal | StatusVendor | Priority | Created Date | Internal Owner | Vendor Owner | Resolution | Target Resolution Date | Comments | ClosedDate
但是,首先,代码不会保留表格的格式,例如边框,字体,对齐方式,标题行颜色等,换句话说,就是纯文本格式。因此,我想知道保留格式的可能性以及如何更改代码以实现它。
其次,我想将由代码创建的新文档以"DocumentName_Date"
的命名约定保存在特定的Google驱动器位置中,我也不知道如何实现。
下面的更新代码
function createSpreadsheets() {
var StatusInternal = ["Open","Hold","Future","Closed"];
//Create the new file
var newSprsheet = Spreadsheetapp.create("Status Internal");
for (var i = 0; i < StatusInternal.length; i++){
//Create the new Sheets with the Status Internal names
newSprsheet.insertSheet(StatusInternal[i]);
}
//activates the first sheet (Sheet1),to delete it,as it is not necessary
newSprsheet.getSheets()[0].activate();
newSprsheet.deleteactiveSheet();
splitData(newSprsheet,StatusInternal);
}
function splitData(newSprsheet,StatusInternal){
var mainSprsheet = Spreadsheetapp.openById("DocID");
var mainSheet = mainSprsheet.getSheetByName("MasterTracker");
//Gets the values of the first row
var headcolumns = mainSheet.getRange("A1:M1").getvalues();
//Gets the values of the Status Internal Column
var StatusValues = mainSheet.getRange("D2:D").getvalues();
var lastRow = mainSheet.getLastRow();
for (var i = 0; i < 4; i++){
//Copies the first row values to each new Sheet
newSprsheet.getSheets()[i].appendRow(headcolumns[0]);
}
for (var i = 2; i <= lastRow; i++){
//Gets all the values of the row
var rowContents = mainSheet.getRange("A"+i+":M"+i).getvalues();
//Appends a new row to the Sheet depending of the value in Column C.
switch(StatusValues[i-2].toString()){
case StatusInternal[0]:
newSprsheet.getSheetByName(StatusInternal[0]).appendRow(rowContents[0]);
break;
case StatusInternal[1]:
newSprsheet.getSheetByName(StatusInternal[1]).appendRow(rowContents[0]);
break;
case StatusInternal[2]:
newSprsheet.getSheetByName(StatusInternal[2]).appendRow(rowContents[0]);
break;
case StatusInternal[3]:
newSprsheet.getSheetByName(StatusInternal[3]).appendRow(rowContents[0]);
break;
}
var newAllsheets = newSprsheet.getSheets();
for (var s in newAllsheets)
{
var sheet = newAllsheets[s];
sheet.setColumnWidth(2,275);
sheet.setColumnWidth(3,275);
sheet.setColumnWidth(10,275);
sheet.setColumnWidth(12,275);
sheet.getRange('A1:M1').activate();
sheet.getactiveRangeList().setBackground('accENT6')
.setfontColor('white');
sheet.getRange('A:M').activate();
sheet.getactiveRangeList().setWrapStrategy(Spreadsheetapp.WrapStrategy.WRAP)
.setVerticalAlignment('middle')
.setHorizontalAlignment('left')
.setfontFamily('Roboto')
.setfontSize(10)
.setBorder(true,true,'#b7b7b7',Spreadsheetapp.BorderStyle.SOLID);
}
}
}
我找到了需要应用于新工作表的属性,并修改了代码。好消息是该格式已正确应用于所有选项卡。但是,执行时间超过8分钟,我看到了此消息
状态未知的执行意味着Apps脚本为 无法确定执行结果。它可能是 执行成功还是失败。
任何帮助您确定错误原因并提高代码效率的帮助都将不胜感激。
提前谢谢
-Al