Find Replace google spread sheet app script

Question

I have this very small issue and I was hoping someone could help me solve it. This is the code.

 function textFinder() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var range = sheet.getActiveRange();


var textFinder = range.createTextFinder('=');

var firstOccurrence = textFinder.findNext();


var numOccurrencesReplaced = firstOccurrence.replaceWith("=");
}

When the code runs I get Error
TypeError: Cannot read property 'replaceWith' of null textFinder @ findReplace.gs:11

I am trying to find the = in a function that is in A1 B1 D1

I have to apologize for my poor English skill. Unfortunately, from `I am trying to find the = in a function that is in A1 B1 D1` and your showing script, I cannot understand your goal. Can I ask you about the detail of your question? — Tanaike, Aug 25 '22 at 05:14
Hi my friend, thank you for your question. I'm trying to search the entire sheet for = and replace it with = to make the function refresh. I do know that the function lives in a1 and b1 and d1 but in future I don't know where the = will be so I'll have to scan the whole sheet. Does that help — Johnny Quest, Aug 25 '22 at 05:26
Thank you for replying. From your reply, I proposed an answer. Could you please confirm it? If I misunderstood your goal and that was not useful, I apologize. — Tanaike, Aug 25 '22 at 06:34

score 0 · Accepted Answer · answered Aug 25 '22 at 06:33

I believe your goal is as follows.

You want to refresh all functions in a sheet using Google Apps Script.

In the case of refreshing the functions on Google Spreadsheet using TextFinder, I thought that these samples are useful. Ref1 Ref2 Ref3 But, in this case, I'm worried that in order to refresh all functions in a sheet, it might be a bit difficult. So, in this answer, I would like to propose a sample script for refreshing all functions in a sheet using Google Apps Script.

Sample script:

function myFunction() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const refresh = (a, b) => sheet.createTextFinder(a).matchFormulaText(true).useRegularExpression(true).replaceAllWith(b);
  
  refresh("^=(.+)$", "==$1");
  refresh("^==(.+)$", "=$1");
}

If you want to refresh all functions in all sheets of a Google Spreadsheet, please modify SpreadsheetApp.getActiveSheet(); to SpreadsheetApp.getActiveSpreadsheet();.

Find Replace google spread sheet app script

1 Answers1

Sample script:

References: