How to create a spreadsheet with an associated script?

Question

I'd like to programatically create a batch of spreadsheets which contain different data, but all of which contain a button that is associated with a custom backend function.

For example, each spreadsheet should have a button that, when pressed exports the data to another sheet.

Is such a thing possible?

One idea I had was maybe to create a template that includes the button and associated Apps Script and then make a copy of that spreadsheet and fill it with the custom data.

• Cooper: It is possible to make copies of another spreadsheet which includes it's scripts.
• Alan Wells: If you are trying to avoid having the user need to authorize a script every time that they use a new spreadsheet, then I don't think there is any way to avoid that except with an add-on.
• tehhowch: Save yourself loads of hassle and start right.. use an add-on model for distribution

Answer 1

The Apps-Script API allows you to programmatically create Apps Script projects with the option of binding them to a Google Sheet

• Convert your project into a Cloud Platform project and enable Apps Script API for this project
• Give your projects the necessary scopes in the manifest file
• Incorporate Apps Script API into Apps Script with a Urlfetch request
• Create a new Apps Script Project with Method: projects.create specifying the parentId
• Add contents to the project with Method: projects.updateContent. You can store the contents in a variable and thus add the same content to all of your Apps Script projects.

Sample: JSON file

{
 "timeZone": "America/New_York",
 "oauthScopes": [
 "https://www.googleapis.com/auth/spreadsheets",
 "https://www.googleapis.com/auth/script.projects",
 "https://www.googleapis.com/auth/script.external_request"
 ],
 "dependencies": {
 },
 "exceptionLogging": "STACKDRIVER"
}

.gs file

function createSpreadsheetwithScript() {
 var ss=SpreadsheetApp.create('mySpreadsheet');
 var id=ss.getId();
 var token = ScriptApp.getOAuthToken();
 var url = "https://script.googleapis.com/v1/projects";
 var payload = {
 "title": "myAutoCreatedScript",
 "parentId": id
 }
 var options = {
 "method" : "POST",
 "muteHttpExceptions": true,
 "headers": {
 'Authorization': 'Bearer ' + token
 },
 "contentType": "application/json",
 "payload": JSON.stringify(payload)
 }
 var response = UrlFetchApp.fetch(url,options);
 var scriptId=JSON.parse(response).scriptId;
 var url2="https://script.googleapis.com/v1/projects/"+scriptId+"/content";
 //test content 
 var source="function myFunction() {\n var x=1;\n}";
 var JSONsource="{\"timeZone\":\"America/New_York\",\"exceptionLogging\":\"STACKDRIVER\"}"; 
 var payload2 = {
 "files": [
 {
 "name": "this is the gs. file",
 "type": "SERVER_JS",
 "source": source
 },
 {
 "name": "appsscript",
 "type": "JSON",
 "source": JSONsource,
 "updateTime":"2018-03-04T19:49:08.871Z",
 "functionSet":{
 "values":[{"name":"myFunction"}]}
 }
 ]
}
var options2 = {
 "headers": {
 'Authorization': 'Bearer ' + token,
 }, 
 "contentType": "application/vnd.google-apps.script+json",
 "method" : "PUT", 
 "payload": JSON.stringify(payload2)
 }
 var response2 = UrlFetchApp.fetch(url2,options2); 
}

Make sure to enable the Apps-script API before using it under https://script.google.com/home/usersettings and that your upadteContent request inclusdes a manifest file

More samples

Answer 2

To write a custom function:

Create or open a spreadsheet in Google Sheets.

Select the menu item Tools> Script editor. If you are presented with a welcome screen, click Blank Project on the left to start a new project.

Delete any code in the script editor....

Select the menu item File> Save.... All done!