CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets-formula
Rick P
Rick P

Reputation: 55

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

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.

Upvotes: 1

Views: 1979

Answers (1)

ziganotschka
ziganotschka

Reputation: 26806

Problems

  1. 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
  2. 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());

Upvotes: 1

Related Questions