Google表格脚本:将所有命名范围输出到单元格 [英] Google Sheets Script: Output All Named Ranges to Cells

问题描述

我希望附加到电子表格中的脚本中的函数可以遍历所有命名范围，并在数据"字段的单元格中输入它们.床单.该工作表有很多列表等，供我的主工作表使用(分析").

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").

理想情况下，这将使它们从行1001开始，其中col A为名称"，而col D为范围".(Cols B& C留空以允许Col A的溢出-不想调整Col A的大小)

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.

尽管我掌握了脚本语言的精髓，但是我为此苦苦挣扎，因此，如果有人可以提供示例函数，将不胜感激！MTIA.

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);
}

推荐答案

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);
}

这篇关于Google表格脚本:将所有命名范围输出到单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！