CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
c-an
c-an

Reputation: 4120

How to request or get the permission of google spread sheet access in the apps script of other sheet?

I am writing apps script of A spread sheet for my custom functions and trying to get values in B spread sheet from there using openUrl()

However, I got ERROR in A spreadsheet when I use the custom function..

in Google Document, it says

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.

ref: https://developers.google.com/apps-script/guides/sheets/functions

However, it doesn't say anything how to get the permission..

  1. I tried with sharable link to everyone but it didn't work.
  2. I tried with url&scope=https://www.googleapis.com/auth/spreadsheets

Both way didn't work. How can I solve this problem?

enter image description here

Upvotes: 2

Views: 1263

Answers (2)

c-an
c-an

Reputation: 4120

Here's how I solved:

You can't access other spreadsheet files, which means you can't use openById() or openByUrl(). So, there is only one method you can achieve this - using getActiveSpreadSheet() which is the current spreadsheet that the app script belongs to.

If you wanted to hide the original file, then you can consider making a reference file that is sharable for anyone. And your original file is private.

So, You need to make it one more file to share.

  1. original file (private)

Use getactiveSpreadSheet() instead of openById() or openByUrl(). And implement your custom functions. You won't have any problem with accessing the current spreadsheet in this way.

function YOUR_CUSTOM_FUNCTION(val1, val2, ...){
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName("YOUR_SHEET_NAME");

    return getMyValue(val1, val2, ...);
}

function getMyValue(val1, val2, ...){
    var result = 0;

    // TODO: calculate values as you want.

    return result;
}

Use your custom function in the file like this:

=YOUR_CUSTOM_FUNCTION(E5, F6, ...)

You won't get any ERROR.

  1. reference file (sharable)

You can make a reference sheet with using:

=IMPORTRANGE("spread sheet link","sheet_name!range")

This will show access button to get authorization. Once you click, it will make a reference from your original sheet.

*range is something like A:C or A1:Z55.

Upvotes: 0

Sourabh Choraria
Sourabh Choraria

Reputation: 2331

Unfortunately, what you're asking for cannot be done directly. In the same documentation, please scroll all the way down to sharing; that's -

Custom Functions in Google Sheets > Advanced > Sharing.

Here, you'll see the following -

Custom functions start out bound to the spreadsheet they were created in. This means that a custom function written in one spreadsheet can't be used in other spreadsheets unless...

If it suits you, you can make use of the 3 methods that they've listed there to overcome this problem.

Hope this helps!

Upvotes: 2

Related Questions