Error msg from custom function when trying to open another spreadsheet. "You do not have permission to perform that action."

Question

I have a custom function that I want to use to retrieve the cell value in a sheet from another spreadsheet. The two parameters of the function are row and col and they determine the cell to reference. Whenever I try to use the function, I get the error "You do not have permission to perform that action.", pointing to the line with the openByID method. Here is my custom function:

function TEST(row,col) {
    var formDataSpreadsheet = SpreadsheetApp.openById(FORM_DATA_SPREADSHEET_ID);
    SpreadsheetApp.setActiveSpreadsheet(formDataSpreadsheet);
    var formDataSheet = formDataSpreadsheet.getSheetByName(WEBSITE_FORM_DATA_SHEET_NAME);
    formDataSpreadsheet.setActiveSheet(formDataSheet);
    return formDataSpreadsheet  
}

Do I need to publish the FORM_DATA_SPREADSHEET? Any help is greatly appreciated.

Answer 1

Sorry, can't do that in a custom function:

Quote from Google documentation:

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.

Google Documentation - Custom functions