Google script setValue

Question

I'm trying to learn how to manipulate with cell in Google spreadsheets with Google script. I have learned, that

Custom functions return values, but they cannot set values outside the cells they are in. In most circumstances, a custom function in cell A1 cannot modify cell A5. However, if a custom function returns a double array, the results overflow the cell containing the function and fill the cells below and to the right of the cell containing the custom function. You can test this with a custom function containing return [[1,2],[3,4]];

So I'm calling function in cell C14

function test(input){
  var secondCell = SpreadsheetApp.getActiveSheet().getRange("C14").setValue("Ahoj");
  return secondCell.getValue();
}

and still getting error

You do not have permission to call setValue

I can't even set data in cell from which I'm calling the function.

Does anybody know why is this not working ?

Edit:

I've read possible duplicate Why can't you use setValue in a custom function?

But this is not solving my problem.

I don't want to edit other cells. I only want to edit the original cell containing the formula. According the quoted text, I should be possible to edit cell, if it's original cell containing the formula. But my example is returning error even though it's accessing only itself.

Kriggs Kriggs · Accepted Answer · 2015-08-26T15:02:01

Your code makes no sense, you set the value progamaticly in C14 to "Ahoj", then get this already know variable, and try to return it to set in the cell that you just tried to set the value to "Ahoj", which would replace the function you just used to run this very function. That's almost a paradox.

Your making a confusion, you can get the value of C14, but that function can't be called in C14, or if you want to set the value of C14 to "Ahoy", in that function all you need is:

function test(input){
  return 'Ahoj';
}

If you want another cell to have the value of C14 then:

function test(input){
  return SpreadsheetApp.getActiveSheet().getRange('C14').getValue();
}

The returned value will be the new cell value, it isn't done trough setValue.

Google script setValue

1 Answers