如何通过 Google App Script 获取单元格编辑历史记录? [英] How to get cell edit history by Google App Script?
问题描述
我有一个 Google 电子表格.当我在任何单元格上单击鼠标右键时，我可以从上下文菜单中选择 Show edit history.
之后，我可以在弹出窗口中看到编辑历史记录.
我的问题是如何通过脚本从单元格中获取这些数据?
我尝试使用检查选项找到解决方案，并在此处找到了预期数据:
<div class="docs-blameview-timestamp">5 月 9 日上午 11:56
对于如何将这些数据导入某些 Google 表格中的任何建议?
更新
我正在继续寻找解决方案.
当我使用 Chrome DevTools 检查此操作(以显示编辑历史记录)时，我在网络"选项卡上发现了此请求:
我认出了一些参数的含义.我不确定更正...
<小时>查询字符串参数
/* 令牌:AC4w5VhQLkaKWPQT2sGl8uO8MkXyW1N7hg:1589033535890AC4w5VhQLkaKWPQT2sGl8uO8MkXyW1N7hg//可能需要来自用户1589033535890//当前用户当前会话的时间戳include_info_params: true//我认为这就是获取单元格的用户数据和编辑时间的原因表单数据:
/*选择:[30710966,//电子表格标识符(任何想法，如何获得它???)[空，[空，[空值，"629843311",//字符串中的工作表 ID9,//单元格的行 - 11],//单元格的列 - 1[[空值，"629843311"//字符串中的工作表 ID,9//单元格的行 - 1,10//单元格的行,1//单元格的列 - 1,2//单元格的列]]]]]]clientRevision: 478//编辑步骤号(如何获得???)includeDiffs: true//True 显示最新版本/false - 看前面的步骤*/我认为可以创建自定义获取并获取 blob.然后提取单元格上一版本的最后编辑者名称和时间戳.
不幸的是，我的技能很差.我只是在学习.有人可以帮我解决这个问题吗?
也许你可以通过 Drive API 获取 clientRevisions 信息，https://developers.google.com/drive/api/v2/reference/revisions.您说的电子表格 ID，30710966 似乎对每个电子表格都很常见.我有一个相同的字符串.你离它很近，我想.对于逆向工程，我相信对每个人开放的信息应该可以通过多种方式访问.如果谷歌为此提供一些 API 那就太好了，我相信他们正在准备它，因为这是一个相对较新的功能.
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?
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.
这篇关于如何通过 Google App Script 获取单元格编辑历史记录?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
