CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetstriggersgoogle-sheets-formula
user1667435
user1667435

Reputation: 3

Spreadsheet and time driven trigger

In a spreadsheet cell I have the following formula:

=importhtml("http://www.eco.hu/currency/table.htm"& year(now()) & month(now()) & day(now()) & hour(now()) & minute(now());"table";1)

which refresh some other cells values too.

I will save every day some values from different cells. I have the following function:

function Historia() {
 Utilities.sleep(15000);
 var ss= SpreadsheetApp.setActiveSheet(XXX);
 var rows = ss.getDataRange();
 var values = rows.getValues();

 for (var i=0;i<values.length; i++) { 
      var j = values[i].indexOf("Portf" );
      if (j > -1) { var aktPort = values[i][j+1]; }
  }
  var ss= SpreadsheetApp.setActiveSheet(Hist);
  ss.getRange(4,2).setValue(aktPort) ;

  }

When I run Historia by hand it works every time perfect, brings the right result. When I make a Time-driven day timer with this function, it brings almost always a #N/A result in my spreadsheet.

What do I do wrong? Thanks

Upvotes: 0

Views: 190

Answers (1)

gssi
gssi

Reputation: 5071

I suggest you think about the possibility that "Portf" is never found.

Here's a re-work of your code:

function Historia() {
 Utilities.sleep(15000);
 var ss= SpreadsheetApp.setActiveSheet(XXX);
 var rows = ss.getDataRange();
 var values = rows.getValues();
 var aktPort = "";

 for (var i=0;i<values.length; i++) { 
   var j = values[i].indexOf("Portf" );
   if (j > -1) { 
     aktPort = values[i][j+1]; 
     var ss= SpreadsheetApp.setActiveSheet(Hist);
     ss.getRange(4,2).setValue(aktPort);  // since there's only one storage location
     break;                               // no need to look further
   }
  }
}

Upvotes: 1

Related Questions