Workaround: Detecting to Edit Google Spreadsheet using Sheets API with Service Account

submit.md

Workaround: Detecting to Edit Google Spreadsheet using Sheets API with Service Account

This is a workaround for detecting to edit Google Spreadsheet using Sheets API with the service account.

It has already been found that when Google Spreadsheet is edited using Sheets API, this can be detected by the installed OnChange trigger. For example, when a Spreadsheet is edited using Sheets API with the access token retrieved from your Google account, the event object of the installable OnChange trigger includes your email address and nickname. With this information, you can know the user who edited the Spreadsheet. However, when the Spreadsheet is edited using Sheets API with the access token retrieved from the service account, unfortunately, the email of the service account is not included in the event object of the OnChange event. It is considered that this is the current specification.

In this post, I would like to introduce a workaround for detecting editing the Google Spreadsheet using Sheets API with the service account.

Usage

1. Create new Google Spreadsheet.

In order to test this workaround, please create a new Google Spreadsheet.

2. Sample script of Google Apps Script.

Please open the script editor at Google Spreadsheet, and copy and paste the following script to the script editor.

function onChange(e) {
  let { source, user } = e;
  if (!user.email) {
    return;
  }

  // do something.

  // This is a sample script.
  source.getSheets()[0].appendRow([new Date(), JSON.stringify(e)]);
}

function doGet(e) {
  const functionNameOfOnChange = "onChange";
  const { email, nickname } = e.parameter;
  const trigger = ScriptApp.getScriptTriggers().find(
    (t) => t.getHandlerFunction() == functionNameOfOnChange
  );
  if (!trigger)
    return ContentService.createTextOutput("Trigger has not been installed.");
  const obj = {
    authMode: "FULL",
    changeType: "EDIT",
    source: SpreadsheetApp.getActiveSpreadsheet(),
    triggerUid: trigger.getUniqueId(),
    user: { email, nickname },
  };
  onChange(obj);
  return ContentService.createTextOutput("Done.");
}

• In this workaround, when the Spreadsheet is edited by the service account, the event object is recreated by including the email and nickname, and the recreated event object is used in the function onChange.

3. Install OnChange trigger.

Please install OnChange trigger to onChange. Ref

4. Deploy Web Apps.

The detailed information can be seen in the official document.

Please set this using the new IDE of the script editor.

1. On the script editor, at the top right of the script editor, please click "click Deploy" -> "New deployment".
2. Please click "Select type" -> "Web App".
3. Please input the information about the Web App in the fields under "Deployment configuration".
4. Please select "Me" for "Execute as".
5. Please select "Anyone" for "Who has access".
6. Please click "Deploy" button.
7. Copy the URL of the Web App. It's like https://script.google.com/macros/s/###/exec. This URL is used for your HTML.

5. Testing.

In order to edit the Spreadsheet using the service account, in this case, a sample python script is used. The sample script is as follows.

import requests
from googleapiclient.discovery import build
from google.oauth2 import service_account

# Please set your Spreadsheet ID.
spreadsheet_id = "###"

# Please set your Web Apps URL and email and nickname.
url = "https://script.google.com/macros/s/###/exec?email=###.gserviceaccount.com&nickname=serviceAccount1"

creds = service_account.Credentials.from_service_account_file("###", scopes=["https://www.googleapis.com/auth/spreadsheets"])
service = build("sheets", "v4", credentials=creds)
values = [["sample"]]
request = service.spreadsheets().values().update(spreadsheetId=spreadsheet_id, range="Sheet1!A1", valueInputOption="USER_ENTERED", body={"values": values}).execute()
res = requests.get(url)
print(res.text)

When this script is run, sample is put to the cell "A1" of "Sheet1". And, Done. is returned. And, at the Spreadsheet, the following value is appended to the 1st tab. You can see that the email and the nickname of the service account are included.

{
  "authMode": "FULL",
  "changeType": "EDIT",
  "source": {},
  "triggerUid": "###",
  "user": {
    "email": "###.gserviceaccount.com",
    "nickname": "serviceAccount1"
  }
}

Note

• When you modified the Google Apps Script of Web Apps, please modify the deployment as a new version. By this, the modified script is reflected in Web Apps. Please be careful about this.

• You can see the detail of this in my report "Redeploying Web Apps without Changing URL of Web Apps for new IDE (Author: me)".

• The sample script for explaining this workaround is a simple sample script. So, please modify this for your actual situation.

References

• Web Apps
• Taking advantage of Web Apps with Google Apps Script (Author: me)