Apps Script: Change Named Ranges in target sheet

[bthuronyi]

We want a sheet-contained script that will:

• [ ] Read in a column of Named Range names ("oldnames") from a tab in its containing sheet
• [ ] Check that a target sheet (specified by URL) contains each of those Named Ranges -- if not, exit with error ("Target sheet does not have a Named Range called ###! Quitting")
• [ ] Read in a column of new Named Range names ("newnames", one per oldname)
• [ ] If there is no newname for each oldname, exit with error
• [ ] Step through the list of oldnames and newnames
  • [ ] Rename the target sheet's oldname Named Range to newname
  • [ ] add "renamed" to a new array
  • [ ] Output the array of "renamed" values in a new column alongside the oldname and newnames columns
  • [ ] Done

[bthuronyi]

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

[ixf1]

See function 6) in https://docs.google.com/spreadsheets/d/1P--8Wu_-ljclJkdBpfuK5NKSgs03ZT783T6O7emFBbI/edit?gid=473209585#gid=473209585

Almost done; I need to add headers and instructions to put the new named in Column C

` //6) Rename Named Ranges in Target Sheet function renameNamedRanges() { identifyFullColumnRanges(); var ss = SpreadsheetApp.getActiveSpreadsheet(); var importedRangesSheet = ss.getSheetByName("2) Full-Column Ranges");

var configSheet = ss.getSheetByName("Config"); var targetSheetUrl = configSheet.getRange("A2").getValue(); //formerly A4 but I feel like we don't need it?

// Get old and new named range names var oldNamesRange = importedRangesSheet.getRange("A1:A"); var oldNames = oldNamesRange.getValues().flat().filter(String);

var newNamesRange = importedRangesSheet.getRange("D1:D"); // MANUAL input var newNames = newNamesRange.getValues().flat().filter(String);

// Check if newNames length matches oldNames length if (oldNames.length !== newNames.length) { throw new Error("Number of old names and new names must match! Old length is " + oldNames.length + " and new length is " + newNames.length); }

if (oldNames.length == newNames.length) { Logger.log("GREAT! Old length " + oldNames.length + " matches new length " + newNames.length); }

// Open the target spreadsheet var targetSpreadsheet = SpreadsheetApp.openByUrl(targetSheetUrl);

// Check if all old names exist in the target sheet if (!targetSpreadsheet) { throw new Error("Failed to open targetsheet"); }

for (var i = 0; i < oldNames.length; i++) { var oldName = oldNames[i]; var namedRange = targetSpreadsheet.getRangeByName(oldName); if (!namedRange) { throw new Error("Target sheet does not have a Named Range called " + oldName + "! Quitting!"); } }

// Rename named ranges and create renamed array var renamed = []; for (var i = 0; i < oldNames.length; i++) { var oldName = oldNames[i]; var newName = newNames[i];

// Get the NamedRange object
var namedRange = targetSpreadsheet.getNamedRanges().filter(function (namedRange) {
  return namedRange.getName() === oldName;
})[0];

// Rename the NamedRange
namedRange.setName(newName);

renamed.push(newName);

}

// Output new names in column E in 2) Full-Column Ranges var outputRange = importedRangesSheet.getRange(1, 5, renamed.length, 1); var renamedValues = renamed.map(function (name) { return [name]; }); outputRange.setValues(renamedValues);

/ // Output renamed values to 4) Full-Column Imported Ranges sheet var outputRange = importedRangesSheet.getRange("A1:A" + "namedRange.length"); outputRange.setValues(renamed.map(function(name) { return [name]; })); /

}`

[ixf1]

Added a 6.5) that is like 2). They list existing full-column named ranges.

