google apps script: setNote() from the cell itself

Question

I'd like to set note from a cell—the note should be set to the same cell.

I tried this function

function setNote(note){
    var note = note;
    var cell = SpreadsheetApp.getActiveSheet().getActiveCell();

    if(note == ""){
        cell.clearNote();
    }else{
        cell.setNote(note);
    }
}

but got error You do not have permission to call setNote (line 8), which is obvious, because I can’t edit any cell with a ‘cell function’ (a function that is input/typed in a cell rather than a GAS/JavaScript function itself).

Is there a way to set note from the cell itself (using a ‘cell function’)?

I’d like to be able to set cell value (which I already can) and note (which I can’t yet) from within a cell, like '=if(d4=4, e4, "") & setNote(b4)` … or something like that.

Answer 1

Practically speaking, no. Built-in spreadsheet functions do not create Notes. The two ways to create Notes are: manually and with Apps Script. A custom function can call Apps Script but does not have authorization for any set* methods.

That said, there is a crazy workaround which is probably not worth the effort. A custom function can make GET requests using UrlFetchApp: e.g.,

function myFunction() {
  return UrlFetchApp.fetch("http://example.com").getContentText();
}

works. You can deploy your Apps Script as a web app, authorizing it to act as you (so with your authorization) but be accessible to everyone on the Internet, even anonymous. Put the URL of that web app in the custom function as above. Pass the text of the note and the cell in which it is to be added. For added security, pass some token as a URL parameter which the web app will use to check it's you who's calling it. So the URL will end with

?cell=B12&note=Hello%20World&token=mysecretpassword

Since the web app was authorized to act as you, it will be able to open the spreadsheet and make the change:

function doGet(e) {
  var cellAddress = e.parameter.cell;
  var note = e.parameter.note;
  var token = e.parameter.token;
  if (token == 'mysecretpassword') {
    var ss = SpreadsheetApp.openById('id_here');
    var sheet = ss.getSheetByName('Sheet1');
    var cell = sheet.getRange(cellAddress);
    cell.setNote(note);
  }
}

To make this more widely useful, Spreadsheet id and Sheet name can be sent as URL parameters as well.

Among the potential issues with this approach, such as slow execution, there is also a limit on the number of fetch calls that can be made in a day (20000/day).