How to get cell edit history by Google App Script?

Question

I have a Google Spreadsheet. When I click right mouse button on any cell I can choose Show edit history from context menu.

After that I can see edit history in popup window.

My question is how can I get this data from the cell via script?

I tried to find solution with inspect option and found expected data here:

<div class="docs-blameview-authortimestamp">
  <div class="docs-blameview-author">My name
  </div>
  <div class="docs-blameview-timestamp">May 9, 11:56 AM
  </div>
</div>

Any suggestions how can I import that data in some Google sheet?

Updated

I am continue looking for the solution.

When I inspect this action (to show edit history) with Chrome DevTools I found on the Network tab this request:

fetch("https://docs.google.com/spreadsheets/d/1xVrzBezPzOmZC7Vap-reuNcWXZx_0qONbZ67pBiHVkQ/blame?token=AC4w5VhQLkaKWPQT2sGl8uO8MkXyW1N7hg%3A1589033535890&includes_info_params=true", {
  "headers": {
    "accept": "*/*",
    "accept-language": "en-US,en;q=0.9",
    "content-type": "multipart/form-data; boundary=----WebKitFormBoundaryFpwmP3acru2z5xQc",
    "sec-fetch-dest": "empty",
    "sec-fetch-mode": "cors",
    "sec-fetch-site": "same-origin",
    "x-build": "trix_2020.18-Tue_RC02",
    "x-client-data": "CI62yQEIpLbJAQipncoBCNCvygEIvLDKAQjttcoBCI66ygEYmr7KAQ==",
    "x-rel-id": "6a4.4ffb56e6.s",
    "x-same-domain": "1"
  },
  "referrer": "https://docs.google.com/spreadsheets/d/1xVrzBezPzOmZC7Vap-reuNcWXZx_0qONbZ67pBiHVkQ/edit",
  "referrerPolicy": "strict-origin-when-cross-origin",
  "body": "------WebKitFormBoundaryFpwmP3acru2z5xQc\r\nContent-Disposition: form-data; name=\"selection\"\r\n\r\n[30710966,[null,[null,[null,\"629843311\",9,1],[[null,\"629843311\",9,10,1,2]]]]]\r\n------WebKitFormBoundaryFpwmP3acru2z5xQc\r\nContent-Disposition: form-data; name=\"clientRevision\"\r\n\r\n478\r\n------WebKitFormBoundaryFpwmP3acru2z5xQc\r\nContent-Disposition: form-data; name=\"includeDiffs\"\r\n\r\ntrue\r\n------WebKitFormBoundaryFpwmP3acru2z5xQc--\r\n",
  "method": "POST",
  "mode": "cors",
  "credentials": "include"
});

I recognized meaning of some parameters. I'm not sure about correction...

---

Query string parameters

/* token: AC4w5VhQLkaKWPQT2sGl8uO8MkXyW1N7hg:1589033535890
     AC4w5VhQLkaKWPQT2sGl8uO8MkXyW1N7hg // Probably it takes from user
     1589033535890 // Timestamp of current session of current user

   includes_info_params: true  // I think that's what gets the user data                                       and editing time of the cell

---

Form data:

/*
selection: 
    [30710966,    // Spreadsheet identificator (Any idea, how to get it???)
    [null,[null,
    [null,
    "629843311",  // Sheet ID in string
    9,            // cell's row - 1
    1],           // cell's column - 1
    [[null,
    "629843311"   // Sheet ID in string
    ,9            // cell's row - 1
    ,10           // cell's row
    ,1            // cell's column - 1
    ,2            // cell's column
    ]]]]]
    
clientRevision: 478   // Edit step number (How to get it???)

includeDiffs: true    // True shows last edition / false - look at previous steps 
*/

I think it is possible to create custom fetch and get blob. Then extract Last editor name and timestamp of last edition of the cell.

Unfortunately, my skill is poor. I'm just learning. Can some one help me with this?

Fretted

@TheMaster Thank you for reply. How do you know? Can you explain, please? – Fretted

Since you're down the rabbit hole, I'll remove my comment. What you're doing is reverse engineering. If this is open source and/or documented, it won't be a problem. But this is proprietory software owned by Google. Despite the legal ground, you also have to consider 1.that this might be completely impossible without encrypted ids, which may be provided by Google's internal software and 2. Even if you figure everything out, Google might change everything in a month or so- do it differently. And you have to do it all again. Is it worth the trouble? – Hage

sites.google.com/site/appsscripttutorial/urlfetch-and-oauth/… – Rapine

Answer 1

Maybe you can get clientRevisions information with Drive API, https://developers.google.com/drive/api/v2/reference/revisions. Spreadsheet ID you said, 30710966 seems common to every spreadsheet. I have a same string for it. You are very close to it, I think. And for the reverse engineering thing, I believe that the information open for everyone should be accessible in many ways. It would be great if google provide some APIs for this, and I believe they are preparing it since this is a relatively new function.

Permanganate

Answer 2

If you are prepared to take up this much trouble, an easier solution is to roll out your own edit history. Simply log every edit via the onEdit trigger (regular or installed). Then retrieve it when necessary.

Vesuvius