setNamedRange() outside of the spreadsheet contain

2020-03-06 07:26发布

问题:

I've tried as many combinations as I could come up with.

My goal is to have a Google Apps Script running StandAlone or from a Library and be able to set Named Ranges in a spreadsheet.

As best I can figure it, the setNamedRange() method is only available from within the Spreadsheet container and only when you use SpreadsheetApp.getActiveSpreadsheet().

I tried using openById() to no avail. The method is just not available.

Thought I was clever and tried openById then setActiveSpreadsheet. I wasn't clever enough.

Update, I opened issue 1816 "Object become global, auto complete persists even when deleted" with google-apps-script-issues http://code.google.com/p/google-apps-script-issues/issues/detail?id=1816

Quite interesting behavior. Misled me into asking the wrong question

Looks to be a bug in the GAS editor.

回答1:

The following function demonstrates how to set a named range in a standalone script.

function testNamedRange() {
  var ss = SpreadsheetApp.openById('here is the spreadsheet id');
  var range = ss.getRange('Sheet1!A1:B2');
  ss.setNamedRange('TestRange', range);
  var rangeCheck = ss.getRangeByName('TestRange');
  var rangeCheckName = rangeCheck.getA1Notation();
}

The rangeCheckName variable contains the A1:B2 string.