1

Following on from this which works, I am having trouble with some of the sheet names that have spaces in between them. Aveva Group for example returns The name given to this range is invalid.. Should I just change the name of the tab/sheet or is there a way to solve it the way it is?

function NamedRanges() {
      var spreadsheet = SpreadsheetApp.getActive();
      //put all the sheets here you want to include
      var sheetNames = ["Trainline","Softcat","Avast", "AVEVA Group"];
      var namerng=['Date','Price','Returns','GrossReturns','GeometricReturns',
                  'Risk','NegativeReturns','PositiveReturns','TimeValueMoney'];
      sheetNames.forEach(sh=>{
          sheet = spreadsheet.getSheetByName(sh);
          namerng.forEach((nr,i)=>{
           spreadsheet.setNamedRange(sh+nr, sheet.getRange(1,i+1,sheet.getMaxRows(),1));
          });
      });
};
Draco D
  • 314
  • 1
  • 6
  • 16
  • In the current stage, it seems that the space cannot be used in the name of the named range. So when the name is set to the named range, how about removing the space or replacing the space with other character like `_`? In this case, your goal can be achieved by modifying the script. Also, the space can be removed or replaced from the tab names using a script. Which is the direction you expect? – Tanaike Jan 16 '21 at 03:33
  • I tried `_` character but this failed. I also tried `-` which also failed. So the script path may work. – Draco D Jan 16 '21 at 03:35
  • Thank you for replying. `-` cannot be used for the name of the named range. But `_` can be used for the name of the named range. So in order to correctly understand about your current situation, can you provide the script you tested? – Tanaike Jan 16 '21 at 03:39
  • No problem, I have attached it. It is from the link in the original post – Draco D Jan 16 '21 at 03:42
  • 1
    Thank you for replying. From your added script, I proposed a modified script as an answer. Could you please confirm it? If that was not the result you expect, I apologize. – Tanaike Jan 16 '21 at 03:48

1 Answers1

2

How about the following modification?

From:

spreadsheet.setNamedRange(sh+nr, sheet.getRange(1,i+1,sheet.getMaxRows(),1));

To:

spreadsheet.setNamedRange((sh + nr).replace(/ /g, "_"), sheet.getRange(1,i+1,sheet.getMaxRows(),1)); //  This might be able to be also used. or (sh + nr).replace(/\s/g, "_")
  • In this modification, the space is replaced with _. When you want to replace with other character, please modify the script.

Reference:

Tanaike
  • 181,128
  • 11
  • 97
  • 165