Google Sheets API - creating app scripts or attaching them to a Spreadsheet that was created with API

user_1414721 0
Viewed 909

I am using the Google Sheets API to create a spreadsheet. I want to add an onEdit function to this sheet that calls out and does an ajax request.

  1. Using either the app script api or the spreadsheet API I want to create the onEdit function, something like:
// Following comment ensures UrlFetchApp authorization will be prompted:
// UrlFetchApp.fetch()

function onEdit(e) {
  var range = e.range
  var currentSheet = range.getSheet()
  var sheetId = currentSheet.getParent().getId(); 
  var url = 'https://example.com/synchronise?sheetId=' + sheetId;
  var response = JSON.parse(UrlFetchApp.fetch(url).getContentText());
}

and I want to attach that to the sheet I am creating with the API. My app uses Google OAuth so I am hoping I can also deal with the permissions required to make external calls as part of the OAuth authentication process (but happy if when the user opens the sheet it asks them then which I suspect might be the case?)

So my question is what is the appraoch to

a) create a script and attach it to a sheet b) programmatically (or via API) get the user to accept permissions for the script to call out?

What I want to replicate is going to a sheet, clicking tools->script editor and pasting in the function but do that with an API

google-apps-script-api google-apps-script google-sheets-api google-sheets
1 Answers

You can use Apps Script API to add container-bound script.

What to do:

  1. Create a container-bound script using projecs.create method.

To set a container-bound script, you need to assign the spreadsheet file id to the parentId parameter

Sample Request Body:

{
  "title": "new",
  "parentId": "spreadsheet file id"
}
  1. Get the newly created container-bound script id in the project response body of projecs.create method.

Sample Response Body:

{
  "scriptId": "newly created script id",
  "title": "new",
  "parentId": "spreadsheet file id",
  "createTime": "2020-12-25T23:33:48.026Z",
  "updateTime": "2020-12-25T23:33:48.026Z"
}
  1. Update the content of the newly created bound script using projects.updateContent method and include your function.

Sample Request Body:

{
  files: [{
      name: 'hello',
      type: 'SERVER_JS',
      source: 'function helloWorld() {\n  console.log("Hello, world!");\n}'
    }, {
      name: 'appsscript',
      type: 'JSON',
      source: "{\"timeZone\":\"America/New_York\",\"" +
      "exceptionLogging\":\"CLOUD\"}"
    }]
}
user_146060460
Related