CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetscustom-function
Lucas Fernando
Lucas Fernando

Reputation: 66

Permission to Call SpreadsheetApp.OpenById()

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 A - Example

Sheet B - Getting value (ImportRange) - Request

Sheet B - Getting value (ImportRange) - Request

Sheet B - Getting value (ImportRange) - Return

Sheet B - Getting value (ImportRange) - Return

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

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();
}

Upvotes: 1

Views: 1871

Answers (1)

ziganotschka
ziganotschka

Reputation: 26796

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

enter image description here

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

Upvotes: 5

Related Questions