0

The problem started a month ago,

Some of the older sheets, where I get a list of stocks, and calculate moving averages, bollinger band values etc, in form of a table through googlesheets script. It used to do 4-10 stocks per minute when I created the sheet. But now, it is at 0.3 Stock/min.

What I have done till now based on other questions on stackoverflow.

  1. Removed all the formulas where range was infinite, A:A to A1:A100.
  2. Added SpreadsheetApp.flush(); without any help
  3. Put sleep timer on time based infinite loop. (Do While loop with an end time - start time < 3 minutes)
  4. Deleted blank rows and blank columns.
  5. Part of the code where I think the problem is.

  if ( s1.getRange("t1").getValue() == 1)
  {
    var stime = min+sec/60;
    do
  {
  SpreadsheetApp.flush();
  if(s1.getRange("d7").getValue()>0){s1.getRange('e1').copyTo(s1.getRange('d14'),{contentsOnly: true})}
  s1.getRange("t1").setValue(0);
  var pasterow= s1.getRange("d7").getValue();
  if(pasterow<=s1.getRange('t2').getValue()&&s1.getRange("G9").getValue()>0) //Conditions required for pasting values in table
  {
  var t = 'l'+pasterow;    var p = 'm'+pasterow;var q = 'n'+pasterow;var r = 'o'+pasterow;
  var s = 'p'+pasterow;var x = 'q'+pasterow;var u = 'L'+pasterow;var v = 's'+pasterow;var w = 'i'+pasterow; 
   s1.getRange('d6').copyTo(s1.getRange(t),{contentsOnly: true});
   s1.getRange('d5').copyTo(s1.getRange(p),{contentsOnly: true});
   s1.getRange('d8').copyTo(s1.getRange(q),{contentsOnly: true});
   s1.getRange('d9').copyTo(s1.getRange(r),{contentsOnly: true});
   s1.getRange('d10').copyTo(s1.getRange(s),{contentsOnly: true});
   s1.getRange('d11').copyTo(s1.getRange(x),{contentsOnly: true});
   s1.getRange('d16').copyTo(s1.getRange(v),{contentsOnly: true});
   s1.getRange("d18").copyTo(s1.getRange(w),{contentsOnly: true});
  }
    Utilities.sleep(10000);
    var date = new Date();  var day = date.getDay();var hrs = date.getHours();var min = date.getMinutes(); var sec = date.getSeconds()+1;
    var etime =min+sec/60;if(stime>=55&&etime<10){etime = etime+60;};
  s1.getRange("G9").setValue(etime)  
  } while (etime-stime<1.95 && pasterow<s1.getRange('t2').getValue()+1 && s1.getRange('ad2').getValue()==0); 
    
}
Finance_Himanshu
  • 1
  • 1
  • I can see the value in google sheet, but script sees it and paste it #DIV/0 (error). This point do come when a new stock (next in line) is added in the calculation. – Finance_Himanshu Jun 26 '21 at 09:05
  • Would like to help but without having a project to test with its hard to troubleshoot. It is possible for you to make a [Minimal Reproducible Example](https://stackoverflow.com/help/minimal-reproducible-example)? Something that we can reproduce from scratch on our accounts that illustrates the problem? Same a sample simple project? – iansedano Jun 28 '21 at 07:59
  • Please visit this page, https://docs.google.com/spreadsheets/d/1aU-6glR1tzi2z7zEuzuNQdNSsGdC3fjPhBxxeZWDO_Q/edit?usp=sharing, To start it, makee sure D20 field in excel is set to 1, and move to script, and start. If any permissions issue, let e know. – Finance_Himanshu Jun 29 '21 at 10:54
  • There is a funny issue with all of my sheets, if i press delete key repeatedly, the value fetched from script is the current ones, that I can see. I am sure, this "Pressing of delete key" is unique. Didn't find an answer online. You/any viewer can have complete access to the complete sheet and the script. And play with it, I have made myself a backup. – Finance_Himanshu Jun 29 '21 at 11:03
  • Can you describe briefly what your script does? Does it go through the list of stocks waiting for the `GOOGLEFINANCE` formula to refresh for each of them? What is meant to be in `AA1241`? – iansedano Jun 29 '21 at 15:10
  • Ok in AA1241, there was a list of stocks imported from my google sheet for fundamental analysis. This cell has nothing to do with the main Do loop. I forgot to mention, 1. This has to run once a week, but the script is set to repeat at 1 minute interval. – Finance_Himanshu Jun 29 '21 at 16:13
  • Ok, but can you add to your question a description of the process of the script? Its a long script, your formatting is not what I am used to and the variable names are not very descriptive, it would make things easier to understand if you could expand a little on what the script does exactly, step by step. – iansedano Jun 30 '21 at 07:01

0 Answers0