0

I'm making script that inserts data to the sheet. But it doesn't work. Returns error: setValues don't have permission. Why?

function addData(type){

/*
Somehow I get 2d array: result
*/

  var arr = [];
  var c = [];
  var i, j;
  for (i = 0;i < result.length-1; i++) {
    c=[];
    for (j = 0; j < result[0].length; j++){
      c.push(result[i][j]);
    }
    arr.push(c);
  }

  var sheet = SpreadsheetApp.getActiveSheet();    
  sheet.getRange(1, 1, i+1, j+1).setValues(arr);
}
Rubén
  • 34,714
  • 9
  • 70
  • 166
Petr Garmashov
  • 53
  • 1
  • 1
  • 7
  • Is the range you're trying to setValue on empty? Or the sheet protected by any chance? – pointNclick Nov 19 '15 at 17:58
  • Array's not empty, I checked through debugger. Sheet's not protected. it's new and even available by url :) – Petr Garmashov Nov 20 '15 at 11:10
  • Available by URL doesn't necessarily mean the sheet is not protected. You could only have view permissions to the sheet. Hence, you might not be able to edit it. – pointNclick Nov 20 '15 at 18:58
  • 1
    i bet your issue is you are calling this as a custom cell function. dont. read the custom function official help. use a menu item or webapp instead – Zig Mandel Dec 01 '15 at 15:12
  • Possible duplicate of [Google apps script error: "You do not have permission to call protect"](http://stackoverflow.com/questions/36365447/google-apps-script-error-you-do-not-have-permission-to-call-protect) – Rubén Sep 12 '16 at 14:42

1 Answers1

0

Ok, so I figured out the problem. Your line:

sheet.getRange(1, 1, i+1, j+1).setValues(arr);

is calling the array to setValue at the positin Row 1 and Column 1 for every value in your result array. Replace it with something like this and you should be good to go:

function addData(){
  var sheet = SpreadsheetApp.getActiveSheet();
  var result = [[1,2,3,4,5], [6,7,8,9,10], [11,12,13,14,15], [16,17,18,19,20]];

  for(var i=0; i<result.length; i++)
  {
    for(var j=0; j<result[0].length; j++)
    {
      sheet.getRange(i+1, j+1).setValue(result[i][j]);
    }
  }

}

The output of my sheet:

enter image description here

So just fix your code according to the suggestion above and your function should work.

PS: I don't know why you're adding type to the function as an argument but I'm assuming it has something to do with getting the result array. If not, and you don't have a use for it I'd suggest to look into removing that as well.

pointNclick
  • 1,584
  • 1
  • 12
  • 17