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>
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./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./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?
Share Improve this question edited May 10, 2020 at 6:09 Timogavk asked May 9, 2020 at 8:22 TimogavkTimogavk 8991 gold badge9 silver badges26 bronze badges 3- @TheMaster Thank you for reply. How do you know? Can you explain, please? – Timogavk Commented May 9, 2020 at 15:18
- Since you're down the rabbit hole, I'll remove my ment. 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 pletely 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? – TheMaster Commented May 9, 2020 at 16:01
- 3 sites.google./site/appsscripttutorial/urlfetch-and-oauth/… – 123 Commented Aug 26, 2020 at 23:28
2 Answers
Reset to default 3Maybe you can get clientRevisions information with Drive API, https://developers.google./drive/api/v2/reference/revisions. Spreadsheet ID you said, 30710966 seems mon 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.
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.