Search & Replace within formula of another entire sheet using Google script

Question

I have used a variation of this: How to copy format and values, not formulas, when creating a spreadsheet backup in Google Apps Script?

code here:

function copySheet() {
  var spreadsheetId = "1O6D59wAwzq45fHHPrlZea_cpZsVdlTuct0YgJRD3iyw"; // Please set the source Spreadsheet ID.
  var destFolderId = "18L8k7jtldUQTonp5VO5ZBFd6j_AoC0bj";  // Please set the destination folder ID.

  // Copy each sheet in the source Spreadsheet by removing the formulas as the temporal sheets.
  var ss = SpreadsheetApp.openById(spreadsheetId);
  var tempSheets = ss.getSheets().map(function(sheet) {
    var dstSheet = sheet.copyTo(ss).setName(sheet.getSheetName() + "_temp");
    var src = dstSheet.getDataRange();
    src.copyTo(src, {contentsOnly: false});
    return dstSheet;
  });
  
  // Copy the source Spreadsheet.
  var timeZone = Session.getScriptTimeZone();
  date = Utilities.formatDate(new Date(), timeZone, "YYMMdd");

  var destination = ss.copy(ss.getName() + " - " + date);
  
  // Delete the temporal sheets in the source Spreadsheet.
  tempSheets.forEach(function(sheet) {ss.deleteSheet(sheet)});
  
  // Delete the original sheets from the copied Spreadsheet and rename the copied sheets.
  destination.getSheets().forEach(function(sheet) {
    var sheetName = sheet.getSheetName();
    if (sheetName.indexOf("_temp") == -1) {
      destination.deleteSheet(sheet);
    } else {
      sheet.setName(sheetName.slice(0, -5));
    }
  });

  // Move file to the destination folder.
  var file = DriveApp.getFileById(destination.getId());
  DriveApp.getFolderById(destFolderId).addFile(file);
  file.getParents().next().removeFile(file);
}

function searchReplace() {
  var FILE = SpreadsheetApp.openById("1vb3lv7boclruNyy116CN3oZXeqjUpPl5oFDSakEclDI");
  var textFinder = FILE.createTextFinder('Raw').matchFormulaText(true);
  textFinder.replaceAllWith('Raw');
}

To copy an entire sheet WITH formulas. The problem I have found is that the formulas referencing other sheets/tabs break with "Unresolved Sheet Name" error, even though the sheets do exist.

I have found a workaround here: https://support.google.com/docs/thread/25074318/unresolved-sheet-name-workaround-when-copying-or-renaming-tabs?hl=en

and am hoping to be able to search and replace within the new copied sheet to replace the word "Raw" within the formulas with "Raw" which will fix the broken formulas, all from the original script from the sheet being duplicated.

I hope this makes sense, I'd love to hear any suggestions or workarounds...

Thanks

How are you copying these formulas? If possible, post a snippet that shows that so it's easier for others to help. — mshcruz

ADW ADW · Accepted Answer · 2021-07-15T08:38:24

If you are looking for an apps-script based solution, I answered a similar question here. I also wrote a short tutorial explaining the script. But that one was about replacing text.

You are looking to find-and-replace inside the formula.

To do that you need to tweak my script as follows:

var textFinder = activeSheet.createTextFinder('Raw').matchFormulaText(true);

This will look for the text Raw inside the formulas on the sheet.

To replace it with Cooked, the next line of the script is:

textFinder.replaceAllWith('Cooked');

Please ref my previous answer and tutorial for more.

Search & Replace within formula of another entire sheet using Google script

1 Answers