Google Sheets API: get the name of the tab that was last edited

user_10381177 0
Viewed 868

I'm trying to find out which tab in the spreadsheet was last changed.

I have a google sheet with multiple tabs shared among multiple users. I can detect when a user makes a change like this:

def testChanges(self):
    response = self.service.revisions().list(fileId='some-file-id').execute()
    revs = response.get('revisions',[])
    if not len(revs):
        return
    revID = revs[-1].get('id')
    response = self.service.revisions().get(
                            fileId='some-file-id',
                            revisionId=revID,
                            fields='kind, id, modifiedTime, lastModifyingUser'
                            ).execute()

And I get a response:

{
  "kind": "drive#revision",
  "id": "12",
  "modifiedTime": "2018-09-17T20:10:41.330Z",
  "lastModifyingUser": {
    "kind": "drive#user",
    "displayName": "USER NAME",
    "me": true,
    "permissionId": "08921584708523400585",
    "emailAddress": "email.address@gmail.com"
  }

Now I need to process the tab that the user just changed. How can I figure that out? I've checked Drive documentation and Sheets API, but so far no luck.

google-drive-api google-sheets python
2 Answers

I don't believe this is supported or possible from the REST API. You will probably need to create an installed edit trigger, and use it to log the edit event object (e.g. POSTing it to a URL, appending it to some "sentinel" spreadsheet, etc.). You can combine this with your current revisions method to know who performed the modification (since the user identity is generally restricted information unless you are in the same GSuite domain).

An example, that writes the data to a designated spreadsheet.

function installedEdit(e) {
  const edited = e.range,
        sheet = edited.getSheet();
  const row = [
    new Date(),         // Time of the edit
    e.source.getId(),   // id of the edited workbook.
    e.source.getName(),
    sheet.getName(),    // edited sheet name
    sheet.getSheetId(), // gridid for REST API
    edited.getA1Notation(),
    edited.oldValue !== undefined ? edited.oldValue : "",
    edited.value !== undefined ? edited.value : ""
  ];
  try { row.push(e.user.getEmail()); }
  catch (e) { row.push("Unknown User"); }
  // Log this row somehow
  SpreadsheetApp.openById("some spreadsheet id").getSheetByName("Edit log").appendRow(row);
}

You could create this function in a standalone Apps Script project and programmatically install this function as the edit trigger for various spreadsheets you need to watch.

user_93370710

You may make a for loop to scan all tabs (every few seconds) and store the modifiedTime for all of them in a table. Program the loop to compare the current modifiedTime for each tab with the value you have from the last scan. When there is a mismatch, that's mean the current tab is modified.

Just an idea.

user_82462000
Related