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 · Accepted Answer · 2016-04-25T10:30:08.537

1

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.

edited Apr 25 '16 at 10:30

answered Apr 25 '16 at 09:57

Douglas Gaskell

9,017
9
71
128

`You cannot modify other documents`: I'm modifying the same document, that's what I don't get. I would understand if I modified different spreadsheets, but that's not the case here. – Max Apr 25 '16 at 10:23
1

@Max As far as it's aware, it's a sheet that requires authorization that it cannot retrieve as an anonymous user. The functions can only return data, but cannot use any other services that require authorization to write data. I have edited my repose to reflect that. – Douglas Gaskell Apr 25 '16 at 10:29
@Max A menu is a pretty user friendly way to go about it. If you get the chance, check out custom sidebars and dialogs, they are pretty neat as well. – Douglas Gaskell Apr 25 '16 at 11:04

score 1 · Answer 2 · answered Apr 25 '16 at 23:34

1

Not sure about whether or not your data is persistent in your source spreadsheet, but you could always use the built-in IMPORTRANGE() function. Syntax is:

=IMPORTRANGE("SPREADSHEET_ID","SOURCE_SHEET!RANGE_START:RANGE_END")

Where SPREADSHEET_ID is the ID of the file you're working on.

answered Apr 25 '16 at 23:34

Eric Dauenhauer

710
6
23

Aware of `IMPORTRANGE`, but it doesn't preserve formulas (which is requirement for me, see first line of question) – Max Apr 26 '16 at 06:03

You do not have permission to use copyTo

2 Answers2

Linked