You do not have permission to use copyTo

Question

I'm trying to copy a range from one sheet to another (whilst preserving the formulas). I wrote a simple script using copyTo:

function copyRangeAcrossSheets(source_sheet,source_range,target_sheet,target_range) {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var source_sheet = spreadsheet.getSheetByName(source_sheet);
  var target_sheet = spreadsheet.getSheetByName(target_sheet);
  var source_range = source_sheet.getRange(source_range);
  var target_range = target_sheet.getRange(target_range);
  source_range.copyTo(target_range);
}

Which I call as followed:

=copyRangeAcrossSheets("TEST_source","A1:A3","TEST_target","A1:A3")

And I'm getting the below error:

You do not have the permission to call copyTo

I did some digging around and found that functions have to use special triggers (installable) in order to modify another file. However here I'm modifying the same file.

Q1: Why is copyTo failing here?

Q2: How can I workaround the issue without having to define installable triggers? (I just want to copy ranges whilst preserving formulas)

Douglas Gaskell Douglas Gaskell · Accepted Answer · 2016-04-25T09:57:58

Why is it failing?

You cannot modify ~~other~~ any documents that require authorization via a custom function. The reason for this is that your function is executed as an anonymous user, which cannot obtain the necessary authorization to edit other sheets or documents of yours.

Reference: https://developers.google.com/apps-script/guides/sheets/functions#using_apps_script_services

Specific to you is this snippet:

Spreadsheet: Read only (can use most get*() methods, but not set*()). Cannot open other spreadsheets (SpreadsheetApp.openById() or SpreadsheetApp.openByUrl()).

Also:

If your custom function throws the error message "You do not have permission to call X service.", the service requires user authorization and thus cannot be used in a custom function.

How can you work around this?

Write an Apps Script function that is executed via a trigger or manually, you can use onEdit or onChange triggers, or a time-based trigger. You can even manually run the function in the Apps Script IDE when you need to. This is the intended behavior of Apps Script.

You do not have permission to use copyTo

2 Answers