same function works in one script but doesn’t work in another script although copy pasted :(

I wrote a function that works in one application script for one spreadsheet and then copy pasted it into another application script for another spreadsheet where it doesn’t work and I get error: Error
Exception: The number of rows in the range must be at least 1. (anonymous) @ Code.gs:5

Function is:

function validate() 
{
  var ss1=SpreadsheetApp.getActiveSpreadsheet();
  var sht1=ss1.getSheets()[0]; // sheet where to create dropDown list
  var ss4imp=SpreadsheetApp.openById('1wOo-ntaLOIcDrFuB9y3WwAOsRm1GyAFOcIacCewQfUo');
  var sht4imp=ss4imp.getSheets()[0]; // sheet within the program containing the list of dropDown items
  var sht2=ss1.getSheets()[1]; // sheet within the program containing the list of dropDown items
  var lastRowOfImpItems = sht4imp.getLastRow();
  Logger.log(lastRowOfImpItems);
  var rng4=sht4imp.getRange(1,1,lastRowOfImpItems,1).getValues(); // range on a sheet outside the program containing the list of dropDown items
  var rng1=sht1.getRange('A1'); // range (cell) where to create dropDown
  var rng2=sht2.getRange(1,1,lastRowOfImpItems,1).setValues(rng4); // range on a sheet within the program containing the list of dropDown items
  var rule = SpreadsheetApp.newDataValidation().requireValueInRange(rng2).build();
  rng1.setDataValidation(rule);
}

And works well in a simple .gs project where this function is the only function in .gs project for sheet: https://docs.google.com/spreadsheets/d/1cEpLk-jEgWCIfSwzEj5X0n3Q1OOJ_5MJr5vT257BHgI/edit?usp=sharing

But when I copy paste it in another .gs project where it is not the only function in that project it stops working and throws an error:

Error
Exception: The number of rows in the range must be at least 1. (anonymous) @ Code.gs:5

This happens in a script:

  var active_spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = SpreadsheetApp.getActiveSheet();
  var cell = sheet.getActiveCell();
  var lastRow = sheet.getLastRow();

function onOpen() 
{
  var cellRow = cell.getRow();
  var cellColumn = cell.getColumn();
  var cellValue = cell.getValue();
     sheet.getRange('O2').setValue('Klik tu in izberi akcijo')
     .setFontColor("red")
     .setFontWeight("bold")
     .setBorder(true, true, true, true, true, true);
}
function validate() 
{
// var ss1=SpreadsheetApp.getActiveSpreadsheet();
// var sht1=ss1.getSheets()[0]; // sheet where to create dropDown list
  var ss4imp=SpreadsheetApp.openById('1wOo-ntaLOIcDrFuB9y3WwAOsRm1GyAFOcIacCewQfUo');
  var sht4imp=ss4imp.getSheets()[0]; // sheet within the program containing the list of dropDown items
  var sht2=active_spreadsheet.getSheets()[1]; // sheet within the program containing the list of dropDown items
  var lastRowOfImpItems = sht4imp.getLastRow();
  Logger.log(lastRowOfImpItems);
  var rng4=sht4imp.getRange(1,1,lastRowOfImpItems,1).getValues(); // range on a sheet outside the program containing the list of dropDown items
  var rng1=sheet.getRange(2,2,1,1); // range (cell) where to create dropDown
  var rng2=sht2.getRange(1,1,lastRowOfImpItems,1).setValues(rng4); // range on a sheet within the program containing the list of dropDown items
  var rule = SpreadsheetApp.newDataValidation().requireValueInRange(rng2).build();
  rng1.setDataValidation(rule);
}
function onEdit(e)
{
   var row = e.range.getRow();
   var column = e.range.getColumn();
   var cellValue = e.range.getValue();
   if(column == 15 && row == row && cellValue == "vrini vrstico na vrh" )
   {
    sheet.insertRowBefore(2);
    cell.setValue('Klik tu in izberi akcijo');
    sheet.getRange(2,column).setValue('Klik tu in izberi akcijo');
   }
   if(column == 15 && row == row && cellValue == "vrini vrstico nad trenutno" )
   {
    sheet.insertRowBefore(row);
    cell.setValue('Klik tu in izberi akcijo');
    sheet.getRange(row+1,column).setValue('Klik tu in izberi akcijo');
   }
   if(column == 15 && row == row && cellValue == "vrini vrstico pod trenutno" )
   {
    sheet.insertRowAfter(row);
    cell.setValue('Klik tu in izberi akcijo');
    sheet.getRange(row+1,column).setValue('Klik tu in izberi akcijo');
   }
   if(column == 15 && row == row && cellValue == "dodaj vrstico na konec" )
   {
    sheet.insertRowAfter(lastRow);
    cell.setValue('Klik tu in izberi akcijo');
    validate();
    sheet.getRange(lastRow+1,column).setValue('Klik tu in izberi akcijo')
     .setFontColor("red")
     .setFontWeight("bold")
     .setBorder(true, true, true, true, true, true)
     .setWrap(true)
     .setHorizontalAlignment("center");
    sheet.getRange(lastRow+1,16).setValue(lastRow);
   }
   for (var i = 0; i <= lastRow-1; i = i + 1) 
   {
    sheet.getRange(i+2,1).setValue(lastRow-i);
   }
}

function onChange(e){
  const sh=e.source.getActiveSheet();
  if(e.changeType == 'INSERT_ROW' || e.changeType == 'REMOVE_ROW')
  { if(sh.getName()=='Main')
    {
      for (var i = 0; i <= lastRow-1; i = i + 1) 
      {
        sheet.getRange(i+2,1).setValue(lastRow-i);
      }
    }  
  }
}

And it doesn’t work (does not create a dropdown) in a spreadsheet: https://docs.google.com/spreadsheets/d/1ZXACv6TfPQuy9zmOIjIpNm1RVfpDaoBx5sIQGnOuPiQ/edit?usp=sharing

Any idea and hint very much wellcomed 🙂 tnx.

Answer

If you want to call function validate() with onEdit trigger and you want that function validate() creates a dropdown with items from another spreadsheet, then you have to change onEdit function from simple trigger to instalable triger because simple trigger cannot access data from another spreadsheet. Only instalable trigger can do this. That’s why length of the range was 0 and you were getting the error.

Advertisement

Answer