I have succesfully managed to learn/put together quite a bit of code reading through a lot of examples, but seem to be stuck on the final step -> writing back the new sheet ID back into my original file.

// 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);
  folder = DriveApp.getFolderById("xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx")

  DriveApp.getFileById(createnew.getId()).moveTo(folder);
  SpreadsheetApp.getActiveSheet().copyTo(createnew);

//
  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();
}

My questions are:

• How to write the file ID (of the new file) back into my source file (sheet "!HOME", cell G9)
• All my new files have an empty "Sheet1" - how to prevent this or delete those by default?

CodePudding user response：

I believe your goal is as follows.

• By modifying your script, you want to put the Spreadsheet ID of the created new Spreadsheet to the cell '!HOME'!G9 of the source Spreadsheet. And, you want to delete the default sheet from the created new Spreadsheet.

In this case, how about putting the following script to the last line of your function?

ss.getRange("'!HOME'!G9").setValue(createnew.getId());
createnew.deleteSheet(createnew.getSheets()[0]);

I thought that by this additional script, your expected 2 goals can be achieved.