通过评论&建议将Google文档修改为Google表格 [英] Pass Comments & Suggested Edits from a Google Document to a Google Sheet

问题描述

同伴堆垛机。我使用Google Apps脚本（a）捕获Google文档中的所有评论...

...和（b）将它们列在Google表格的列中...

然而，我想知道是否有可能（1）在我的工作表中将单个单元格中的注释数组排列到列中，而不是像现在一样列入单个列中。这是我目前用来抓取评论内容的气体：

  var comments = JSON.parse（Drive.Comments .LIST（ID））; 
 var items = comments.items; 
 
 var string =; 
 for（var i in items）{
 string + ='\\\
'; 
 string + = items [i] .content; 
 
 
 
 
 
 
（2）在我的Google Document-即在文档中锚定最高的评论将出现在工作表列的第一个单元格中。
 
 
 
（3）还包括我的Google文档中的建议修改以及评论。这些可以通过API访问吗？
 
 
 
感谢所有可能帮助您的人！
 
 
 
 
 
 Google文档： https：// docs。 google.com/document/d/1O7zAdkCmxhYihtfJhZ3OGkWfO8UUJ_deoHEYr7rQHW4/edit?usp=sharing  
 
 
 Google Apps脚本： https://script.google.com/macros/ d / 1MgTtU0cKSS_XghRjAMtjZFQAdsbU9SkD_2zx03KVKb1Vy4iBBp3MI2QW /编辑UIV = 2&安培;中旬= ACjPJvHY-vp53Ek1wBR4-W3Q1Ur8dSdyN0g6ZI7n3I48-e7EWyq6v9gY82OAeVNlnpQBbY3ICOzi4PCRtp-pjuqAbH3oePLelcIp-YUPs2FNbB7Cl7CC-AvgnoJPcXCnrO8CrIJEI2v8ns8&安培;溅=是 
 
 
 Google表格： https ：//docs.google.com/spreadsheets/d/1uAtmAO0we7h3HUAFlDBLXlShSLvHVM_W6OSXPwpX_t8/edit？usp = sharing  
 
 
 
 
解决方案（b）将数据注释分散到单个单元格中的列中，而不是放在单个列中，像我现在这样。
 
 
这段代码是将一系列注释，并将它们连接成一个字符串：
 
 
 
 
  var string =; 
 for（var i in items）{
 string + ='\\\
'; 
 string + = items [i] .content; 
 
 $ / code> 
为了能够将每个注释放入列中的单独单元格中，需要将该数组更改为2维数组，每个原始元素都在其自己的行中。例如： 
 
 
  var data = []; //从一个空数组开始
 for（var i = 0; i< items.length; i ++）{
 var item = items [i]; //当前注释
 //一行是一个单元格数组
 var row = [item.htmlContent，item.author.displayName，item.createdDate]; 
 data.push（row）; //将此行添加到数据数组
} 
  
这行写入数据数组的内容尽管使用 setValues（）可以填充一个矩形范围：
 
 
  var targetRange = sheet.getRange（lastRow + 1,1,1,1）.setValues（[[string]]）; 
  
使用之前创建的2-D数组，您可以像这样追加到工作表：
  var targetRange = sheet.getRange（lastRow + 1,1，data.length，data [0]。长度）; 
 targetRange.setValues（data）; 
  

结果：



  function driveApiComment（id）{
 
 var comments = JSON.parse（Drive.Comments.list（id））; 
 var items = comments.items; 
 
 var data = []; //从一个空数组开始
 for（var i = 0; i< items.length; i ++）{
 var item = items [i]; //当前注释
 //一行是一个单元格数组
 var row = [item.htmlContent，item.author.displayName，item.createdDate]; 
 data.push（row）; //将这一行添加到数据数组
} 
 
 var sheet = SpreadsheetApp.openById（submissionSSKey）.getSheets（）[0]; 
 var lastRow = sheet.getLastRow（）; 
 var targetRange = sheet.getRange（lastRow + 1,1，data.length，data [0] .length）; 
 targetRange.setValues（data）; 
 
 $ b $ / code> 





（2）通过我的Google文档中的定位位置发表评论 - 即在文档中锚定最高的评论将出现在表格列的第一个单元格中。

您现在运气不好（现在， 至少）。请参阅：



• 锚文档不存在？


• 在Google文档中以编程方式创建锚定评论

摘要：Google的定位符不可辨认。 （它们可能是一个隐藏数据库的关键，它包括文档的实际行和字符引用，以及社会安全号码和母亲的娘家姓。）您可以检索它们，按字母顺序对它们进行排序......但与注释出现在文档中的位置无关。

（3）还包括建议编辑从我的Google文档旁边的评论。那些可以通过API访问吗？

没有。

Fellow Stackers. I'm using a Google Apps Script to (a) capture all "Comments" in a Google Document... ...and (b) list them in a column of a Google Sheet...

However, I'm wondering if it's possible to...

(1) Array "Comments" into individual cells down a column in my Sheet rather than into a single column, as I have now. This is the bit of GAS I'm currently using to grab comment contents:

 var comments = JSON.parse(Drive.Comments.list(id));
 var items=comments.items;

 var string = "";
  for(var i in items){
    string+='\n';
    string+=items[i].content;
  }

(2) Order the "Comments" by anchor position in my Google Document—i.e. the comment anchored highest in the doc would appear in the first cell of the Sheet's column.

(3) Also include "Suggested Edits" from my Google Document alongside the comments. Can those be accessed via API yet?

Thanks in advance to anyone who may be able to help!

• Google Document: https://docs.google.com/document/d/1O7zAdkCmxhYihtfJhZ3OGkWfO8UUJ_deoHEYr7rQHW4/edit?usp=sharing
• Google Apps Script: https://script.google.com/macros/d/1MgTtU0cKSS_XghRjAMtjZFQAdsbU9SkD_2zx03KVKb1Vy4iBBp3MI2QW/edit?uiv=2&mid=ACjPJvHY-vp53Ek1wBR4-W3Q1Ur8dSdyN0g6ZI7n3I48-e7EWyq6v9gY82OAeVNlnpQBbY3ICOzi4PCRtp-pjuqAbH3oePLelcIp-YUPs2FNbB7Cl7CC-AvgnoJPcXCnrO8CrIJEI2v8ns8&splash=yes
• Google Sheet: https://docs.google.com/spreadsheets/d/1uAtmAO0we7h3HUAFlDBLXlShSLvHVM_W6OSXPwpX_t8/edit?usp=sharing

解决方案

(1) Array "Comments" into individual cells down a column in my Sheet rather than into a single column, as I have now.

This bit of code is taking an array of comments, and concatenating them into a single string:

  var string = "";
  for(var i in items){
    string+='\n';
    string+=items[i].content;
  }

To be able to put each comment into a separate cell in a column, you need to change that array into a 2-dimensional array, with each of the original elements in its own "row". Something like:

  var data = [];           // start with an empty array
  for (var i=0; i<items.length; i++) {
    var item = items[i];   // current comment
    // A row is an array of cells
    var row = [item.htmlContent,item.author.displayName,item.createdDate];
    data.push(row);        // Add this row to the data array
  }

This line writes the content of a single cell, albeit using setValues() which can fill a rectangular range:

var targetRange = sheet.getRange(lastRow+1,1,1,1).setValues([[string]]);

With the 2-D array created earlier, you can append to the sheet like so:

var targetRange = sheet.getRange(lastRow+1,1,data.length,data[0].length);
targetRange.setValues(data);

Result:

function driveApiComment(id){

  var comments = JSON.parse(Drive.Comments.list(id));
  var items=comments.items;

  var data = [];           // start with an empty array
  for (var i=0; i<items.length; i++) {
    var item = items[i];   // current comment
    // A row is an array of cells
    var row = [item.htmlContent,item.author.displayName,item.createdDate];
    data.push(row);        // Add this row to the data array
  }

  var sheet = SpreadsheetApp.openById(submissionSSKey).getSheets()[0];
  var lastRow = sheet.getLastRow();
  var targetRange = sheet.getRange(lastRow+1,1,data.length,data[0].length);
  targetRange.setValues(data);

}

(2) Order the "Comments" by anchor position in my Google Document—i.e. the comment anchored highest in the doc would appear in the first cell of the Sheet's column.

You're out of luck (for now, at least). See:

• How to match comments on an image using kix anchor (or not) in Google Docs
• Anchor documentation does not exist?
• Creating anchored comments programmatically in Google Docs

Summary: Google's anchors are not decipherable. (Likely they are a key to a hidden database that includes the actual line & char refs to your document, along with your social security number and mother's maiden name.) You could retrieve them & sort them alphabetically... but that would have no relation to where the comments appear in a document.

(3) Also include "Suggested Edits" from my Google Document alongside the comments. Can those be accessed via API yet?

Nope.

这篇关于通过评论&建议将Google文档修改为Google表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！