Google Sheet脚本技术问题：新文件ID回写与默认Sheet删除

问题1：将新创建文件的ID写入源文件「!HOME」工作表G9单元格 #

在创建新文件createnew后，直接利用脚本开头已定义的源文件对象ss，获取新文件ID并写入指定单元格即可，添加一行代码实现：

// 写入新文件ID到!HOME的G9单元格
ss.getSheetByName('!HOME').getRange('G9').setValue(createnew.getId());

问题2：删除新文件默认的空「Sheet1」 #

新文件创建后默认生成「Sheet1」，在将目标工作表复制到新文件后（此时新文件已有有效工作表，满足至少保留一个工作表的要求），即可安全删除默认工作表，添加以下代码：

// 删除新文件中的默认Sheet1
var defaultSheet = createnew.getSheetByName('Sheet1');
if (defaultSheet) createnew.deleteSheet(defaultSheet);

修改后的完整脚本 #

// get filename for new file from sheet "!HOME", cell G8
// duplicate sheet "SVY" and rename to above file name

function savesvy() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('!HOME');  
  var cell = sheet.getRange("G8");
  var value = cell.getValue();
  ss.setActiveSheet(ss.getSheetByName('SVY'),true); 
  ss.duplicateActiveSheet().setName(value)

// copy+paste values in new sheet to remove all calculations

  var spreadsheet = SpreadsheetApp.getActive();
  var sheet = spreadsheet.getActiveSheet();
  sheet.getRange(1, 1, sheet.getMaxRows(), sheet.getMaxColumns()).activate();
  sheet = spreadsheet.getActiveSheet();
  sheet.getRange(1, 1, sheet.getMaxRows(), sheet.getMaxColumns()).copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  SpreadsheetApp.getActiveSpreadsheet().moveActiveSheet(2);

  pausewait()

// SAVE A COPY TO TEAM DRIVE AND CORRECT FOLDER WITH SAME NAME AS NEW SHEET

  var sheetname = SpreadsheetApp.getActiveSheet().getSheetName();
  var createnew = SpreadsheetApp.create(sheetname);
  // 写入新文件ID到源文件!HOME的G9单元格
  ss.getSheetByName('!HOME').getRange('G9').setValue(createnew.getId());
  
  folder = DriveApp.getFolderById("xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx")

  DriveApp.getFileById(createnew.getId()).moveTo(folder);
  SpreadsheetApp.getActiveSheet().copyTo(createnew);
  
  // 删除新文件中的默认Sheet1
  var defaultSheet = createnew.getSheetByName('Sheet1');
  if (defaultSheet) createnew.deleteSheet(defaultSheet);

//
  pausewait()

// DELETE COPIED SHEET IN TOOL

SpreadsheetApp.getActive();
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  ss.deleteSheet(ss.getSheets()[1]);

  pausewait()

// JUMP BACK TO "!HOME" SECTION

var spreadsheet = SpreadsheetApp.getActive();
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('!HOME'), true);
spreadsheet.getRange('A1').activate();
}

内容的提问来源于stack exchange，提问作者Tim Harris