Automated Import Named Range formulas and Named Ranges

[bthuronyi]

It would be great to have a script that can:

• enumerate all the Named Ranges present in a source sheet
• create a new tab in a destination sheet that contains IMPORTANGE formulas that pull the Named Ranges from the source
• create Named Ranges in the destinction sheet for each imported range with the same name name as it

Best case scenario would be to check for and skip existing Named Ranges in the destination rather than making duplicates.

An accompanying script could delete redundant Named Range columns from a sheet if they are not referenced anywhere in its formulas.

[bthuronyi]

A good place to start: create Apps Script that runs from a Sheet; takes a Sheet URL as input, and then outputs all of the Named Range names from that sheet into a new tab.

[ixf1]

Code for "A good place to start: create Apps Script that runs from a Sheet; takes a Sheet URL as input, and then outputs all of the Named Range names from that sheet into a new tab."

@bthuronyi Can you check this code? It works but for some reason there is an error message when I run appscript. If I don't run and just save it, the spreadsheets works fine.

https://docs.google.com/spreadsheets/d/1AcRlupN3HObzV1BYOmc6d-wY8mkPIut9eP3TPLE1clA/edit?gid=1290042982#gid=1290042982

` function onOpen() { const ui = SpreadsheetApp.getUi(); ui.createMenu('Named Range Tools') .addItem('List Named Ranges', 'listNamedRanges') .addToUi(); }

function listNamedRanges() { const ss = SpreadsheetApp.getActiveSpreadsheet(); const configSheet = ss.getSheetByName('Config'); const sourceUrlCell = 'A2';

if (configSheet) { const sourceUrl = configSheet.getRange(sourceUrlCell).getValue(); if (sourceUrl) { try { const sourceSpreadsheet = SpreadsheetApp.openByUrl(sourceUrl); const namedRanges = sourceSpreadsheet.getNamedRanges(); const namedRangeNames = namedRanges.map(range => range.getName());

    const newSheet = ss.getSheetByName('Named Ranges') || ss.insertSheet('Named Ranges');

    namedRangeNames.forEach((name, index) => {
      newSheet.getRange(index + 1, 1).setValue(name);
    });

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

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

[ixf1]

Code for "create a new tab in a destination sheet that contains IMPORTANGE formulas that pull the Named Ranges from the source."

I made sure that the named ranges are inputted on the same row, which is different than the other function, because otherwise the data was all wrong.

` //create a new tab in a destination sheet that contains IMPORTANGE formulas that pull the Named Ranges from the source

