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(); — user6655984
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
Does this answer your question? No permission to call msgBox in Google Apps Scripting — Rubén

Serge insas Serge insas · Accepted Answer · 2017-01-03T10:29:26

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 Answers