Apps Script: clean up (make a list of and then delete) #REF Named Ranges

[bthuronyi]

We want a standalone Sheet-contained script that will:

• [ ] Find all Named Ranges in a target Sheet (by URL)
• [ ] Identify the Named Ranges whose range definition is #REF (because the underlying cells were deleted)
• [ ] Output the names of those Named Ranges to a new tab in the script's Sheet (not the target)
• [ ] Modify the target sheet by deleting the #REF Named Ranges

[bthuronyi]

Note that Gemini might just be able to write this from the description above!

[ixf1]

`

//8) Delete Undefined Named Ranges in Target Sheet function deleteUndefinedNamedRanges() { const ss = SpreadsheetApp.getActiveSpreadsheet(); const configSheet = ss.getSheetByName('Config'); const targetSheetUrl = configSheet.getRange('A5').getValue(); const targetSpreadsheet = SpreadsheetApp.openByUrl(targetSheetUrl);

const newSheet = ss.getSheetByName('8) Deleted Undefined Named Ranges') || ss.insertSheet('8) Deleted Undefined Named Ranges'); newSheet.clearContents();

// Get all sheets in the target spreadsheet const sheets = targetSpreadsheet.getSheets();

// Check for #REF! errors and clear content const clearRefErrors = (sheet) => { const dataRange = sheet.getDataRange(); const values = dataRange.getValues(); const formulas = dataRange.getFormulas();

for (let i = 0; i < values.length; i++) {
  for (let j = 0; j < values[i].length; j++) {
    if (values[i][j] === '#REF!') {
      sheet.getRange(i + 1, j + 1).clearContent(); //Contents????
    } else if (formulas[i][j] && formulas[i][j].indexOf('#REF!') !== -1) {
      sheet.getRange(i + 1, j + 1).clearContent(); //Contents????
    }
  }
}

};

// Go thru each sheet sheets.forEach(clearRefErrors);

// Find and remove unreferenced Named Ranges const allFormulas = sheets.reduce((acc, sheet) => { return acc.concat(sheet.getDataRange().getFormulas().flat()); }, []);

const namedRanges = targetSpreadsheet.getNamedRanges();

const unreferencedNamedRanges = namedRanges.filter(namedRange => { const name = namedRange.getName(); return !allFormulas.some(formula => new RegExp('\b' + name + '\b').test(formula)); });

if (unreferencedNamedRanges.length > 0) { const ui = SpreadsheetApp.getUi(); const response = ui.alert('Delete ' + unreferencedNamedRanges.length + ' unreferenced Named Ranges?', ui.ButtonSet.YES_NO); if (response == ui.Button.YES) { const deletedNames = [];

  unreferencedNamedRanges.forEach(namedRange => {
      deletedNames.push(namedRange.getName());

    namedRange.remove();
  });

  // output
  newSheet.getRange(1, 1).setValue("Deleted Undefined Named Ranges in Target Sheet");
  newSheet.getRange(2, 1, deletedNames.length, 1).setValues(deletedNames.map(name => [name]));

  ui.alert(`${unreferencedNamedRanges.length} unreferenced Named Ranges have been removed. Yay!`);
}

} else { SpreadsheetApp.getUi().alert('No unreferenced Named Ranges found! Yay?'); } }

`

DONE

[ixf1]

Debugged deletion of REDUNDANT (not #REF!) named ranges function so that a new sheet (9) is made and the deleted named ranges are output in a column. Matches the code/format of deletion of #REF! named ranges function.