April 24, 2010
What I initially had in mind was something like this:
Where myCustomFunction() is:
But as stated above this doesn’t work. The following error message is displayed: You do not have permission to call setValue. The reason as to why it’s not allowed is beyond me but the same question was asked on the Google support forums where a Google employee simply stated it wasn’t possible. The workarounds that he suggested were “…running your script inside the editor, or from a drawing…“.
I didn’t really find that to be neat enough so my workaround as stated before was to modify the function to retrieve the values itself and do its thing with the output cells. Google provides excellent tutorials on how to accomplish both things.
When you want to know the last index that contains data you can do it like so:
sheet.getDataRange().getLastRow(). However if you have a Data validation on a column, it seems
as if that defines that every cell has a value, even if the cells are all empty. This makes the
above function call return the number 101 (the default number of rows). When I removed the data
validation from the column it returned the right index.
You can’t sort individual columns apart from others. You can freeze up to the first 5 rows and columns, meaning they won’t be affected by the sorting. Beyond that I guess the only solution is to spread to multiple sheets.