0

I have a small script and what I'm trying to do is to write one value from 'Sheet 1' to 'Sheet 2'. Wait for the results to load and compare the cells to see if it is above 10% or not. I have some =importhtml functions in the spreadsheet and it takes along time to load. I've tried sleep, utilities sleep, and flush. None have been working, maybe because I might be putting it in the wrong area..

function compareCells() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var listSheet = ss.getSheetByName('Stocks');
    var dataSheet = ss.getSheetByName('Summary');
    var listSheetLastRow = listSheet.getLastRow();
    var currRow = 1;
    for (i = 1; i <= listSheetLastRow; i++) {
        if (listSheet.getRange(1, 3).getValue() == 'asd') {
            var ticker = listSheet.getRange(currRow, 1).getValue();
            dataSheet.getRange(5, 4).setValue(ticker);
            var value1 = dataSheet.getRange(15, 4).getValue();
            var value2 = dataSheet.getRange(22, 4).getValue();
            SpreadsheetApp.flush();
            if (value1 > 0.10 && value2 > 0.10) {
                listSheet.getRange(currRow, 8).setValue('True');
                listSheet.getRange(currRow, 9).setValue(value1);
                listSheet.getRange(currRow, 10).setValue(value2);
            } else {
                listSheet.getRange(currRow, 8).setValue('False');
            }
        } else {
            Browser.msgBox('Script aborted');
            return null;
        }
        currRow++;
    }
}
John Saunders
  • 160,644
  • 26
  • 247
  • 397
user3527050
  • 1
  • 1
  • 1
  • Please post more details of what range of values are being imported using `=importhtml` function and also if you can add the spreadsheet with the data to analyse. – KRR Dec 18 '14 at 23:29
  • I'm importing financial statements of companies. I've created a valuation method for myself. What I'd like is for that valuation to be done on the entire market. So essentially what I'm doing is, I'm going to have a sheet with a list of stock ticker symbols, I wanted the script to take each symbol input into appropriate field and then wait for the financial statements to be imported, the calculations to be done (roughly takes 10 seconds) and then check if margin of safety meets 10%. If it does then it writes true, and moves to the next one else writes false. Let me know if you want to see it. – user3527050 Dec 19 '14 at 02:20

3 Answers3

4

If it is not important that you use the =IMPORTHTML() function in your sheet, the easiest way to do this will be to use UrlFetchApp within Apps Script. Getting the data this way will cause your script to block until the HTML response is returned. You can also create a time-based trigger so your data is always fresh, and the user will not have to wait for the URL fetch when looking at your sheet.

Once you get the HTML response, you can do all of the same processing you'd do in Sheet1 within your script. If that won't work because you have complex processing in Sheet1, you can:

  1. use UrlFetchpApp.fetch('http://sample.com/data.html') to retrieve your data
  2. write the data to Sheet1
  3. call SpreadsheetApp.flush() to force the write and whatever subsequent processing
  4. proceed as per your example above

By handling these steps sequentially in your script you guarantee that your later steps don't happen before the data is present.

Jesse Scherer
  • 1,492
  • 9
  • 26
  • You know thats not a bad idea but the thing is I've went on too far with this method. i'm importing financial statements from about 4 different url, once they get loaded there is another sheet that takes relevant data from that and extracts it then another sheet thatactually does calculations. If anyone is actually interested in see'ing ig please let me know. Maybe it'll make it easier. – user3527050 Dec 19 '14 at 02:22
  • I'm still not sure why you couldn't just do a URL Fetch to do the "import financial statements from 4 URLs" part of what you just described. If you fetch then write in the same script which does the processing you pasted above, you get a guarantee that the data is there when script runs, and you do not have to change anything. – Jesse Scherer Dec 19 '14 at 17:25
  • Well to be honest I'm not very skilled in script writing. I know excel functions and really had to learn a lot while using google sheets. Could you please explain more? The other reason I didn't use URLFetch was because what if I'm not using the script and I just wanted to review the financial data... – user3527050 Dec 21 '14 at 07:58
2

I had a similar problem but came up with a solution which uses a while loop which forces the script to wait until at least 1 extra column or 1 extra row has been added. So for this to work the formula needs to add data to at least one extra cell other than the one containing the formula, and it needs to extend the sheet's data range (number of rows or columns), for example by adding the formula to the end of the sheet, which looks like what you are doing. Every 0.5 seconds for 10 seconds it checks if extra cells have been added.

 dataSheet.getRange(5, 4).setValue(ticker);            
 var wait = 0;
 var timebetween = 500;
 var timeout = 10000; 
 var lastRow = dataSheet.getLastRow();
 var lastColumn = dataSheet.getLastColumn();
 while (dataSheet.getLastColumn() <= lastColumn && dataSheet.getLastRow() <= lastRow){
   Utilities.sleep(timebetween);
   wait += timebetween;
   if (wait >= timeout){
     Logger.log('ERROR: Source data for ' + ticker + ' still empty after ' + timeout.toString() + ' seconds.');
     throw new Error('Source data for ' + ticker + ' still empty after ' + timeout.toString() + ' seconds.');
   }
 }
user3261477
  • 987
  • 8
  • 3
1

In case if you are getting these two values (

var value1 = dataSheet.getRange(15, 4).getValue();
var value2 = dataSheet.getRange(22, 4).getValue();

) after the =importhtml call, you have to add sleep function before these two lines of code.

You also can have a loop until you get some values into the range from =importhtml call and add some sleep in the loop. Also note that as of April 2014 the limitation of script runtime is 6 minutes.

I also found this link which might be helpful.

Hope that helps!

Community
  • 1
  • 1
KRR
  • 4,647
  • 2
  • 14
  • 14