Google Scripts Error: "You do not have permission to call sort"

Question

I am trying to run a custom function within Google Sheets. I do not get a compile error but when I try to call the function, I get an #ERROR along with a message,

You do not have permission to call sort

Is there a way to get around this problem? How can I sort a range within a custom function if range.sort is not available?

Here is my code:

function installmentPrice(priceRange, measuringPeriod, lowestDays,     discount) 
{
    measuringPeriod = measuringPeriod || 5;
    lowestDays = lowestDays || 1;
    discount = discount || 1;

    var sheet = SpreadsheetApp.getActiveSpreadsheet();
    var tempRange = sheet.getRange("C3:C9");

    var newRange = tempRange.offset(0,0,measuringPeriod);
    newRange.sort(newRange.getColumn());

    Logger.log(newRange.getValues());

    var lowestSum = 0;
    var installmentPx = 0;

    for (i=0; i<= lowestDays; i++) {
        lowestSum = lowestSum + newRange[i];
    }

    lowestSum = lowestSum/lowestDays;
    installmentPx = discount * lowestSum;
    return installmentPx;

}

Your script works in my spreadsheet. Do you have edit access to the spreadsheet? One thing you may want to fix: the line `var sheet = SpreadsheetApp.getActiveSpreadsheet();` assigns a spreadsheet object to variable `sheet`, which is confusing (and makes it unclear what sheet the subsequent operations are performed on). It should probably be `var sheet = SpreadsheetApp.getActiveSheet();` — , Jan 02 '17 at 22:12
Try this: `sheet.getRange("C3:C9").setValues(sheet.getRange("C3:C9").getValues().sort());` Here, I'm assuming that you want to sort values in C3:C9 range and show it on spreadsheet itself. — Shyam Kansagra, Jan 03 '17 at 05:08
Does this answer your question? [No permission to call msgBox in Google Apps Scripting](https://stackoverflow.com/questions/10506105/no-permission-to-call-msgbox-in-google-apps-scripting) — Rubén, Jun 28 '20 at 00:16

score 1 · Answer 1 · edited Jun 20 '20 at 09:12

As specified in the documentation :

A custom function cannot affect cells other than those it returns a value to. In other words, a custom function cannot edit arbitrary cells, only the cells it is called from and their adjacent cells. To edit arbitrary cells, use a custom menu to run a function instead.

Your function tries to modify a range different from the origin cell, that explains the error you get on the newRange object

Google Scripts Error: "You do not have permission to call sort"

1 Answers1