0

In google sheets, I am trying to return an array of results to an array of cells with a single custom function. Much like the "=IMPORT..." or "=QUERY" native functions. Thanks to user Bardy and this original post, I have developed the following script.

function asset() {  
  var s = SpreadsheetApp.getActiveSpreadsheet()
  var ss = s.getActiveSheet()
  var sc = ss.getActiveCell()
  var items = [{Description:'Well',ItemId:'1'},
{Description:'House',ItemId:'2'}];

  var outputRows = []
  var headings = ["Description","ItemId"]

  items.forEach(function(item){
    outputRows.push(headings.map(function(heading){
      return item[heading] || '';
     }));
  });

  if (outputRows.length){
    outputRows.unshift(headings);
SpreadsheetApp.getActiveSheet().getRange(1,1,outputRows.length,outputRows[0].length).setValues(outputRows);
  }
}

The scrip works like a charm when I run it, but if I try to call the function from the spreadsheet, I get the vicious

'ERROR:You do not have permission to call setValues (line 18).'

I've scanned the blogs and checked just about every help file, and something isnt connecting. I think it has something to do with the way I try to activate the cell, but what am I missing?

Note i am aware of similar posts like "No Permission to call setValues() - Google Apps Script, JSON data [duplicate]" (marked as a dupliacte of "No permission to call msgBox in Google Apps Scripting ") however i believe my application is a bit different. I AM trying to allow end users to drop the results anywhere in the sheet based on the position of the entered formula.

I think it has something to do with the way I try to activate the cell. I cant seem to figure out how to make it relative to the location of the entered formula. What am I missing?

ACrizzle
  • 3
  • 1
  • 3

1 Answers1

3

When you run the function from the script editor you can use the function .setValues() . However, when you using the function as custom function, you can only set values to the cell that has the custom function or its neighbouring cells. This answer by Ruben pretty much sums up how to set values when using custom function.

Similarly in your case instead of using setValues() you just return the array. Like so:

function asset() {  
  var s = SpreadsheetApp.getActiveSpreadsheet()
  var ss = s.getActiveSheet()
  var sc = ss.getActiveCell()
  var items = [{Description:'Well',ItemId:'1'},
{Description:'House',ItemId:'2'}];

  var outputRows = []
  var headings = ["Description","ItemId"]

  items.forEach(function(item){
    outputRows.push(headings.map(function(heading){
      return item[heading] || '';
     }));
  });

  if (outputRows.length){
    outputRows.unshift(headings);
    return outputRows
  }
}
Jack Brown
  • 5,802
  • 2
  • 12
  • 27