Using AppScript to Automatically Create Named Ranges From Your Dataset

If you’re into Named Ranges but hate that Google doesn’t let you create them automagically, then you’ve come to the right place.

Below is a pretty simple script that takes the active sheet and creates a Named Range for each column. It first checks to see if any header starts with a number. If they do, an ‘R’ is prefixed to keep them compliant with Google Sheets named range’s syntax.

After that, for each header that’s not empty, it creates a range that corresponds to the entire column below that header and then uses that header’s name as the name for the named range.

Just paste it into your AppScript section for your Google Sheet, save it and click run.

You will probably have to accept a permissions popup, but once you do, you should be good to go.

After you run the script, every column in your sheet should now have its own named range.

Thanks to Steph Smith, who brought this issue to light in her Spreadsheet Wizardry webinar, and thanks to Perplexity for doing all the heavy lifting. I hope this helps make someone’s life easier.

The AppScript Code

function createNamedRanges() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
  var range;

  for (var i = 0; i < headers.length; i++) {
    var header = headers[i].toString().replace(/[^a-zA-Z0-9]/g, '_'); // Sanitize header
    if (header.match(/^\d/)) header = 'R' + header; // Prefix headers that start with a number
    if (header) { // Check if the header is not empty
      range = sheet.getRange(2, i + 1, sheet.getLastRow() - 1, 1);
      try {
        SpreadsheetApp.getActiveSpreadsheet().setNamedRange(header, range);
      } catch (e) {
        Logger.log('Failed to create named range for header: ' + headers[i] + ' with sanitized name: ' + header);
        Logger.log(e.message);
      }
    }
  }
}