google-apps-scriptgoogle-sheetsgoogle-sheets-custom-function

Dynamic cell position; No permission to call setValues


So I have a sheets that tracks my work hours via IFTTT and I wanted to add a script to my Sheet such that at the end of my work week it can be called and it will put "End Work Week!" All in different columns but in the same row.....However for the IFTTT code to work I need to have a Blank row beneath that so i have been putting ' =" " '. Here is the custom function:

function myRows(w, x, y, z) 
{
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  //Logger.log(x);
  //Logger.log(y);
  var range = sheet.getRange(w, x, y - w + 1, z - x + 1);
  var rangeSetUp = [["Begin", "Work", "Week", "!"], ["=F1",  " ", " ", " "]];
  range = range.getA1Notation(); //Say the range is 1,1,2,4 then this will return a string "A1:D2", which seems to work better on getRange...
  range = sheet.getRange(range);
  range.setValues(rangeSetUp);
}

and in the cell I would have =myRows(ROW(), COLUMN(), (ROW() + 1), (COLUMN() + 3)) And i dont send it static values because the code will be placed into different cells.....the "Array" of values should always be 4 columns wide and 2 rows deep.... No Matter where it is placed, which is why var rangeSetUp is static. I can get the code to fully execute all the way to setValues then I get

#ERROR! You do not have permission to call setValues (line 11).

Any help is welcome. I am the owner of the Sheet Yes.


Solution

  • A custom function cannot use .setValues(). Quote from 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.

    To write values to adjacents cells, just return an array of values and it will automatically fill neighboring values. Refer documentation for more details.

    In your specific case all you need to do is the following:

    function myRows() 
    {
      var rangeSetUp = [["Begin", "Work", "Week", "!"], ["=F1",  " ", " ", " "]];
      return rangeSetUp
    }
    

    So if your cell A3 is '=myRows()' your output would like so
    enter image description here