GoogleSheet Script cell value setting

Question

In Google sheets Script, I'm trying to copy value from one cell in Sheet A to another cell in sheet B.

The problems is, when I start avgUtilisationPerWeek() function in oogle Sheets script editor, the code is doing the job, it sets the values of cells as it is defined.

But when I call the function from the target sheet with formula =avgUtilisationPerWeek(), it reports #ERROR

You do not have permission to call setValue (line 108).

In the custom function, I'm trying to set the value of other cells in the sheet, not just the cell where the formula is placed.

This is the source code:

function avgUtilisationPerWeek(){
    for(var i = 1; i < 14; i++) {
        Utilities.sleep(3000);
        SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Dynamic Weekly Utilisation Report').getRange('D1').setValue(i);
        t1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Dynamic Weekly Utilisation Report').getRange('L4').getValue();
        SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Graph Q1 2017').getRange((String.fromCharCode('A'.charCodeAt(0) + i)).concat('24')).setValue(t1);

        v = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Dynamic Weekly Utilisation Report').getRange('L5').getValue();
        SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Graph Q1 2017').getRange((String.fromCharCode('A'.charCodeAt(0) + i)).concat('29')).setValue(v);

        g = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Dynamic Weekly Utilisation Report').getRange('L6').getValue();
        SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Graph Q1 2017').getRange((String.fromCharCode('A'.charCodeAt(0) + i)).concat('34')).setValue(g);

        t2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Dynamic Weekly Utilisation Report').getRange('L7').getValue();
        SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Graph Q1 2017').getRange((String.fromCharCode('A'.charCodeAt(0) + i)).concat('39')).setValue(t2);
  }
}

I read this articles, but didnt find the solution.

Google Script setValue permission

Transferring cell data in Google Script?

Answer 1

Google's guidelines regarding the use of formulas inside a sheet specifically states:

Read only (can use most get*() methods, but not set*()). Cannot open other spreadsheets (SpreadsheetApp.openById() or SpreadsheetApp.openByUrl()).

and:

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.

To use a service other than those listed above, create a custom menu that runs an Apps Script function instead of writing a custom function. A function that is triggered from a menu will ask the user for authorization if necessary and can consequently use all Apps Script services.

Therefore as suggested by David, you should use a custom menu, and not a formula.

Answer 2

I don't think you'll be able to launch it from the sheet - unless I'm wrong. What should work for you is creating a separate function onOpen() to get a custom menu which allows you to run your function straight from the sheet. Would the following work for you?

Detailed below:

function onOpen() {
var ui = SpreadsheetApp.getUi();

  ui.createMenu('Custom Functions')
      .addItem('Copy Function', 'avgUtilisationPerWeek')
      .addToUi();

  SpreadsheetApp.flush();    
}

Your code is the same below but I just cleaned it up a bit for my own visualisation purposes, it's optional to do this.

function avgUtilisationPerWeek(){

    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet1 = ss.getSheetByName('Dynamic Weekly Utilisation Report');
    var sheet2 = ss.getSheetByName('Graph Q1 2017');

  for(var i = 1; i < 14; i++) {
    Utilities.sleep(3000); 

    sheet1.getRange('D1').setValue(i);
    t1 = sheet1.getRange('L4').getValue();
    sheet2.getRange((String.fromCharCode('A'.charCodeAt(0) + i)).concat('24')).setValue(t1);

    v = sheet1.getRange('L5').getValue();
    sheet2.getRange((String.fromCharCode('A'.charCodeAt(0) + i)).concat('29')).setValue(v);

    g = sheet1.getRange('L6').getValue();
    sheet2.getRange((String.fromCharCode('A'.charCodeAt(0) + i)).concat('34')).setValue(g);

    t2 = sheet1.getRange('L7').getValue();
    sheet2.getRange((String.fromCharCode('A'.charCodeAt(0) + i)).concat('39')).setValue(t2);
  }
}