Permission to Call SpreadsheetApp.OpenById()

Question

Here is the problem, i need to read the note from a external spreadsheet, so recently i search and found this anwser: Get cell note value but it isn't working with external spreadsheets, i tried a lot of things, but nothing worked, somebody can help me?

Sheet A - Example

Sheet B - Getting value (ImportRange) - Request

Sheet B - Getting value (ImportRange) - Return

Sheet B - Getting Notes(Custom Script) - Request FAIL

function getNote2(cell, token)
{
   var ss = SpreadsheetApp.openById(token)
   var range = ss.getRange(cell)
   return range.getNote();
}

Answer 1

The documentation specifies:

Spreadsheet
Read only (can use most get*() methods, but not set*()).
Cannot open other spreadsheets (SpreadsheetApp.openById() or SpreadsheetApp.openByUrl()).

So unfortunately you will need to come up with another solution

Possible solutions would be

• running the script from the Apps Script editor whereby the values
• Importing the script as a custom macro

• Create a button and assign the script to it

In any case you will need to slightly rewrite your script

• Instead of return you will need to use the method setValue() to set the note into the cell of choice (e.g. the active cell)

Sample script

function callMe(){
  var activeCell = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getActiveCell();
  var cellWithNotes = "A4";
  var id = "PASTE HERE THE SPREADSHEET ID";
  var note = getNote(cellWithNotes, id);
  activeCell.setValue(note);
}


function getNote(cell, token)
{
  var ss = SpreadsheetApp.openById(token)
  var range = ss.getRange(cell)
  return range.getNote();
}

• Run the function callMe() either manually or in another way as described above
• Instead of hardcoding the cellReference and id you can retrieve them dynamically e.g. from a table - depending on your situation