Add a menu to a newly created Google Spreadsheet

Question

I am trying to add a menu to a newly created Spreadsheet using Google App Script. I don't know if that is even possible. Can I somehow automatically add a script to a new Spreadsheetfile where i can define the onOpen() method? Or can I add the menu from the outside of the Spreadsheet-file, where I have created the file itself?

My code looks like this:

  sheetfile = SpreadsheetApp.create(sheetName);
  sheetfile = DriveApp.getFileById(sheetfile.getId());
  sheetfile = SpreadsheetApp.open(sheetfile);      
  var menuEntries = [];
  menuEntries.push({name: "Choose Folder", functionName: "showPicker"});
  sheetfile.addMenu("Export to JSON-file", menuEntries);

But this does not work. Your help is very much appreciated! :D

Answer 1

You can't insert a script directly when creating a new spreadsheet.

The best way to get this effect is to create template spreadsheet with the necessary script (with the onOpen & your other functions) attached, then create a copy of that template sheet instead of creating a brand new sheet.

When you create the copy, the script will be carried over along with any contents in the sheet.

var template_sheet = SpreadsheetApp.openByUrl('YOUR TEMPLATE SHEET URL');
var sheetfile = template_sheet.copy(sheetName);