Google Sheets Script: Output All Named Ranges to Cells

Question

I would like a Function in the Script attached to the Spreadsheet, to iterate through all the Named Ranges, and enter them in cells in a "Data" sheet. This sheet has lots of lists etc, which are used by my main sheet ("Analysis").

Ideally, this would put them starting at Row 1001, with col A being the Name, and col D being the Range. (Cols B & C left blank to allow for Overflow from col A - don't want to resize col A)

To be effective, any existing data in the rows previously entered by the Function should first be deleted, before updating with the new data.

Although I'm getting the hang of the Script language, I'm struggling with this, so if anyone could provide an example function, it would be appreciated! MTIA.

[edit] Apologies if my original question was inadequate. I had some code, but it was rubbish, I knew it, so I scrapped it. Almost there now (I think) but one more prob to sort, getting an exception:

 function namedRangeToSpreadsheet() {
// Author: Cooper
// Link: https://stackoverflow.com/a/64841828/190925
// Purpose: Record all NamedRanges by both Name and Range, as a check that 
//          public copy is NOT missing any!
  const ss=SpreadsheetApp.getActive();
  const nrA=ss.getNamedRanges();
  
  var startRow = 1001;  // MH insert from this row
  var endRow = 1200;    // MH clear|insert to a max. of this
  // var targetSh = "Data";   // MH revert to Data ss when tests OK
  var targetSh = "Named Ranges";  

  // MH clear previous data
  let sh=ss.getSheetByName(targetSh); 
  var range = sh.getRange(targetSh + "!A" + startRow + ":D" + endRow);
  range.clear();
  //sh.getRange(sh.getLastRow()+1,1,rowA.length,4).clear();  

  let rowA=[];
  for(let i=0;i<nrA.length;i++) {
    let name=nrA[i].getName();
    let range=nrA[i].getRange().getA1Notation();
    rowA.push([name,'','',range]);
  }
  rowA.sort(); // MH   

  /*  Exception: The number of rows in the data does not match the number of rows in the range. 
  The data has 32 but the range has 200. (line 348, file "Code") */

  //sh.getRange(sh.getLastRow()+1,1,rowA.length,4).setValues(rowA);

  range.setValues(rowA);
}

I cannot understand about your goal. I apologize for this. Can I ask you about the detail of Ideally, this would put them starting at Row 1001, with col A being the Name, and col D being the Range. (Cols B & C left blank to allow for Overflow from col A - don't want to resize col A) To be effective, any existing data in the rows previously entered by the Function should first be deleted, before updating with the new data.? By correctly understanding your goal, I would like to think of the solution. — Tanaike
– Tanaike, Commented Nov 15, 2020 at 5:53

Cooper · Accepted Answer · 2020-11-15 06:42:30Z

2

function namedRangeToSpreadsheet() {
  const ss=SpreadsheetApp.getActive();
  const nrA=ss.getNamedRanges();
  let rowA=[];
  for(let i=0;i<nrA.length;i++) {
    let name=nrA[i].getName();
    let range=nrA[i].getRange().getA1Notation();
    rowA.push([name,'','',range]);
  }
  let sh=ss.getSheetByName('Sheet1');
  sh.getRange(sh.getLastRow()+1,1,rowA.length,4).setValues(rowA);
}

answered Nov 15, 2020 at 6:42

Cooper

64.2k7 gold badges29 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

maxhugen Over a year ago

Thanks Cooper, outputs just as I need it. Two things though, how can I get it start inserting values at Row 1001, and FIRST delete any values between Rows 1001 and 1200?

Cooper Over a year ago

I think I've given enough such that you should be able to implement it into your own code. The days of getting me to add more and more to my code just to get a few measly reputation points are over. If you want it done then first you must try it yourself and if you can't accomplish it on your own then ask another question. I personally don't care if you check the answer or not.

Marios Over a year ago

@maxhugen it is a good idea to ask a new question. This will help both the community and future readers. Cooper has given you more than he should have, given a codeless question. I also answer codeless questions when they are clear enough, but asking for more code when you haven't tried anything by yourself is not the best idea and it is not going to make you a better programmer. That's my personal opinion though. Happy learning :)

Collectives™ on Stack Overflow

Google Sheets Script: Output All Named Ranges to Cells

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related