Google Sheet Script not triggered when sheet is edited by service account?

Question

So I have written a gs function that sends an email when someone inserts a new row in the sheet. It works fine when users manually insert data, however that sheet is also used by a service account which inserts a new row through the API, and the edit event is not triggered in that case.

This is the trigger that I'm using

I created the script and the trigger as the owner of the sheet, but that didn't fix anything, so I'm out of ideas.

Answer 1

The only way to trigger a user event with code is to use the Sheets API, with a special setting to set the value as USER_ENTERED And it only works with the "On Change" event. So, you'll need to create a second trigger for "On Change" but you can use the same function name. Although you may need to modify the function to deal with a different event object. Or you could use a different function.

So, your service account will need to run code that uses the Sheets API to set values in your Google Sheet.

You can use either the REST API or the Sheets Advanced Service.

To use the Advance Sheets Service the code would look like the following:

function writeToSheet() {
  id = "Put the Sheet ID here";

  var rowValues = [
    ["one","two"],
  ];

  var request = {
    'valueInputOption': 'USER_ENTERED',
    'data': [
      {
        "range": "Sheet1!A2:B2",
        "majorDimension": "ROWS",
        "values": rowValues,
      },
    ],
  };

  var response = Sheets.Spreadsheets.Values.batchUpdate(request, id);
  Logger.log('response ' + JSON.stringify(response))
}

For the REST API the basic code is as follows:

function writeToSheet() {
  var id,options,range,response,sh,ss,url,values;

  id = 'Put the spreadsheet ID here';
  range = "Sheet1!A1:A1";
  values = {values: [['3','two','nine']]}; // Modified

  url = "https://sheets.googleapis.com/v4/spreadsheets/" +
    id + "/values/" + range + ":append?valueInputOption=USER_ENTERED";

  options = {
    "method":"post",
    "muteHttpExceptions": true,
    "headers": {
      "Authorization": "Bearer " + ScriptApp.getOAuthToken()
    },
    "contentType": "application/json", // Added
    "payload": JSON.stringify(values) // Added
  }

  response = UrlFetchApp.fetch(url,options)
  response = JSON.parse(response);

  //Logger.log('response ' + JSON.stringify(response))
}