Trying to copy comments from one google sheet to a copy of the sheet in another location using apps script

Question

Trying to copy Comments from one google sheet to a copy of the sheet in another location.
There doesn't appear to be a copyTo() so I'm trying moveTo()
Looking for more ideas.

Included this in the code below

It works within the same workbook:

// This works
function copyCellAndComments() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Source_Sheet");
  var targetSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Destination_Sheet");
  sheet.getRange("P21:26").moveTo(targetSheet.getRange("P21:26"));
};

// I run into permissions issues if I try something like
function copyCellAndComments02() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Source_Sheet");

  //This didn't work.  Told me formatted wrong
  var targetSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Spreadsheet URL", "Sheetname");

  // I tried using SpreadsheetApp.openById() as well as SpreadsheetApp.openByUrl() in the script and it told me I don't have sufficient permissions to run openById or openByUrl.

    // (Error
Exception: You do not have permission to call SpreadsheetApp.openByUrl. Required permissions: https://www.googleapis.com/auth/spreadsheets )

  // Also tried creating a standalone function and calling the function within the sheet.
function importComments(spreadsheetKey,a1NotationReference) {
    return SpreadsheetApp.openByUrl(spreadsheetKey).getRange(a1NotationReference).getComments();
};

Found something online that said it would run if I added a menu and call it from the menu.

     // That generates an Error - Exception: Invalid argument: url (line 7, file "MyScripts_RickP")

I tried importrange. Thant copies text in the comment but not as a comment.

Also tried doing it as a query wrapped around the importrange. Same result.

This is the test comment I'm trying to copy

  sheet.getRange("P6").moveTo(targetSheet.getRange("P6"));
};

Since I have not found a way to copy the sheet with it's comments intact As Comments (the comment text copies okay but I need to copy it as a comment) to the Archive location.

ziganotschka · Answer 1 · 2019-08-28T11:13:15.540

Problems

getSheetbyName(name) only allows you to retrieve a sheet from the same spreadsheet and requires you to specify the sheet name only, without the spreadsheet URL
The method getComments() for spreadsheets is depricated. There is a feature request for this functionality, but currently it is not implemented

Workaround

If you want to copy a sheet into a different spreadsheet, you can do it as following:

  var targetSheet=SpreadsheetApp.openById('SPREADSHEETID');
SpreadsheetApp.getActive().getSheetByName("Source_Sheet").copyTo(targetSheet);

However, this will not copy the comments.

Same applies to other copy methods like SpreadsheetApp.getActive().copy('This is a copy'); or DriveApp.getFileById(SpreadsheetApp.getActive().getId()).makeCopy();
You can retrieve the comments with the Advanced Drive Service:

var comments=Drive.Comments.list(SpreadsheetApp.getActive().getId());

You can use the retrieved comment to create a comment in the destination file, e.g.:

Drive.Comments.insert({"content":comments.items[0].content, "context":comments.items[0].context, "author":comments.items[0].author}, targetSheet.getId());

Comments.insert allows you to specify different parameters, also the anchor, however the latter is trickier to pass from the original comment to the destination.

Trying to copy comments from one google sheet to a copy of the sheet in another location using apps script

1 Answers1

Problems

Workaround