Get Range from GetRangeByName, activate range

0
votes

My goal is to automate adding 10 rows to a Google Sheet used by a nonprofit organization's business and then replicate the formulas sequences needed in the newly created rows. I had all the code working to accomplish the task and prevent users from messing up the spreadsheet formulas when they manually insert rows. However, the code time out due to the number of rows in the spreadsheet with the looping use of getRange(). My new approach is to jump to a named cell as a starting point instead of the the really slow looping cell search.

I have created a name "EndData", read all the stuff I can find online, trialed and errored the syntax for hours to get the named_cell range into myrange and then activate the range on the worksheet...

Here is the current coding attempt (which leaves the cursor at the top of the column and an

"TypeError: Cannot find function getRangeByName in object Sheet. (line 170, file "macros")"

//Get EOD range, select, index up 3 rows to start row insertions
function getEOD() {
 var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
 Logger.log(ss);  //lOG is not helpful, says, "sheet", not SheetName
 var MyRange = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRangeByName("EndData"); 
 Logger.log(MyRange);  //lOG is not helpful, says, "Range", not RangeAddress
 //Activate the named cell, moves with the spreadsheet
 MyRange.activate();
};

Had a new idea after I asked for help, here's the working code that gets the job done:

    //Get EOD range, select, index up 3 rows to start row insertions
    function getEOD() {
      var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
      //Logger.log(ss);  //lOG is not helpful, says, "sheet", not SheetName
      var rg = SpreadsheetApp.getActiveSpreadsheet().getRangeByName('EndData');
      if (rg != null) {
        Logger.log(rg.getNumColumns());
      }
      //Logger.log(rg);  //lOG is not helpful, says, "Range", not RangeAddress
      //Referenced the Named the EOD cell
      //Future, Trying to create a debug status bar  
      //SpreadsheetApp.getActiveSpreadsheet().toast("looking at row" & i "now")
      //Activate the named cell, which moves with spreadsheet growth, down
      rg.activate();
      //Uncomment for testing purposes, places a Yes on the row 4 columns over
      //sheet.setActiveRange(sheet.getRange(0, 4)).setValue('Yes');
      //turned off durning testing, writes in data range with this trial code
      //Reposition from named cell to insert lines location
      ss.getRange(sheet.getCurrentCell().getRow() -2, 1, 1, 
      sheet.getMaxColumns()).activate();  
      //Insert ten lines, copy and paste special formulas only
      Insert_10_Lines()
      //https://stackguides.com/questions/59772934/get-range-from- 
      getrangebyname-activate-range
    };
2
google-apps-scriptgoogle-sheets
Use proper tags. Take the tour and see How to Ask – TheMaster
If your solved your question, add it as a answer in the answer box below and not as a edit to your question. See How to Answer – TheMaster

2 Answers

1
votes

My answer is I have tenacity, I try to limit the variables one at a time to prove how things really work, moved the code that did work in the test script file to a production script file and it didn't work for copy of what I had in test, went back to the test script and it didn't work either, after it had... There are variables in play that seem to be happening at the scripting, savings and running steps that change the interactive responses I am getting. Trial and error again through the production environment came up with a working combination by going to the simples code suggested and getting a combination that works. Here is the the code that is running in product to the main need of my question...

function InsertRows() {
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  //Get range from named cell
  var range = SpreadsheetApp.getActiveSpreadsheet().getRangeByName("EOD");
  if (range != null) {
      Logger.log(range.getNumColumns());
  }
  //Select the cell as a starting point for code to follow
  range.activate();    //start location cell is active and row is selected

Thanks for those that responded for the help! Looks like it is going to take a while to recognize the patterns and figure out what to do to get consistent results in a timely manner...

0
votes

Try this:

function getEOD() {
 var ss=SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
 var MyRange=ss.getRangeByName("EndData"); 
 MyRange.activate();
}