CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
TheFireFox
TheFireFox

Reputation: 71

google apps script get range somehow not working what am i doing wrong?

somehow .getRange not working ?? works this way in every other script why not here ? Log is attached...

Error is at line

**

"var days srcSheet.getRange("F" + i);"

**

function Test() {
      var sSheet = SpreadsheetApp.getActiveSpreadsheet();
      var srcSheet = sSheet.getSheetByName("Refinery");
      var lastRow = srcSheet.getLastRow();    
    
  for(var i = 0; i <= lastRow; i++) {
    var days = srcSheet.getRange("F" + i);
    var day = days.getValue();
    var hours = srcSheet.getRange("G" + i)
    var hour = hours.getValue();
    var cells = srcSheet.getRange("E" + i);
    if((day != "")||(day != null)&&(hour != "")||(hour != null)&&(cells == "-"))
    {
      //Datum berechnen
      var neudate = new Date()
      var Btag = 1000*60*60*24*day
      var Bstd = 1000*60*60*hour
      var DATUM = (new Date(neudate.getTime()+ Btag+Bstd) )
      DATUM = Utilities.formatDate(DATUM, "GMT+2", "dd.MM.yyyy HH:mm:ss")
      cells.setValue(DATUM);
    }

      
  }
    
}

enter image description here

Upvotes: 1

Views: 658

Answers (1)

Tanaike
Tanaike

Reputation: 201378

Modification points:

  • In the case of method getRange(a1Notation), the start number of A1Notation is 1. But in your script, the 1st number is 0. I think that this is the reason of your issue.
  • In your script, getValue and setValue are used in a loop. In this case, the process cost of the script becomes high. Ref

Modified script 1:

In this modification, your script is modified. In this case, please modify your script as follows.

From:

for(var i = 0; i <= lastRow; i++) {

To:

for(var i = 1; i <= lastRow; i++) {

Modified script 2:

In this modification, your script is modified by reducing the process cost. In this case, please modify your script as follows.

function Test() {
  var sSheet = SpreadsheetApp.getActiveSpreadsheet();
  var srcSheet = sSheet.getSheetByName("Refinery");
  var lastRow = srcSheet.getLastRow();
  var range = srcSheet.getRange("E1:G" + lastRow); 
  var values = range.getValues().map(([cells, day, hour]) => {
    if ((day != "") || (day != null) && (hour != "") || (hour != null) && (cells == "-")) {
      var neudate = new Date();
      var Btag = 1000 * 60 * 60 * 24 * day
      var Bstd = 1000 * 60 * 60 * hour
      var DATUM = (new Date(neudate.getTime() + Btag + Bstd))
      DATUM = Utilities.formatDate(DATUM, "GMT+2", "dd.MM.yyyy HH:mm:ss")
      return [DATUM];
    }
    return [cells]
  });
  range.offset(0, 0, values.length, 1).setValues(values);
}

References:

Upvotes: 1

Related Questions