Every time I use getRange in my program (lines 8 and 11), I get an error
Exception: Range not found,
but I cannot figure out why.
I tried using getA1notation() and toString() , but both resulted in the same error.
Code as text:
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];
function rangeAsNote(targCell, targRange) {
//Sets up output string
let rangeAsString = new String();
//Creates new var "cell" and sets it to the inputted range of cells
var cell = sheet.getRange(targCell);
//Sets "rangeAsString" to "targRange" converted to the desired format using rangeToString()
rangeAsString = rangeToString(sheet.getRange(targRange));
//Sets the note of "cell" to "rangeAsString"
cell.setNote(rangeAsString);
return;
}
>Solution :
Let’s start by saying that what you are trying to do is not possible because custom functions in Google Sheets can only return values; they can’t be used to modify the spreadsheet.
Let’s delve into the specific question. The error message you’re encountering is likely due to the cell/range references used as parameters of custom functions in Google Sheets and Google Apps Script. These references return the cell / range values, not the string required by the SpreasheetApp.Sheet.getRange method. Understanding this will help you troubleshoot more effectively.
Instead of
=rangeAsNote(A1, E1:G1)
the simplest solution is to use text values as parameters, as shown below:
=rangeAsNote("A1", "E1:G1")
However, as mentioned on top of these answers, this will not work as you expect due to custom function limitations. Use another method to call the function:
- Use a Custom menu item.
- Assign the function to an image.
- Use a Google Apps Script trigger.
- Run the function from a dialog, sidebar or web application created using the HTML Service.
- Run the function from the Apps Script editor.
This might imply making modifications to the function to pass the required parameters.