`

//6) Rename Named Ranges in Target Sheet function renameNamedRanges() { identifyFullColumnRanges(); var ss = SpreadsheetApp.getActiveSpreadsheet(); identifyFullColumnRangesForTarget(); var importedRangesSheet = ss.getSheetByName("6.5) Full-Column Ranges"); // issue with using 2) Full-Column Ranges is that it uses the source sheet and not the target sheet... so I just copied it

var configSheet = ss.getSheetByName("Config"); var targetSheetUrl = configSheet.getRange("A5").getValue();

// Get old and new named range names var oldNamesRange = importedRangesSheet.getRange("A1:A"); var oldNames = oldNamesRange.getValues().flat().filter(String);

var newNamesRange = importedRangesSheet.getRange("D1:D"); // MANUAL input var newNames = newNamesRange.getValues().flat().filter(String);

// Check if newNames length matches oldNames length if (oldNames.length !== newNames.length) { throw new Error("Number of old names and new names must match! Old length is " + oldNames.length + " and new length is " + newNames.length); }

if (oldNames.length == newNames.length) { Logger.log("GREAT! Old length " + oldNames.length + " matches new length " + newNames.length); }

// Open the target spreadsheet var targetSpreadsheet = SpreadsheetApp.openByUrl(targetSheetUrl);

// Check if all old names exist in the target sheet if (!targetSpreadsheet) { throw new Error("Failed to open targetsheet"); }

for (var i = 0; i < oldNames.length; i++) { var oldName = oldNames[i]; var namedRange = targetSpreadsheet.getRangeByName(oldName); if (!namedRange) { throw new Error("Target sheet does not have a Named Range called " + oldName + "! Quitting!"); } }

// Rename named ranges and create renamed array var renamed = []; for (var i = 0; i < oldNames.length; i++) { var oldName = oldNames[i]; var newName = newNames[i];

// Get the NamedRange object
var namedRange = targetSpreadsheet.getNamedRanges().filter(function (namedRange) {
  return namedRange.getName() === oldName;
})[0];

// Rename the NamedRange
namedRange.setName(newName);

renamed.push(newName);

}

// Output new names in column E in 2) Full-Column Ranges var outputRange = importedRangesSheet.getRange(1, 5, renamed.length, 1); var renamedValues = renamed.map(function (name) { return [name]; }); outputRange.setValues(renamedValues);

/ // Output renamed values to 4) Full-Column Imported Ranges sheet var outputRange = importedRangesSheet.getRange("A1:A" + "namedRange.length"); outputRange.setValues(renamed.map(function(name) { return [name]; })); /

} //6.5) almost the same as 2) Identify Full-Column Ranges but uses A5 and not A2 function identifyFullColumnRangesForTarget() {

const ss = SpreadsheetApp.getActiveSpreadsheet(); const configSheet = ss.getSheetByName('Config'); const targetUrlCell = 'A5';

if (configSheet) { const targetUrl = configSheet.getRange(targetUrlCell).getValue(); if (targetUrl) { try { const sourceSpreadsheet = SpreadsheetApp.openByUrl(targetUrl); const namedRangesList = sourceSpreadsheet.getNamedRanges(); const newSheet = ss.getSheetByName('6.5) Full-Column Ranges') || ss.insertSheet('6.5) Full-Column Ranges'); newSheet.clearContents();

    const outputData = [];
    outputData.push(['Named Range', 'Range', 'Enter Replacement Names for 6) Below']);
    namedRangesList.forEach((namedRange, index) => {
      const range = namedRange.getRange();
      const rangeA1 = range.getA1Notation();

      const fullColumnRegex = /([A-Z]+):\1$/; //should match A:A but not A:B 
      if (fullColumnRegex.test(rangeA1)) {
        outputData.push([namedRange.getName(), rangeA1, '']); // Add an empty string for the third column
      }
    });
    // Output the named ranges to the new sheet
    if (outputData.length > 0) {
      try {
        // newSheet.getRange(1, 1, outputData.length, 3).setValues(outputData); 
        const dataRange = newSheet.getRange(1, 1, outputData.length, 3);
        dataRange.setValues(outputData);

        // Format the header row
        const headerRange = dataRange.offset(0, 0, 1, 3);
        headerRange.setFontWeight('bold');

        // Adjust column widths
        newSheet.autoResizeColumns(1, 3);
      } catch (error) {
        SpreadsheetApp.getUi().alert('Error: ' + error.message);
      }
    }

    SpreadsheetApp.getUi().alert('Named Ranges have been listed in the sheet "6.5) Full-Column Ranges"!');
  } catch (e) {
    SpreadsheetApp.getUi().alert('Error: ' + e.message);
  }
} else {
  SpreadsheetApp.getUi().alert('No URL found in cell ' + targetUrlCell + '. Please enter the source URL.');
}

} else { SpreadsheetApp.getUi().alert('Config sheet not found. Please create a sheet named "Config" and enter the target URL in cell ' + targetUrlCell + '.'); } setRobotoMonoForAllSheets(); }

`