Google Sheets Script TextFinder FormulaText Doesn't Work with Named Ranges?

Question

I am trying to create a function that modifies formulas in a range that changes bounds over time.

WORKS

var range=ss.getSheetByName('cleaned').getRange("A499:AH540");
sumForm=range.createTextFinder("ss2").matchFormulaText(true);
sumForm.replaceAllWith('ss');

DOESN'T WORK

var range=ss.getRangeByName('cleanedSums');
sumForm=range.createTextFinder("ss2").matchFormulaText(true);
sumForm.replaceAllWith('ss');

Yes, I realize the static defined range is slightly different than the named range definition, but the formulae in question appear in columns E, M, V, & AD.

I created a publicly editable version of the sheet: https://docs.google.com/spreadsheets/d/1v_rYBDz6NWmv5JFghsNl2ScAP3u0fq2QHEmZM5ArvG4/edit

The function in question is ss1(), accessible from UI menu Hide (Create SS1). Any insights would be appreciated.

I am not sure how to go about implementing this functionality without using a silly helper cell at the lower bound of the data, which would eventually become inaccurate or iterating over the rows to find the lower bound each time. Neither are particularly appealing and I am struggling to think of why the TextFinder replacement function would not work on named ranges. Sorry if this belongs on /webapps

It works for me.... If you try function myFunctionTest() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var range=ss.getRangeByName('cleanedSums'); sumForm=range.createTextFinder("ss2").matchFormulaText(true); sumForm.replaceAllWith('ss'); } and run it in a different script you will see that it is working perfectly fine. — soMario
It probably has to do with the rest of your code. You define global variables and you have many menus along with an html file. Check if the rest of the code is correct. — soMario
@Marios thanks, but i am not sure how the other code can be relevant. all other functions fire properly. i reinstantiated the ss variable within the function and it still does not revise the formulas. where did you test this code? it says no one has edited the sheet i shared — macheteYeti
I created a copy of the sheet. I don't want to touch original files. Did you test my code in a separate script to see if it works? this will verify that the code is working properly so you can verify that the issue is different from this code. — soMario
@Marios you're right. it works in a separate script. still don't understand why, but if you post an answer I will accept it for you. Thanks! EDIT: well, it worked once. but now it says that it cannot find the function declared in the secondary script. Any advice there? — macheteYeti

soMario soMario · Accepted Answer · 2021-03-01T16:51:37

Your code works as a separate function.

Most likely your global variables interfered with the script.

function myFunctionTest() {   
  var ss = SpreadsheetApp.getActiveSpreadsheet();   
  var range=ss.getRangeByName('cleanedSums');   
  sumForm=range.createTextFinder("ss2").matchFormulaText(true);   
  sumForm.replaceAllWith('ss'); 
}

Google Sheets Script TextFinder FormulaText Doesn't Work with Named Ranges?

1 Answers