function createImportRangeFormulas() { const ss = SpreadsheetApp.getActiveSpreadsheet(); const configSheet = ss.getSheetByName('Config'); const sourceUrlCell = 'A2'; const destSheet = ss.getSheetByName('Imported Ranges') || ss.insertSheet('Imported Ranges');

if (configSheet) { const sourceUrl = configSheet.getRange(sourceUrlCell).getValue(); Logger.log('Source URL: ' + sourceUrl);

if (sourceUrl) {
  try {
    const sourceSpreadsheet = SpreadsheetApp.openByUrl(sourceUrl);
    const namedRanges = sourceSpreadsheet.getNamedRanges();
    const namedRangeNames = namedRanges.map(range => range.getName());

    // Clear the previous content in the sheet
    destSheet.clear();

    namedRangeNames.forEach((name, index) => {
      const formula = `=IMPORTRANGE("${sourceUrl}", "${name}")`;
      destSheet.getRange(1, index + 1).setValue(name);
      destSheet.getRange(2, index + 1).setFormula(formula);
    });

    SpreadsheetApp.getUi().alert('IMPORTRANGE formulas have been created in the sheet "Imported Ranges".');
  } catch (e) {
    SpreadsheetApp.getUi().alert('Error: ' + e.message);
  }
} else {
  SpreadsheetApp.getUi().alert('No URL found in cell ' + sourceUrlCell + '. Please enter the source URL.');
}

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

[ixf1]

[ixf1]

If I am ever to deal with single cell stuff:

/* if (rangeValue.getWidth() === 1 && rangeValue.getHeight() === 1) { if (singleCellColumnIndex === null) { // Find the next empty column singleCellColumnIndex = columnIndex + 1; destSheet.getRange(1, singleCellColumnIndex).setValue('Single-cell Named Ranges'); } const formula ==IMPORTRANGE("${sourceUrl}", "${name}"); destSheet.getRange(2, singleCellColumnIndex).setFormula(formula); } */

[ixf1]

New instructions: Function 1: enumerate all the Named Ranges present in a source sheet (This works!)

Function 2: Create and output onto a column the named ranges (loop thru and get their A1 notation for each of their ranges) Check the A1 notation – they are strings. Check if its full column (are there numbers or not) only want the full columns - REGEX capturing groups (match things A:A, same this separating by : and no numbers) If all of this matches, output the named range (add to array and at end of loop put into destination) - column Call this whole function “get named ranges named to copy!” and give it a menu button Do all this in container (not source/destination)

Function 3 Read in this column and make the importrange and named ranges in row 1 (or 2) like the code i already made. This is second function/menu option

Function 4: Read in list of named ranges to copy from container tab This is a script from stack overflow. For each named range names, get the A1 notation and change the A1 notation

[bthuronyi]

This is a script from stack overflow. For each named range names, get the A1 notation and change the A1 notation

Let's make sure to link and credit them! They helped us figure out the approach conceptually too.

[ixf1]

Agreed! Here is the link to this specific code: https://stackoverflow.com/a/71643831

[ixf1]

@bthuronyi Can we take a look at this together? It seems like it cannot recognize the full column so I'm guessing my regex code isn't working (which is very very likely). https://script.google.com/u/0/home/projects/1InNwR3aGf_VPHuaE3ATKNCZmbiFJWkQZjNE5735GKIWRlst3hhXLXNmw/edit

` function getNamedRangesNamedToCopy() { const ss = SpreadsheetApp.getActiveSpreadsheet();

// Create a new sheet named Full Column Named Ranges let outputSheet = ss.getSheetByName('Full Column Named Ranges'); if (!outputSheet) { outputSheet = ss.insertSheet('Full Column Named Ranges'); }

const namedRanges = ss.getNamedRanges(); const outputData = [];

namedRanges.forEach((namedRange, index) => { SpreadsheetApp.getUi().alert('About to iterate woot');

const range = namedRange.getRange();
const rangeA1 = range.getA1Notation();

// Check if the range is a full column using regex
const fullColumnRegex = /^([A-Z]):([A-Z])$/;
if (fullColumnRegex.test(rangeA1)) {
  outputData.push([namedRange.getName(), rangeA1]);
  SpreadsheetApp.getUi().alert('There is a full column woot');
}

});

// Output the named ranges to the new sheet if (outputData.length > 0) { try { SpreadsheetApp.getUi().alert('About to output data'); outputSheet.getRange(1, 1, outputData.length, 2).setValues(outputData); } catch (error) { SpreadsheetApp.getUi().alert('Error: ' + error.message); } } }`

[bthuronyi]

Really coming along! Yeah, I do think that regex probably needs tweaking. For one thing, it wouldn't match "AB:AB", and it would match A:C which we don't want.

To debug, can we output all the named range names in one column, plus their A1 notation definitions next to them, and whether they matched the regex in a third column? Maybe the range strings aren't what we expect exactly.

In fact, you know what, I bet they have the sheet name in them. Removing the initial ^ might deal with that. If you keep the final $ you're still ok to be matching the end of the string.

[bthuronyi]

Noting that as part of deciding which columns to "copy"/create import functions for, we may later want to not just check for full-column, but ALSO check that the contents of row 3 within that range are not a formula. There may be a built-in method to do this or we can parse the contents (as formula... again need to check the built-in methods to be sure we pull the formula not its output) with a regex like ^\=. But I bet there is something like isFormula() on a cell object.

For now, we can just copy all of these. There's no big problem with having too many columns go into imported-data and I don't think the formula-based columns are more expensive to pull in. Later we can write a tidying up script that removes the importrange functions for unused ranges.

[bthuronyi]

Automatic import now works!

[bthuronyi]

Bug fix in script 5: it was (as expected) changing the range for the one-cell Named Range "settings_NumHeaderRows" to full-column. To address this, allowed it to be changed but then added a line to reset it after the loop. It's hard-coded into the scripts anyway.