You Do Not Have Permission To Use Copyto

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_rang

Solution 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.

Solution 2:

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.